Thank you for your help!

Financial Statement Exercises #2 Begin by reading the entire assignment carefully. Part 1: Building from your prior assignment and the prior balance sheet (provided below) create the statement of cash flows. Only reference the income statement and balance sheets. When complete, the BS and SCF ending cash balances should be equal. Time 0 Balance Sheet 39,000 Accounts Payable 5,000 Accounts Receivable 5,000 Accruals 1,000 Inventory 50,000 Current Portion - Long Term Debt Total Current Assets 94,000 Total Current Liabilities 6,000 Cash Gross Property, plant and Equipment 350,000 Long Term Debt 121,500 Accumulated Depreciation 75,000 Net Property, plant and Equipment 275,000 Common Stock 121,500 Retained Earnings 120,000 Total Assets 369,000 Total Equity and Liabilities 369,000 Create a formula to calculate the ending balance sheet's cash value. Starting from the total liabilities and owners' equity, subtract off all asset accounts besides cash (be sure not to double count by including individual line items and subtotals). This value should be equal to the cash value input for the balance sheet. Insert your formula into the cash cell and remove the cash balance input. Your model will now adjust to changes in other assumptions and maintain balance. 1. What is the EOY cash balance if sales are 10% less than projected? 2. Assuming 10% lower sales (as in the prior problem), which of the following accompanying changes result in a positive EOY cash balance? Provide the updated cash balance. a. Increase AR to 30,000 b. Increase AP to 30,000 Increase depreciation to 75,000 d. Decrease purchases to 200,000 C. Part 2: Given the following information, build projected financial statements for years 1 to 5 (and also the time O balance sheet) Multi-Period Financial Statement Inputs Beginning Balances Represent the start of the Company Accounts Payable (as % of past year's COGS) 8.33% Accounts Receivable (as % of past year's Sales) 16.6796 Starting Accruals 10,000 Accrual Growth Rate 09 Common Stock 400,000 Gross Margin 50% Depreciation Expense (as % of BOY Net Assets) 10% First year dividend 40,000 Dividend growth rate 5% Gross Property, Plant, and Equipment 400,000 Gross PPE Growth Rate 10% LTD interest Rate (applied to BOY balances) 796 Long Term Debt 120,000 LTD Raised in Year 2 (EOY) 7 Net Sales 300,000 Sales Growth Rate 12% Operating Expenses (excluding depreciation) 40,000 Opex Growth Rate 15% Inventory (as % of next year's Sales) 20% Tax rate 40% Determine how much debt should be raised at the end of year 2 to keep cash positive through the projection period. Use Solver. Search for the solution by changing your EOY2 LTD amount input until your year 5 cash balance is zero. 1. How much cash should you raise to avoid a negative cash balance in any year? 2. How much cash should you raise to ensure you have at least $10,000 at the end of every year? Financial Statement Exercises #2 Begin by reading the entire assignment carefully. Part 1: Building from your prior assignment and the prior balance sheet (provided below) create the statement of cash flows. Only reference the income statement and balance sheets. When complete, the BS and SCF ending cash balances should be equal. Time 0 Balance Sheet 39,000 Accounts Payable 5,000 Accounts Receivable 5,000 Accruals 1,000 Inventory 50,000 Current Portion - Long Term Debt Total Current Assets 94,000 Total Current Liabilities 6,000 Cash Gross Property, plant and Equipment 350,000 Long Term Debt 121,500 Accumulated Depreciation 75,000 Net Property, plant and Equipment 275,000 Common Stock 121,500 Retained Earnings 120,000 Total Assets 369,000 Total Equity and Liabilities 369,000 Create a formula to calculate the ending balance sheet's cash value. Starting from the total liabilities and owners' equity, subtract off all asset accounts besides cash (be sure not to double count by including individual line items and subtotals). This value should be equal to the cash value input for the balance sheet. Insert your formula into the cash cell and remove the cash balance input. Your model will now adjust to changes in other assumptions and maintain balance. 1. What is the EOY cash balance if sales are 10% less than projected? 2. Assuming 10% lower sales (as in the prior problem), which of the following accompanying changes result in a positive EOY cash balance? Provide the updated cash balance. a. Increase AR to 30,000 b. Increase AP to 30,000 Increase depreciation to 75,000 d. Decrease purchases to 200,000 C. Part 2: Given the following information, build projected financial statements for years 1 to 5 (and also the time O balance sheet) Multi-Period Financial Statement Inputs Beginning Balances Represent the start of the Company Accounts Payable (as % of past year's COGS) 8.33% Accounts Receivable (as % of past year's Sales) 16.6796 Starting Accruals 10,000 Accrual Growth Rate 09 Common Stock 400,000 Gross Margin 50% Depreciation Expense (as % of BOY Net Assets) 10% First year dividend 40,000 Dividend growth rate 5% Gross Property, Plant, and Equipment 400,000 Gross PPE Growth Rate 10% LTD interest Rate (applied to BOY balances) 796 Long Term Debt 120,000 LTD Raised in Year 2 (EOY) 7 Net Sales 300,000 Sales Growth Rate 12% Operating Expenses (excluding depreciation) 40,000 Opex Growth Rate 15% Inventory (as % of next year's Sales) 20% Tax rate 40% Determine how much debt should be raised at the end of year 2 to keep cash positive through the projection period. Use Solver. Search for the solution by changing your EOY2 LTD amount input until your year 5 cash balance is zero. 1. How much cash should you raise to avoid a negative cash balance in any year? 2. How much cash should you raise to ensure you have at least $10,000 at the end of every year