Financial Planning and Forecasting: Forecasted Financial Statements The APN equation provides useful insights into the forecasting process, but this equation assumes that all of the firm's key ratios remain constant, which is not likely to hold true. Consequently, it is useful to forecast the firm's financial statements. The firm begins with forecasting its which the feed into the firm's balance sheet. Management looks at person abs and the relationship with industry and benchmark averages. The forecasted income statement begins with the prior year's income statement and adjusted for the sales growth forecast. Soments for the income statement are not under the firm's control-for example, tax and interest rates. The forecasted balance sheet is calculated from stratos that management has reviewed and changed based on industry and benchmark averages. An Excel spreadsheet is used for this may because changes in assumptions, financing and rates can be made to the statements to review anternative scenarios. The impact of these changes on the firm's forecasted financial statementstimately can be used to improve the firm's operations Quantitative Problem. At the end of last year, Edwin In reported the following income statement in millions of dollars) Sales Operating costs excluding depreciation EBITDA Depreciation $4.150.00 3.081.00 $1,069.00 320.00 $749.00 150.00 $599.00 239.60 $350.40 Interest EBT Tawes (40%) Net income Looking ahead to the following year, the company's CFO has assembled this information: Year-end sales are expected to be higher than $4.15 billion in sales generated last year. Year-end operating costs, excluding depreciation will equal 60% of sales. Depreciation costs are expected to increase at the same rate as sales Interest costs are expected to remain unchanged. The tax rate is expected to remain at 40%. On the basis of this information, what will be the forecast for Edwin's year-end net income? Enter your answers in millions. Round your answers to two decimal places. Do not found intermediate calculations. Enter al values as positive numbers. (in millions of dollars) Sales Operating costs excluding depreciation EBITDA Depreciation EBIT Interest Taxes Net Income Excel Online Structured Activity: Excess capacity Earleton Manufacturing Company has $2 billion in sales and $800,000,000 in fixed assets. Currently, the company's fixed assets are operating at 85% of capacity. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Open spreadsheet 3. What level of sales could Earleton have obtained if it had been operating at full capacity? Write out your answer completely. Round your answer to the nearest cent. b. What is Earleton's target fixed assets/sales ratio? Round your answer to two decimal places c. If Earleton's sales increase 35%, how large of an increase in foed assets will the company need to meet its target fixed assets/sales ratio? Write out your answer completely. Do not round intermediate calculations. Round your answer to the nearest dollar. E F G H I Excess capacity 4 5 Sales Fixed assets % Fixed assets capacity $2,000,000,000.00 $800,000,000.00 85.00% Formulas 7 8 Cacluation of Full Capacity Sales: Full capacity sales ANA 10 Calculation of Target Fixed Assets-to-Sales Ratio: 11 Target fixed assets-to-sales ratio #N/A 13 % Sales increase 35.00% NA 15 Calculation of Fixed Assets Increase Needed: 16 New sales level 17 Increase in fixed assets #N/A
