Hi,

Please only answer this question if you under indirect statement of cash flows, net working capital, net cash flows, cash balance versus minimum target balance etc. I have had 3 tutors incorrectly do this and I really just want someone who understands this to help me now.

1) Attached is my spreadsheet. I'm concerned with rows 42, 57 and 60 in particular. I think the formulas used are wrong. Can you check?

2) Can you explain to me what is the significance of using this type of statement of cash flow?

3) what is the significance of these figures? What is it telling me or at least where are the key areas/figures I should be looking at in order to finish this assignment.

4) detail of the full assignment and data to be inputted into the spread sheet is attached for your information

5) I've attached the original excel template for your information

I just want someone who knows and understand this to check my spread sheet and explain to me how to finish the spread sheet and WHY it is done this way so I can actually understand this.

thanks!

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 $ 50,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 Accounts Receivable Inventories Accounts Payable Other Payables $ $ $ $ Net Working Capital Flow Other (e.g. debt principal + interest) $ 100,000,000 12,500,000 (80,000,000) (20,000,000) 1 2 $ $ $ $ 20,000,000 (8,000,000) (10,000,000) $ 25,000,000 $ $ (24,000,000) $ (5,000,000) $ 2,000,000 $ (4,000,000) (10,542,775) $ (3,846) $ - Net Cash Flow $ 1,996,154 $ (4,000,000) Beginning Cash Beginning Cash Plus Period Net Cash Flow $ $ 15,000,000 16,996,154 $ 15,000,000 $ 11,000,000 Minimum Cash Target Balance $ 15,000,000 $ 15,000,000 Cash Balance Surplus (deficit) Versus Minimum Target Balance (amount to allocate) $ 1,996,154 $ (4,000,000) Cash Surplus Allocation / Cash Deficit Financing Beginning Investment Portfolio Addition (Subtraction) Ending Balance $ $ $ 50,000,000 $ 50,019,231 19,231 $ 19,231 50,019,231 $ 50,038,462 Beginning Revolving Credit Facility Balance Drawdown (Repayment) Ending Revolving Credit Facility Balance $ $ $ 5,000,000 $ (2,015,385) $ 2,984,615 $ Check - 2,984,615 4,019,231 7,003,846 - nputs 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) $ ast 4,000,000 $ (10,500,000) $ - $ 10,000,000 $ 10,000,000 $ 5,000,000 $ 5,000,000 $ (8,000,000) $ (8,000,000) $ - $ - $ $ (6,500,000) $ (3,500,000) $ 7,000,000 $ 7,000,000 (31,195) $ (7,734) 6,968,805 $ 6,992,266 $ 15,000,000 $ 8,500,000 $ 15,000,000 $ 11,500,000 $ 15,000,000 $ 21,968,805 $ 15,000,000 $ 21,992,266 $ 15,000,000 $ 15,000,000 $ 15,000,000 $ 15,000,000 $ (6,500,000) $ (3,500,000) $ 6,968,805 $ 6,992,266 Deficit Financing $ 50,038,462 $ 50,057,692 $ 19,231 $ 19,231 $ 50,057,692 $ 50,076,923 $ 7,003,846 $ 6,519,231 $ 13,523,077 - $ 50,076,923 $ 19,231 $ 50,096,154 $ 50,096,154 $ 19,231 $ 50,115,385 $ 13,523,077 $ 17,042,308 $ 10,054,272 $ 3,519,231 $ (6,988,036) $ (7,011,497) $ 17,042,308 $ 10,054,272 $ 3,042,775 - - - 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 - $ $