This module teaches the basic concept of using electronic spreadsheets. Spreadsheets are basically multicolumn sheets on the computer. The sheets are basically used for number processing, and as such are usually not used for data storage.

The software used is MS Excel (Office 97 version). The students are taken through the motions of learning how to manipulate numbers, using different methods, i.e. formulas, functions, etc. It is important to get the concept of the formulas right, as this is the basis of using Excel, or any other electronic spreadsheet program.

The following nine exercises take the students through the motion of learning formulas, using different scenarios. Exercise 9 deals with making a simple chart.

It is advisable to start with the first exercise before you venture out any further. It would also be worth noting here that these exercises are meant to complement the learning in the classroom, and are meant as an additional aid only. In the interest of the environment, please do not send to print, unless you are absolutely sure.

Items sold (flowers) = 4000 in 1994 and increase by 500 every year Price = 2.50 per flower and every year the price increases by 10%

Other Revenue (vases) is $1000 per year but in 1997 was 1500

Operating Expenses = $6000 in 1994 and then increased by $1000 per year

Fixed Expenses = $1500 per year

In the Total column, you would have the totals of the revenue to the Profit, this will help in calculating the Average Revenue, Expenses, etc.

In the sales revenue row, you would need to multiply the number of flowers, by multiplying the number of flowers sold, i.e. 4000x2.50 will get you the base figure. For the next year, the increase is 10%, so multiply the first sale figure by 1.10 to get the next years figure, and then take it from there.

Enter a formula to increase both incomes by 2% in Nov & Dec

The following expenses are relative to the Jones income. You are to enter them in the October column:

Clothing is considered to be 15% of total income

Food is considered to be 45% of total income

Entertainment is 15% of total income

The expenses for rent and travel are fixed and are not expected to change in November & December

Enter a formula to add total Income and total Expenses

Enter a formula to calculate excess/surplus for each month

To get 15% of the total income, you need to multiply the figure by 0.15 to get the figure, take it from there and see how you go, every one has their own way of calculating figures, just remember to keep them simple.

The figures for Gross Profit are derived from the cost per item by the Items sold.

Expenses are 15% of Gross Profit from Jan to Apr, May & Jun is an increase to 20%.

Company Tax is 29% of Profit.

Gross Profit-Expenses-Company tax = Net Profit

In column D row 6 to 14 have to be added up for the Total, in column E an average per row is required, with the total average in the Total row. In column G a 20% increase in fare is required. In column H the numbers required are to be derived from multiplying the passengers in column B with the fares in column F. In column I column C figures are to be multiplied with the figures in column G. Column J will be total value. Column K will be an average of the total value.

1. Total sales for each employee for the quarter

2. Total sales for each month.

3. Total sales for the 1st Quarter

4. Calculate a Bonus for each employee as follows; if the person has sold more than his/her target sales for the qtr, the bonus is 2.5% of the amount of sales greater than the target.

5. Minimum, maximum and average sales for each employee.

6. The average sales figure should be formatted to 2 decimal places.

7. Targets for each employee are calculated at a 10% increase on 1st Qtr sales, if necessary, the percentage should be placed in a cell and referenced by absolute cell reference in the formula.

Just a quick hint: for the bonus figure, you would have to write the following formula or something similar to it e.g.: =(e6-e7)*2.5%, see how you go.

Production costs 38%

Marketing costs 12%

Repairs 1%

You are to enter formulas to calculate the Gross Profit value for each year 1987 to 1991.

The Gross Profit is:

Sales Forecast Value

Minus Production Costs

Minus Marketing costs

Minus Repairs.

For each column 1987 to 1991, enter the formula to calculate the Total Sales Value cells. The Total Sales Value is the total of the sales forecast value for Large, Medium and Small refrigerators for the specific year.

For each column 1987 to 1991, enter the necessary formula to calculate the Total Profit cells. The Total Profit Value is the total of the calculated gross profit for Large, Medium and Small refrigerators for the specific year.

Enter the formulas into the spreadsheet for the seventh exercise, you have created below to do the following calculations:

1. Each cell in the "Flight Fuel" column is determined by multiplying the "Flying Hours" value by the "Litres per hour" value for the appropriate "Plane".

2. The "Reserve Fuel" is calculated by multiplying the "Flight Fuel" by the "Reserve Fuel" percentage.

3. Similarly the "Holding Fuel" is calculated by multiplying the "Flight Fuel" by the Percentage indicated in the "Holding".

4. The "Total Fuel" is the sum of the "Flight Fuel", the "Reserve Fuel" and the "Holding Fuel".

5. The "Nett Cost" is calculated by multiplying the "Total Fuel" by the "Cost per litre".

6. The "Sales Tax" is determined by the "Nett Cost" calculated above. If the "Nett Cost" is less than $100,000 then the sales tax is 6.5% of the Nett Cost. If the "Net Cost" is greater than or equal to $100,000 then the "Sales Tax is 3.5% of the Nett Cost.

7. Enter the appropriate function in the adjoining cells to determine the:

• The Highest Flight Fuel Cost
• The Total Cost of Fuel
• The Lowest Fuel Consumption
• The Average Fuel Consumption

8. Place a footer with your name, student no and group in appropriate places.

Formulas (absolute cell references where necessary) are to be used for all calculations, so that if adjustments are made throughout the year the worksheet will automatically update.

Calculate the dealer mark up based on the wholesale cost.

Calculate the retail-selling price based on the cost of the car plus the markup.

Now calculate the government stamp duty and the registration, based on the retail selling price

Work out the final selling price including the on road costs (registration, stamp duty and dealer delivery)

Enter the item numbers using AutoFill, and describe in your own words in a text box at the bottom of the worksheet how and why you use this feature

Format all dollar amounts to currency showing no decimal places

Format all percentages to show percentage signs

Create a totals row at the bottom of the worksheet, and show totals of 'wholesale cost' and 'retail selling price'

Add a border between around the cell titled Victoria's Car Yard.

In a header, center your name.

Ensure that the work fits on to one page, you may need to adjust margins

Select the range of the calculations to display formulas.

The chart below is derived from the numbers above, using the Chart wizard from the toolbar.

Remember to highlight the figures you want represented in the chart.

For this exercise, you are to make a chart from the above figures, similar to the one below.

Remember to keep the chart in the same worksheet as the figures.