Hi,

Can a tutor kindly double check my cash flow statement. I think rows 42, 56 and 60 are incorrect. I think the formulas are wrong. A fresh pair of eyes would be greatly appreciated.

I have attached the original excel template, the data inputs and actual question, and my working spread sheet.

I don't want a tutor to write an analysis or anything like that. I would just appreciate someone checking my spread sheet to ensure everything is correct before I complete my assignment. Again, I am particularly concerned with rows 42, 56 and 60.

Kind regards.

Assumptions and Inputs Day 0 Balances: Cash Accounts Receivable Inventories Accounts Payable Other Payables Investment Portfolio Revolving Credit Facility Borrowing $ $ $ $ $ $ $ 15,000,000 100,000,000 50,000,000 80,000,000 20,000,000 50,000,000 5,000,000 Cash Conversion Profile of Receivables and Payables: Week Accounts Receivable Inventories Accounts Payable Other Payables 1 2 20% 0% 10% 50% 25% 0% 30% 25% Other Inputs: Bank Revolving Credit Facility Amount Credit Facility Cost $ 45,000,000 4.00% Cash Minimum Target Investments Minimum Target Investment Portfolio Yield $ $ 15,000,000 40,000,000 2.00% Cash Flow Forecast Week Inflow or (Outflow 1 2 Accounts Receivable Inventories Accounts Payable Other Payables $ $ $ $ $ 20,000,000 $ $ (8,000,000) $ (10,000,000) $ 25,000,000 $ $ (24,000,000) $ (5,000,000) Net Working Capital Flow $ $ (4,000,000) Other (e.g. debt principal + interest) $ Net Cash Flow $ 2,000,000 $ (8,000,000) Beginning Cash Beginning Cash Less Period Net Cash Flow $ $ 15,000,000 17,000,000 $ 15,000,000 $ 7,000,000 Minimum Cash Target Balance $ 15,000,000 $ 15,000,000 100,000,000 50,000,000 (80,000,000) (20,000,000) 2,000,000 - ### Cash Balance Surplus (deficit) Versus Minimum Target Balance (amount to allocate) $ 2,000,000 $ (8,000,000) Cash Surplus Allocation / Cash Deficit Financing Beginning Investment Portfolio Addition (Subtraction) Ending Balance $ $ $ Beginning Revolving Credit Facility Balance Drawdown (Repayment) Ending Revolving Credit Facility Balance $ $ $ Check 50,000,000 1,000,000 51,000,000 $ 51,000,000 $ 1,020,000 $ 52,020,000 5,000,000 $ (200,000) $ 4,800,000 $ 800,000.00 4,800,000 (192,000) 4,608,000 (9,212,000.00) d Inputs Inputs that you must complete are bordered with red. 3 4 5 6 25% 0% 20% 25% 10% 5% 20% 0% 10% 10% 10% 0% 10% 10% 10% 0% 3 4 5 6 $ 25,000,000 $ $ (16,000,000) $ (5,000,000) $ 10,000,000 $ 2,500,000 $ (16,000,000) $ - $ $ (3,500,000) $ orecast 4,000,000 $ (10,500,000) $ - $ $ $ $ $ 10,000,000 5,000,000 (8,000,000) - $ $ $ $ 10,000,000 5,000,000 (8,000,000) - 7,000,000 $ 7,000,000 - ### $ (2,500,000) $ (7,000,000) $ 14,000,000 $ 14,000,000 $ $ 15,000,000 12,500,000 $ 15,000,000 $ $ 8,000,000 $ 15,000,000 $ 29,000,000 $ 15,000,000 29,000,000 $ 15,000,000 $ 15,000,000 $ 15,000,000 $ 15,000,000 $ (2,500,000) $ (7,000,000) $ 14,000,000 $ 14,000,000 54,121,608 $ 1,082,432 $ 55,204,040 $ 55,204,040 1,104,081 56,308,121 ash Deficit Financing $ $ $ $ $ $ 52,020,000 1,040,400 53,060,400 $ 53,060,400 $ $ 1,061,208 $ $ 54,121,608 $ 4,608,000 $ (184,320) $ 4,423,680 $ 4,423,680 $ (176,947) $ 4,246,733 $ 4,246,733 $ (169,869) $ 4,076,863 $ 4,076,863 (163,075) 3,913,789 (3,724,720.00) (8,238,155.20) 12,747,698.53 12,732,844.66 Calculate forecast cash flow based upon data to be provided, and decide how to apply net cash inflows (between paying down debt or increasing investments) or to finance net cash outflows (between increasing debt or reducing investments). Justify your decisions. Input data for cash flow forecast Day 0 Balances: ($) Accounts receivable 100,000,000 Inventories 50,000,000 Accounts payable 80,000,000 Other payables 20,000,000 Investment portfolio 50,000,000 (Yields 2% per annum, minimum target level 40,000,000) Revolving credit facility 5,000,000 borrowed, 45,000,000 available, interest rate is 4% per annum Cash balance (i.e. bank demand deposits) 15,000,000 (minimum level targeted) Other forecast cash flow items: Debt maturity (not revolving credit facility) 10,000,000 in Week 3 Interest payment on maturing debt 500,000 in Week 3 Assumptions regarding percent of receivables, inventories, and payables that will convert to cash inflows or outflows during upcoming weeks Account Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Accounts Receivable 20% 25% 25% 10% 10% 10% Inventories 0% 0% 0% 5% 10% 10% Accounts Payable 10% 30% 20% 20% 10% 10% Other payables 50% 25% 25% 0 0 0 Using the Excel template in Cash Flow Forecast Worksheet.xls, create a 6-week cash flow forecast by week and recommend alternatives for the application of net cash inflows or financing of net cash outflows. Assumptions and Inputs Day 0 Balances: Cash Accounts Receivable Inventories Accounts Payable Other Payables Investment Portfolio Revolving Credit Facility Borrowing $ $ $ $ $ $ $ - Cash Conversion Profile of Receivables and Payables: Week Accounts Receivable Inventories Accounts Payable Other Payables 1 2 0% 0% 0% 0% 0% 0% 0% 0% Other Inputs: Bank Revolving Credit Facility Amount Credit Facility Cost $ 0.00% Cash Minimum Target Investments Minimum Target Investment Portfolio Yield $ $ 0.00% Cash Flow Forecast Week Inflow or Outflow 1 2 Accounts Receivable Inventories Accounts Payable Other Payables $ $ $ $ - $ $ $ $ - Net Working Capital Flow $ - $ - Other (e.g. debt principal + interest) $ - $ - Net Cash Flow $ - $ - Beginning Cash Beginning Cash Less Period Net Cash Flow $ $ - $ $ - Minimum Cash Target Balance $ - $ - Cash Balance Surplus (deficit) Versus Minimum Target Balance (amount to allocate) $ - $ - Cash Surplus Allocation / Cash Deficit Financing Beginning Investment Portfolio Addition (Subtraction) Ending Balance $ $ $ - $ $ $ - Beginning Revolving Credit Facility Balance Drawdown (Repayment) Ending Revolving Credit Facility Balance $ $ $ - $ $ $ - Check - - nputs Inputs that you must complete are bordered with red. 3 4 5 6 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 3 4 5 6 cast $ $ $ $ - $ $ $ $ - $ $ $ $ - $ $ $ $ - $ - $ - $ - $ - $ - ### $ - $ - $ - $ - $ - $ - $ $ - $ $ - $ $ - $ $ - $ - $ - $ - $ - $ - $ $ $ $ - $ $ $ $ - $ - $ - $ - $ ### $ $ - $ $ $ - $ ### $ $ - $ $ $ - h Deficit Financing - $ $