Set Up a Six-Month Cash Flow Projection Using Microsoft Excel for Elite Plc

$10.00

Purchases are equal to cost of sales, set at 65% of sales

 

SKU: Fin004290

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.

ElITE PLC
Cash flow projection – six mon ths ending 30 June 2X19
Jan Feb Mar Apr May Jun
£ £ £ £ £ £

Sales
Cash receipts
1 month in arrears
2 month in arrears
3 month in arrears
Total operating receipts
Cash payments
Purchases
Overheads
Total operating payments
Dividends
Capital purchases
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

January                                                                                                                                                             20%
February                                                                                                                                                           70%
May                                                                                                                                                                    10%

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)

Required-Group Assignment
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.

Reviews

There are no reviews yet.

Be the first to review “Set Up a Six-Month Cash Flow Projection Using Microsoft Excel for Elite Plc”

Your email address will not be published. Required fields are marked *

Sorry no more offers available