Virtual Workshops

Part of keithjbrown.co.uk

Main Menu

• Server Environment
• MySQL
• PHP Databases
• Web Design
• Excel

A Whole Application

I've had quite a few e-mail enquiries about Excel and how pull all the functionality I introduce in the other workshops into various other applications. Thus I've decided to create this Virtual Workshop which gives another worked through example as well as introducing some more features that make for a more professional application. We are going to use Excel to build an application to solve a problem that I set as an assignment for undergraduate students when I was an Information Systems lecturer. The assignments are changed every year for quality reasons, so it is quite safe to use this as an example here.

The Problem

You have been commissioned by Ski-Breaks Ltd to produce a spreadsheet model which calculates the cost of ski holidays.

Brief

You should develop a spreadsheet model to calculate the total charge to be levied for any holiday.

Data

Example of the holiday data that a Ski-Breaks Ltd employee has at their disposal.

Destination	Val d'isere, France	St Anton, Austria	Courmayeur, Italy	Le Duex Alpes
Hotel	Chalethotel Moris	Chalethotel Schweiserhof	Chalethotel Cristallo	Chalethotel Le Marmottes
Duration	7 Days	14 Days	7 Days	7 Days
Accommodation	Full Board	Bed and Breakfast	Bed and Breakfast	Full Board
Leave from	Gatwick	Edinburgh	Manchester	Glasgow
Season	High	Low	High	Low
Cost	£658 pp	£1096 pp	£750 pp	£550 pp

Additional Pricing Considerations

The prices shown are (at last years prices) the basic cost of either a 7, 14 or 21 day holiday. You should increase these by 5% to reflect the prices this year.

The system is to be built for parties of holiday makers and thus everyone will be travelling the same way.

There is additional costing information, as follows:

• There is a supplement for flying from 'regional' airports: Manchester £25, Edinburgh £35, Belfast £40. (Gatwick has no supplement).
• Independent travel: £90 off per person.
• Insurance is charged at £28 for up to 10 days, £35 for 10 or more days.
• Children go free in low season, or 50% at other times.

The Solution

Open up a new Excel file and enter the following in the first row.

Note: As we are going to try and make this as user friendly as possible we have included an instructions column.

Next merge Columns A and B, make the background colour blue and the text white and bold (See Part 2 of this series for how to do all this). This creates a pretty good heading:

Also add the information about the holiday in column A.

Creating a List

Data in Rows 2 and 3 will change with each holiday, but Row 4 has values that will stay somewhat the same. Row 4 is concerned with the duration of the holiday which can be either 7, 14 or 21 days. Thus we can make a drop-down list for the user to choose from to make data entry easier.

Select cell B4, then from the 'Data' menu select 'Validation'. This bring up a Dialog Box. From the 'Allow' drop-down menu select 'List', which will allow us to enter a list into the 'Source' field. Each value we want in the list must be separated by a comma.

Click OK and check that this has worked (it might also be a good idea to right align the cell):

Cells B5, B6, B7 and B8 also contain a limited choice or values and thus we can make them lists as well.

Cell	Information About	List Values
B5	Accommodation	Bed & Breakfast, Full Board, Self Catering
B6	Leave From	Gatwick, Manchester, Edinburgh, Belfast
B7	Season	Low, High
B8	Travelling Independently	Yes, No

Working Out This Year's Price

We have two pieces of information that can determine the basic cost of the holiday - last year's price and the knowledge that prices have increased by 5%. Thus we can work out the price rise in cell B10 and then add last year's price (B9) to this increase and display the total for a basic holiday in cell C11. Enter the following formula in cells B10 and C11.

Cell	Formula	Explanation
B10	=B9*(5%)	Cell B9 multiplied by 5%
C11	=SUM(B9:B10)	Sum of Cells B9 and B10

Progress Check

If we fill out the data from the first holiday destination we can check that things are working so far. Notice that the currency cells have also been formatted as in Part 2.

Assuming that everything is working fine, create similar title and information for the Additional Costings.

Excel's IF worksheet function

We can use the values already entered to determine the value of any additional costs by combining what we know about these costs and the IF worksheet function.

We will use the regional supplement (which airport they are flying from) as our first example.. We know that there are 4 airports each with a different price. If we can determine which airport has been selected in cell B6 we should be able to work out the price. To do this we use an IF statement to check for an airport name, and if that exists enter a corresponding value. The syntax for this statement is:

=IF(logical_test,value_if_true,value_if_false)

So to test if Belfast is the airport selected in cell B6 (and if so set a value of 40, if not a value of 0) we would enter a formula in cell B13 :

=IF(B6="Belfast",40,0)

This is fine for just one condition, but there are 4 possible airports and costs and thus we need a way of saying 'or else if it is... '. This can be done by replacing the value_if_false with another IF condition.

=IF(B6="Belfast",40,IF(B6="Edinburgh",35,0))

....and so on until all the airports have been filled in. The final false value is just set to 'o'.

You can test this formula by changing the Airport cell (B6) - you should see the value of cell B13 change (make sure that you reset B6 to 'Gatwick' which is the airport in the first example). Once more we can fill out other cells using a similar way.

Cell	Formula	Explanation
B14	=IF(B8="Yes",90,0)	Checks if Cell B8 is Yes and if so sets the cost to 90.
B15	=IF(B5<10,28,35)	If Cell B5 has a value less that 10 then set the insurance cost to 28 else set it to 35.

With some more formatting we end up with something like this.

Adding More Totals

Our next task is to record the impact that these costings have on our total. We can do this by updating the running total in Column C filling out values in C13, C14 and C15 by either adding or subtracting the values in B13, B14 and B15 as dictated.

Cell	Formula	Explanation
C13	=SUM(C11+B13)	Adds the value of the regional charge (B13) to the total in C11.
C14	=SUM(C13-B14)	Subtracts the cost of independent travel (B14) from the total (C13)
C15	=SUM(C14+B15)	Adds the cost of insurance in Cell B15 to the total in C14.

If we copy the final total onto a new line (by dragging the corner of cell C15 over cell C16) and apply some formatting we can get a result that clearly shows the cost per person.

Party Information

The only thing we have to do is enter the details of the travelling party and calculate the cost of the whole holiday. Enter a new header line 'Party Information', some 'Number of Adults' and 'Number of Children' lines and finally the 'Total Holiday Cost' line. Also fill in some sample party numbers that will show our formula working.

In order to complete our spreadsheet application we must work out the cost for adults seperately from children (as they go either free or at 50%) and finally the total for the party. Thus we need 3 further formulae.

Cell	Formula	Explanation
C18	=(C16*B18)	Multiplies the cost per person (C16) by the number of adults (B18).
C19	=IF(B7="High",((C16*(50%))*B19),0)	If the Season Cell (B7) has a value of 'High' then multiply the cost per person (C16) by 50% then by the number of children (B19). Otherwise set the value to 0.
C20	=SUM(C18+C19)	Adds the cost of adults (C18) to the cost of children (C19) to produce a final total cost.

This results in the final look of:

Thus an application has been created that meets the needs of Ski-Breaks Ltd. We can improve the application further by making it more friendly for users.

User Interface Enhancements

We have a fully functioning spreadsheet application but we are also going to add some protection and guidance for the end-user. First we are going to give instructions to the user: but this will look ugly when printed, so we will define a print area. Finally we will lock the database so that the user cannot inadvertently change the formulae and break the application.

Adding Instructions

This part is fairly straightforward. We have already created an 'Instructions' heading in column D so all that we have to do is fill in instructions. I tend to use italicised text.

Note: Because we have created several automations the user doesn't need to manually enter all the data and thus doesn't need instructions for every line.

Defining a Print Area

With the addition of instructions, there is now an area that we wouldn't want to print. Thus we will create a print area that is restricted to the factual information about the holiday. To do this select cells A1 to C20:

Define this area as the print area by selecting Print Area / Set Print Area from the File menu. Now when the user prints the spreadsheet only this selected area will be output.

Locking the Application

The final enhancement is to lock the worksheet to ensure that only the cells which hold values that change can be altered by the user, protecting the formulae from accidental deletion. To achieve this there is a 3 stage process:

1. Ensure that all the cells are marked locked
2. Unlock the cells that we want the users to be able to change
3. Protect the database to enable the locking mechanisms.

Marking the fields as locked is usually done by Excel as the default, but it is worth checking. Select all the cells (including the instructions this time) and then from the Format menu select Cells. This should bring up a dialog box from which we want to choose the Protection tab and make sure that the locked tick box is selected (the tick should be black rather than grey which means that only some of the cells are locked).

Next we select each cell we want the user to be able to change (in this case cells B2 to B9, B18 and B19) and reverse this protection so that the locked box is NOT ticked.

Finally from the Tools menu, select Protection / Protect Sheet. Ensure that only the 'Select unlocked cells' is ticked and press OK.

Note: The password is optional and prevents just anyone being able to remove the protection from the worksheet.

The sheet is now protected and our application is complete.

Conclusion

This workshop was meant as a work though example of how to build an application. Although I've introduced a few new concepts, these were necessary for building the integrated application, the fundamentals of building formulae have remained the same.

Lastest 10 Threads - view all

---

Post a Comment or Question

Name

Email

URL (optional)

Title of Comment

Comment
HTML Allowed:
a,code,strong,em

I would like to be e-mailed any response to my comment.

In this section

• Averages and Mail Merges
• Basic Financial Statements
• Database Functions
• Whole Applications

Related Reading

• Another Tutorial

Related Books

Related Ads