A short spreadsheet course

Task Menu

Student data

Data
Tasks
Notes
Solution

Peter The Carpenter

Data
Task
Notes
Solution

Football League

Data
Tasks
Notes
Solution

ICT Charts

Task
Notes

Katie's Kitchen

Task
Data sheet
Sample Solution

The Big Guide to Excel2007 [1.7MB PDF]

 
 

Katie’s Kitchen
Spreadsheet Modelling Task

Note: This is a more advanced task with no guidance notes (just a solution that should help you figure out most aspects - but feel free to ask if you get stuck!

Katie’s Kitchen is a restaurant with room for about 100 customers and a suite that can be hired out for functions. Your task is to help the manager figure out how it can be profitable and how much financial support might be needed in its first year.

To do this a cash flow forecast is required. This shows the estimated income and expenditure month by month over a year and indicates how the business bank balance changes. Because it is a business in which sales can fluctuate a lot it is likely that there will be periods, especially in the early months, when the bank balance will go into the red – or be negative – and a bank overdraft could be required.

A cash flow forecast will show how much overdraft should be agreed with the bank before the business starts. It will also show how much profit the business is expected to make in its first year.

Here is an example that may be useful to start wityh as it has the right entries for this particular case.

Task 1

The first set of figures to be entered is:
Management salaries 30000 per annum, payable monthly
Heat light and power 400 per month
Rent 2000 per quarter
Rates 200 per month
Equipment 300 per month
Repairs & renewals 1200 per quarter
Bank charges 100 per month
Professional fees 600 per quarter
Sundry expenses 100 per month

These are all payable from January. Quarterly payments are due January, April, July and October.

Task 2

Enter an estimated number of customers in the No. of customers row for each month. These should vary but not be fewer than 100 or more than 2000. At the right end of the row include a formula to show the total number of customers for the year. This should be between 5000 and 15000. Adjust your monthly estimates to ensure the total is within that range.
For Sunset Suite bookings enter figures between 0 and 50 each month. Show a similar total for these and check it is no greater than 300.
Add a suitable validation feature to the sheet to warn a user entering data (or changing yours) of these limits on customer and booking numbers.

Task 3

Now use formulae to display the figures for the rest of the sheet:
Restaurant sales
Number of customers x £20
Sunset Suite sales
Number of bookings x £500
Total income
Restaurant sales + Sunset Suite sales
Cost of sales
20% of the total of Restaurant and Sunset Suite sales
Wages
£1000 for months with less than 1000 customers
£3000 for months with more.
PAYE
This is 10% of the total of wages and salaries. However it is paid 2 months later – so the first payment will be in March, based on January’s figures. In April it will be based on February’s figures and so on.
VAT
VAT is 20% of income minus VAT on some expenditure. The formula should not include wages, salaries, PAYE or interest.
Note: This is not strictly accurate but OK for a rough estimate.
Interest
No interest is payable in January. From February it will be 1% of the January bank balance if it is negative (overdrawn). In March it will be 1% of the February overdraft and so on.
If the previous month’s balance is more than zero then enter 0 for interest.
Total expenditure
Add all the month’s expenditure
Profit or loss for month
Income minus Expenditure. A loss can be displayed as, for example, either -2200 or (2200)
Bank balance
For January the figure will be equal to the profit or loss for January. For February it will be the January balance plus the February profit (or reduced by February loss) and so on.

All the entries for the cash flow forecast should now be complete. You should find that by changing any income or expenditure figure the rest of the sheet will be updated and a new bank balance and end of year profit will be displayed.

Task 4

Create a chart to show how the bank balance moves throughout the year. Use a new sheet as its location and add a title Bank balance in first year. The horizontal axis should display months and vertical axis amounts.

Task 5

On a separate sheet display only:
- a smaller version of the chart
- the overdraft that the forecast indicates is required (the lowest balance throughout the year)
- the predicted profit for the year

Use formulae to collect these from your calculation sheet so that they are updated when the full forecast sheet is changed.

Add these warning messages:
Adjustments needed” if there is an overall loss for the year
Too optimistic!!” if the projected profit is greater than £50000
Check your figures – most businesses need some support at the start” if there is no apparent overdraft requirement
No bank will agree to that much!!” if an overdraft of over £50000 is indicated.


[sample completed solution]