WORKSHEET: Construct and analyse Cash Flow Forecasts



Task 1

Preparing a cash flow forecast

Prepare an annual cash flow forecast for David’s business using the monthly data provided below. Do this using EXCEL.

  1. David is planning to open his new salon, Clarry’s in September. He has of course raised £8,000 of this himself, which he will put into a bank account as his opening balance. He will also obtain a loan in September for £9,000.
  1. David estimates the following revenue from the salon for each month over the coming year.

Month / Predicted Revenue / Month / Predicted Revenue
September / 3500 / March / 4500
October / 4000 / April / 4600
November / 4500 / May / 4600
December / 4500 / June / 4400
January / 4500 / July / 4400
February / 4500 / August / 4300
  1. David will pay at the beginning of September £17,000 for equipping the salon with its fixtures and fittings.
  1. David estimates the following cost of purchases will be as follows each month. He will receive one month’s credit from his suppliers, so will pay a month after receiving goods.

Month / Predicted Cost of purchases / Month / Predicted Cost of purchases
September / 880 / March / 1200
October / 1100 / April / 1200
November / 1100 / May / 1320
December / 1200 / June / 1320
January / 1100 / July / 1200
February / 1100 / August / 1200
  1. David also anticipates the following costs for hot water.

Month / Predicted Cost of heating water / Month / Predicted Cost of heating water
September / 120 / March / 165
October / 150 / April / 165
November / 150 / May / 180
December / 165 / June / 180
January / 150 / July / 165
February / 150 / August / 165
  1. Loan repayments at a rate of £3000 a year, payable in monthly instalments starting in October.
  1. David also plans to spend £150 advertising the launch of the salon in September, and then £75 per month afterwards.
  1. Rent/Rates on the shop needs to be budgeted at £375 per month.
  1. David’s wages will be £320 per week (Approximately £1280 per month)
  1. David will pay his friend £220.80 per week (Approximately £883.20 per month) for doing his accounts and general salon work.
  1. If a schoolgirl/boy is employed they will get on average £108.80 a month.
  1. There will be general electricity and heating expenses of £110 per month.

Task 2 (EXTENSION)

Implications of regular and irregular cash inflows and outflows.

David is looking for further investment into his business, but potential investors have asked for further information about the proposed cash flow of Clarry’s. Provide a report explaining all of the below to potential investors.

  • Explain why it is important for a business man like David to have regular cash inflows into his business.
  • What are the implications of poor and irregular cash inflow for the business?
  • Are there any times when poor cash inflow is likely for David’s business?
  • What should David do to minimise the effects of poor cash inflow?
  • Are there times when David’s cash outflow is higher than the monthly average?
  • What are the implications of irregular cash outflows for David’s business?
  • How would David manage these situations?
  • Why is it important for the business to have regular cash outflows?

Forms of evidence:

Written report 400-600 words.

You will be marked on the clarity of your report.