Microsoft Excel Test

BIA 0075 Formative Assessment for Spreadsheet Exercise
Instructions
Time allowed - 45 minutes
Produce a spreadsheet that will deal with the following situation
The Garden Gnome company is undertaking its five year forecast process and is in the process of constructing its profit and loss account. The following information is available
a) In 2012 Sales are expected to be 50,000 gnomes at £20 each but volumes and prices are expected to increase by 2% and 3% per annum respectively on a cumulative basis. Uncertainty exists around these estimates of growth and these could change.
b) Variable costs amount to 40% of selling prices, however, due to competition this percentage may change in the future
c) Advertising will depend on the growth rate in sales volumes that is anticipated. If sales grow by 2% or above then advertising will be £20,000 per year. If sales volume growth is less than 2% then advertising will be increased to £30,000 per year
d) Salaries are expected to be £100,000 but this figure is expected to grow. At the moment, the estimate of growth is 2.5% per annum on a cumulative basis.
e) Produce a forecast income statement for the next five years that will reflect this information
f) Name, protect and use data validation on cells as appropriate.
g) Set up the following Scenarios
Best If the best market conditions are prevalent sales will initially 60,000 units at £23 per unit. And sales prices will grow at 5% per annum
Worst If the worst market conditions are prevalent sales will initially 40,000 units at £16 per unit. And sales prices will decline by 1% per annum
h) Produce a scenario summary table showing the results cell as the profit/Loss figure for each of the five years that the business is forecasting for.
i) Format your spreadsheet as appropriate 25 marks

THIS ASSESSMENT IS FORMATIVEAND IS INTENDED TO GIVE YOU AN IDEA OF HOW WELL YOU ARE PROGRESSING UP TO NOW.
PLEASE NOTE THAT YOUR SUMMATIVE ASSESSMENT IN MARCH WILL BE DIFFERENT TO THIS.

Marking criteria

Layout/Build of spreadsheet clearly identifying inputs and outputs 4 marks
Flexibility of spreadsheet (0.5 for each identified input) 5 marks
Accuracy Accurate sales units, Accurate revenue, Accurate VC, Correct adv 5 marks Correct sals Sceario summary for each scenario 1 mark Naming of cells 1 mark each 2 marks
Formatting / presentation 3 marks Protection 1 mark Absolute cell referencing, 2 marks Data validation £ 1 mark % 1 mark Total 25 marks