LEARNING MICROSOFT EXCEL 2000
Copyright CIT, 1999-2000. All rights reserved.

CHAPTER 2. BUILDING A SIMPLE SPREADSHEET/4

Worked Example 1
Consider again the problem of a friend from America staying at your house for the week. Each night he rings his wife back in the States. You want to calculate the total cost.

Step 2.26 Enter the data as follows

Figure 2.29

Step 2.27 To sum the charges, define the charges (B3 to B7) and then click on the AutoSum key on the toolbar. The spreadsheet should look as follows

Figure 2.30

Why Spreadsheets(again)?
Why bother with a spreadsheet? This could easily be done on a calculator. There are two main reasons.

• One, the above is easy to read and the daily charges can be visually checked.
• Two, if there is a mistake, it can be corrected easily and automatically the total is recalculated.

The spreadsheet was originally designed to solve financial problems. However because of its power it can be used to solve a wide range of business problems.

It also has the ability to solve scientific and engineering problems. Very powerful statistical functions are available.

Worked Example 2
For fundraising for the netball club, Mere sells $200 worth of shampoo and $80 of conditioner. Jane sells $50 worth of shampoo. Alice sells $50 worth of shampoo and $50 worth of conditioner.

We want totals for each of the netballers, for shampoo and conditioners, and a grand total sales.

We want to calculate the profit for the club, which as 40% of total sales.

Step 2.28 Enter the data as follows

Figure 2.31

Step 2.29 Define the numbers and where you want the totals to go as follows

Figure 2.32

Step 2.30 Click on the AutoSum button.

Figure 2.33

Check the equations and you will find them all correct. The grand total is in D5.

Now calculate the profit. It is given as 40% of total sales(D5).

Step 2.31 Enter into D6 =D5*40/100.

Figure 2.34

Worked Example 3
Say you have $10000 to invest. You are quoted 11% compound interest (yearly) if invested for 5 years.

Step 2.32 Start the spreadsheet as follows

Figure 2.35

For year 1 we start the year with 10000,

Step 2.33 so we write in C4 =C2

The interest for the year would be 10000*11/100 = 1100,

Step 2.34 so in D4 we write =C4*C1/100

This works for D4, but if we copy the equation down to D5 the equation in D5 will be =C5*C2/100. C5 is correct, but C2 should be C1.

Step 2.35 To do this we specify C1 as an absolute cell address. So in D4 we rewrite =C4*$C$1/100. Entering these we get

Figure 2.36

This is correct for year 1, we start with 10000 and make 1100 interest. The start of year 2 should be 10000 plus the interest.

Step 2.36 To do this we write in C5 =C4+D4. This gives

Figure 2.37

Now that the equations are set up all we copy C5 and D5 down four cells.

Figure 2.38

This shows us that for a $10,000 investment with an interest rate of 11% compounded yearly over 5 years the $10,000 investment increases to $16850.58.

The power of what we have done is that we can try other interest rates and/or investment values (called what ifs). We can change the values in C1 or C2 and the spreadsheet recalculates. Change the interest rate to 12% and the amount invested to $20000. We can also extend the number of years by copying the equations down columns C and D.

Exercises

Exercise one
Enter the following spreadsheet and give totals for all columns, rows and a grand total.

Figure 2.39

Exercise two
Enter the following spreadsheet and give in column D the percentage changes in sales.

Percentage change in sales is given by

Exercise three
Enter the following spreadsheet.

Figure 2.41

For each worker calculate the gross pay given a standard rate of pay of $15.50 per hour. The overtime counts as time and a half. For example if 20 hours overtime is worked then they are paid for 30 hours.

Place the calculated total hours in column D and the calculated gross pay in Column E. Also give totals for ordinary hours, overtime hours and gross pay.

Home | Other Courses | Notes | Feedback