I need all areas bounded with RED IN THE EXCEL ARE FILLED AND COMPLETED. please do the justification and the analysis on a word doc.

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. [Note: An Excel spreadsheet template will be provided.] 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 $ $ $ $ $ $ $ 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 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 $ (4,000,000) Beginning Cash Beginning Cash Less Period Net Cash Flow $ $ 2,000,000 $ 15,000,000 $ 11,000,000 Minimum Cash Target Balance $ 15,000,000 $ 15,000,000 2,000,000 - ### Cash Balance Surplus (deficit) Versus Minimum Target Balance (amount to allocate) $ (13,000,000) $ (4,000,000) Cash Surplus Allocation / Cash Deficit Financing Beginning Investment Portfolio Addition (Subtraction) Ending Balance $ $ $ 50,000,000 50,000,000 Beginning Revolving Credit Facility Balance Drawdown (Repayment) Ending Revolving Credit Facility Balance $ $ $ 5,000,000 5,000,000 Check (13,000,000.00) $ 50,000,000 $ (4,000,000) $ 46,000,000 $ $ $ 5,000,000 5,000,000 - 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 ast $ 25,000,000 $ 10,000,000 $ $ 2,500,000 $ (16,000,000) $ (16,000,000) $ (5,000,000) $ $ $ 4,000,000 - $ 10,000,000 $ 10,000,000 $ 5,000,000 $ 5,000,000 $ (8,000,000) $ (8,000,000) $ $ - $ (3,500,000) $ ### $ $ (3,500,000) $ 7,000,000 $ - $ 7,000,000 $ 7,000,000 - $ 4,000,000 7,000,000 $ $ 15,000,000 19,000,000 $ 15,000,000 $ 15,000,000 $ 15,000,000 $ 11,500,000 $ 22,000,000 $ 22,000,000 $ 15,000,000 $ 15,000,000 $ 15,000,000 $ 15,000,000 $ 4,000,000 $ (3,500,000) $ 7,000,000 $ 7,000,000 Deficit Financing $ $ $ $ $ $ 46,000,000 46,000,000 $ 46,000,000 $ 46,000,000 $ 46,000,000 ### $ $ $ 46,000,000 $ 46,000,000 $ 46,000,000 5,000,000 $ 5,000,000 $ 4,000,000.00 5,000,000 $ 5,000,000 $ 5,000,000 ### $ $ 5,000,000 $ 5,000,000 $ 5,000,000 (3,500,000.00) 7,000,000.00 7,000,000.00