Need check figures for the Balance Sheet portion only, all information from the 10K is already plugged in.
9 Use Ford's 10-K annual report from Edgar to find financial information for analysis Click on the link for "Consolidated Income Statement" in the Table of Contents and enter the income statement information into the highlighted cells in the 'Income Statement Ford' tab for FYE 12/31/2017 12 13 Note: I have already included the previous 4 years' information to guide you - Click on the link for "Consolidated Balance Sheet" in the Table of Contents and enter the balance sheet information into the highlighted cells in the 'Balance Sheet Ford' tab for FYE 12/31/2017 Note: I have already included the previous 4 years' information to guide you The common-size perentages are already formulated and will appear as you fill in the income statement and balance sheet information for FYE 2017 15 17 18 19 3) Create a pro-forma income statement and balance sheet using the percent of sales approach in order to 20 forecast the external 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 Ford 23 24 25 26 27 - Blue pro-forma cells represent items that do not vary with sales. These items can simply be carried over from the previous FYE - Green cells represent items that vary directly with sales. These items are calculated as a percentage of the forecasted total revenues, with percentages calculated as the 3-year average percent of sales over 2015-2017 Note: You do not already have previous years' percent of sales for balance sheet items. Keep this in mind when calculating the green cells in the pro-forma balance sheet Depreciation is based upon cost of sales, NOT total revenues 29 30 31 32 Yellow cells are calcuation cells Interest expense on automotive debt is calcuated as interest rate on automotive debt times the combination of forecasted automotive long-term debt and automotive debt payable in one year Provision for income taxes is based upon forecasted income before income taxes and the 3-year average taxe rate that I have formulated for you at the bottom of the pro-forma income statement Total cash dividends paid are based upon basic shares outstanding. I have formulated dividends per share for you based upon the 3-year average dividend payout ratio 34 35 36 37 38 39 A0 41 42 43 - The External Finance Needed (EFN) calculation is at the bottom of the pro-forma balance sheet and must be calculated using Excel's solver function. If total assets is less than total liabilities and equity on the pro-forma, our plug variable is cash. If total assets is greater than total liabilities and equity, our plug variable is automotive long-term debt. However, this is not as simple as simply entering the difference as the plug variable in the pro-forma. This is because changes to these variables lead to simultaneous changes in other financial statement variables that will cause the balance sheet to 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 45 Therefore, you must research and use the solver function to bring your pro-forma balance sheet into balance Note: When the solver function is used correctly, the pro-forma balance sheet will balance and EFN will equal zero 47 48 Entering raw numbers without formulas or cell references will result in a significant deduction in Apoints for all calculation requirements, Instructions Ford Income Statement FordBalance Sheet Ford Ratios FordLiquidity 9 Use Ford's 10-K annual report from Edgar to find financial information for analysis Click on the link for "Consolidated Income Statement" in the Table of Contents and enter the income statement information into the highlighted cells in the 'Income Statement Ford' tab for FYE 12/31/2017 12 13 Note: I have already included the previous 4 years' information to guide you - Click on the link for "Consolidated Balance Sheet" in the Table of Contents and enter the balance sheet information into the highlighted cells in the 'Balance Sheet Ford' tab for FYE 12/31/2017 Note: I have already included the previous 4 years' information to guide you The common-size perentages are already formulated and will appear as you fill in the income statement and balance sheet information for FYE 2017 15 17 18 19 3) Create a pro-forma income statement and balance sheet using the percent of sales approach in order to 20 forecast the external 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 Ford 23 24 25 26 27 - Blue pro-forma cells represent items that do not vary with sales. These items can simply be carried over from the previous FYE - Green cells represent items that vary directly with sales. These items are calculated as a percentage of the forecasted total revenues, with percentages calculated as the 3-year average percent of sales over 2015-2017 Note: You do not already have previous years' percent of sales for balance sheet items. Keep this in mind when calculating the green cells in the pro-forma balance sheet Depreciation is based upon cost of sales, NOT total revenues 29 30 31 32 Yellow cells are calcuation cells Interest expense on automotive debt is calcuated as interest rate on automotive debt times the combination of forecasted automotive long-term debt and automotive debt payable in one year Provision for income taxes is based upon forecasted income before income taxes and the 3-year average taxe rate that I have formulated for you at the bottom of the pro-forma income statement Total cash dividends paid are based upon basic shares outstanding. I have formulated dividends per share for you based upon the 3-year average dividend payout ratio 34 35 36 37 38 39 A0 41 42 43 - The External Finance Needed (EFN) calculation is at the bottom of the pro-forma balance sheet and must be calculated using Excel's solver function. If total assets is less than total liabilities and equity on the pro-forma, our plug variable is cash. If total assets is greater than total liabilities and equity, our plug variable is automotive long-term debt. However, this is not as simple as simply entering the difference as the plug variable in the pro-forma. This is because changes to these variables lead to simultaneous changes in other financial statement variables that will cause the balance sheet to 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 45 Therefore, you must research and use the solver function to bring your pro-forma balance sheet into balance Note: When the solver function is used correctly, the pro-forma balance sheet will balance and EFN will equal zero 47 48 Entering raw numbers without formulas or cell references will result in a significant deduction in Apoints for all calculation requirements, Instructions Ford Income Statement FordBalance Sheet Ford Ratios FordLiquidity
