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
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.
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.
Now use formulae to display the figures for the rest of the sheet:
Number of customers x £20
Sunset Suite sales
Number of bookings x £500
Restaurant sales + Sunset Suite sales
Cost of sales
20% of the total of Restaurant and Sunset Suite sales
£1000 for months with less than 1000 customers
£3000 for months with more.
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 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.
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.
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)
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.
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.
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
“No bank will agree to that much!!” if an overdraft
of over £50000 is indicated.
[sample completed solution]