FIN 451-Excel Project #1. Microsoft Excel Starter Home Insert Page Layout Formulas AutoSum Anial Wrap Text B 1 u , 2 , Or, A, Merge & Center, ss conditional Format . Cell Formatting as Table Styles Insert Delete Format Sort & Find & Spelling Purchase ,P EE $ , % , 2 ClearFilter Select Editing Clipboard Font Alignment Number Proofing Upgrade A18 18 19 3) Create a pro-forma income statement and balance sheet using the percent of sales approach in order to 20 forecast the extemal finance needed for fiscal year 2018. Remember, under this approach, some financial 21 statement information varies with sales, while some do not. 22-I have forecasted the pro-forma total revenues for you based upon the four-year total sales growth rate of a Get Starte Getting Started Guide Hep and Support sales growth rate of Do More Get Free Templates Dowrload Cip Art 24 carried over from the previous 25 -Green cells represent items that vary directly with sales. These items are calculated as a perentage of the 26 forecasted total revenues, with percentages calculated as the 3-year average percent of sales over 2015- 27Note: You do not already have previous years percent of sales for balance sheet items. Keep this in mind 28 Get More Get Microsoft PowerPoint or Microsoft when calculating the green cells in the pro-forma balance sheet Depreciation is based upon cost of sales, NOT total r 30 Yellow cells are calcuation cells 31 32 33 Provision for income taxes is based upon forecasted income before income taxes and the 3-year average 34 taxe rate that I have formulated for you at the bottom of the pro-forma income statement 35 36 Interest expense on automotve debt is calcuated as interest rate on automotve debttimes the of forecasted automotive long-term debt and automotive debt payable in one year Total cash dividends paid are based upon basic shares outstanding I have formulated dlvidends per share for you based upon the 3-y9ar average diidend payout ratio. 37 The External Finance Needed (EFN) calculation is at the bottorm of the pro-forma balance sheet and 38 must be calculated using Excel's solver function. 39 If total assets is less than total liabilities and equity on the pro-forma, our plug variable is cash. It total is greater than total liabilities and equity, our plug variable is automotive long-term debt However, this is 41 as simple as simply entering the difference as the plug variable in the pro-forma This is because to these variables lead to simutaneous changes in other financial statementvariables that will cause the balance sheetto remain unbalanced. we can use the solver function in Excel to accomplish this One of the purposes of this assignment is for you to research and learn how to use this Excel function. Therefore, you must research and use the solver function to bring your pro-forma balance sheet into Note: When the solver function is used correctly, the pro-forma balance sheet will balance and EFN will equal zero Microsoft 45 3Office 48 Entering raw numbers without formulas or cell references will result in a significant deduction in 49 points for all calculation requirements. 50 51 4) The Ratios Ford tab below contains the ratos for Ford for FYE'S 2013-2016. It also contains the most Instructions Ford Income Statement FordBalance Sheet Ford Ratios FordLiquidity Asset ManagementLeverage Profitabirty Ready | |90% 92018 * 12:25 PM O Type here to search FIN 451-Excel Project #1. Microsoft Excel Starter Home Insert Page Layout Formulas AutoSum Anial Wrap Text B 1 u , 2 , Or, A, Merge & Center, ss conditional Format . Cell Formatting as Table Styles Insert Delete Format Sort & Find & Spelling Purchase ,P EE $ , % , 2 ClearFilter Select Editing Clipboard Font Alignment Number Proofing Upgrade A18 18 19 3) Create a pro-forma income statement and balance sheet using the percent of sales approach in order to 20 forecast the extemal finance needed for fiscal year 2018. Remember, under this approach, some financial 21 statement information varies with sales, while some do not. 22-I have forecasted the pro-forma total revenues for you based upon the four-year total sales growth rate of a Get Starte Getting Started Guide Hep and Support sales growth rate of Do More Get Free Templates Dowrload Cip Art 24 carried over from the previous 25 -Green cells represent items that vary directly with sales. These items are calculated as a perentage of the 26 forecasted total revenues, with percentages calculated as the 3-year average percent of sales over 2015- 27Note: You do not already have previous years percent of sales for balance sheet items. Keep this in mind 28 Get More Get Microsoft PowerPoint or Microsoft when calculating the green cells in the pro-forma balance sheet Depreciation is based upon cost of sales, NOT total r 30 Yellow cells are calcuation cells 31 32 33 Provision for income taxes is based upon forecasted income before income taxes and the 3-year average 34 taxe rate that I have formulated for you at the bottom of the pro-forma income statement 35 36 Interest expense on automotve debt is calcuated as interest rate on automotve debttimes the of forecasted automotive long-term debt and automotive debt payable in one year Total cash dividends paid are based upon basic shares outstanding I have formulated dlvidends per share for you based upon the 3-y9ar average diidend payout ratio. 37 The External Finance Needed (EFN) calculation is at the bottorm of the pro-forma balance sheet and 38 must be calculated using Excel's solver function. 39 If total assets is less than total liabilities and equity on the pro-forma, our plug variable is cash. It total is greater than total liabilities and equity, our plug variable is automotive long-term debt However, this is 41 as simple as simply entering the difference as the plug variable in the pro-forma This is because to these variables lead to simutaneous changes in other financial statementvariables that will cause the balance sheetto remain unbalanced. we can use the solver function in Excel to accomplish this One of the purposes of this assignment is for you to research and learn how to use this Excel function. Therefore, you must research and use the solver function to bring your pro-forma balance sheet into Note: When the solver function is used correctly, the pro-forma balance sheet will balance and EFN will equal zero Microsoft 45 3Office 48 Entering raw numbers without formulas or cell references will result in a significant deduction in 49 points for all calculation requirements. 50 51 4) The Ratios Ford tab below contains the ratos for Ford for FYE'S 2013-2016. It also contains the most Instructions Ford Income Statement FordBalance Sheet Ford Ratios FordLiquidity Asset ManagementLeverage Profitabirty Ready | |90% 92018 * 12:25 PM O Type here to search