Hi,

Please can a tutor help me by looking at my working excel sheet of an indirect cash flow statement. I believe that rows 42, 56 and 60 are incorrect. These rows involve the following:

Row 42 is to calculate the "Other (e.g. debt principal + interest)" in the cash flow forecast

Row 56 is to calculate the "Addition (Subtraction)" in the Cash Surplus Allocation / Cash Deficit Financing

Row 60 is to calculate the "Drawdown (Repayment)" in the Cash Surplus Allocation / Cash Deficit Financing

I have attached my working spread sheet, the original template and the data inputs and assignment question for your reference.

I simply need help in ensuring the data and formulas are correct in this spreadsheet. Because only once the data is complete will I be able to write an analysis.

Many thanks and 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 $ 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 - $ $