You have been asked to set up a six-month cash flow projection using Microsoft Excel for Elite Plc. You have the following information.
(a) Sales were £45,000 per months in 2X18, falling to £42,000 in January 2X19. Thereafter they are expected to increase by 3% per month (i.e. February will be 3% higher than January, and so on).
(b) Debts are collected as follows.
i. 60% in month following sale.
ii. 30% in second month after sale.
iii. 7% in third month after sale.
iv. 3% remains uncollected.
(c) Purchases are equal to cost of sales, set at 65% of sales.
(d) Overheads were £6,000 per months in 2X18, rising by 5% in 2X19.
(e) Opening cash is an overdraft of £7,500.
(f) Dividends: £10,000 final dividend on 2X18 profits payable in May.
(g) Capital purchases: plant costing £18,000 will be ordered in January. 20% is payable with order, 70% on delivery in February and the final 10% in May.
The basic structure of the spreadsheet has been set up, and the relevant data are as bellowing.
Cash flow projection – six mon ths ending 30 June 2X19
Jan Feb Mar Apr May Jun
£ £ £ £ £ £
1 month in arrears
2 month in arrears
3 month in arrears
Total operating receipts
Total operating payments
Total other payments
Net cash flow
Cash balance b/f
Cash balance c/f
This table contains the key variables for the 2X19 cash flow projections
Sales growth percentage per month 3%
Purchases as % of sales 65%
Debts paid within 1month 60%
Debts paid within 2month 30%
Debts paid within 3month 7%
Bad debts 3%
Increase in overheads 5%
Dividends (May) 10,000
Capital purchases 18,000
This table contains relevant opening balance data as at Jan 2X19
Monthly sales 2X18 45,000
January 2X19 42,000
Monthly overheads 2X18 6,000
Opening cash (7,500)
Construct a spreadsheet to prepare the six-month cash flow projection for Elite Plc.
TASK 1 Hard copy print out of the cash flow projection for six months ending 30 June 2X19.
Good presentation can help people understand the contents of a spreadsheet. You are required to present the spreadsheet in professional format and appearance.
TASK 2 Hard copy print out of the above six months cash flow projection with formulae presented in the spreadsheet.
TASK 3 Display and analyze data held in the above six months cash flow projection in FIVE different charts or graphs.