Katie’s Kitchen
Spreadsheet Modelling Task
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.
This is what a cash flow forecast looks like. A clearer example will be provided separately.
Task 2A
Create a spreadsheet similar to this to which you will add figures as provided below.
The first set of figures 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 2B
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 25000. 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 2C
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 2D
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 show display months and vertical axis amounts.
Task 2E
On a separate sheet display:
- 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 display these figures 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.