AutoSave Financial_Report Home Insert Draw Page Layout Formulas Data Review View @ Tell me 5. Relying on the assumptions listed below: a. Prepare a complete pro forma forecast of Kohl's 2023 income statement and year-end balance sheet. (This is for the fiscal year 2023 which ends in January 2024). b. Determine the AFN using the pro forma balance sheet. Does Kohl's need additional funds, or do they have excess funds? Provide ideas for raising funds if additional funds are needed or ideas for spending funds if they have excess funds.| This guidance is based on the following assumptions: - Total sales change of 3% (average of range 2% to 4% ). - Gross margin (GM) as a percentage of sales of 33% (Use this to calculate GM and back into Cost of Merchandise Sold). - Depreciation expense of $808 million - Operating Margin as a percentage of sales of 4%. (Use this to calculate Operating Income and back into total operating costs, which is the difference between gross profit and operating income. Subtract depreciation expense to back into Selling, General, and Administrative Expenses). No additional stores closing so no Impairment Expense, Gains/Losses on real estate or Gains/Losses on Extinguishment of Debt. These expenses are not expected for 2023 fiscal year. - Interest expense of $286 million - Effective tax rate of 23.05% - Ignore Net Income (loss) per share calculations. - 2023 Dividend of $2.00 per share with 111 million shares outstanding. - Operating current assets and operating current liabilities change with sales. - Capital expenditures of $625 million (increase in Property and Equipment; still need to subtract depreciation to find Property and Equipment, net). - Retirement of $111 million in bonds (reduction in LT debt). - No expected change in leases or common stock. - Retained earnings are calculated using the Statement of Retained Earnings. AutoSave Financial_Report Home Insert Draw Page Layout Formulas Data Review View @ Tell me 5. Relying on the assumptions listed below: a. Prepare a complete pro forma forecast of Kohl's 2023 income statement and year-end balance sheet. (This is for the fiscal year 2023 which ends in January 2024). b. Determine the AFN using the pro forma balance sheet. Does Kohl's need additional funds, or do they have excess funds? Provide ideas for raising funds if additional funds are needed or ideas for spending funds if they have excess funds.| This guidance is based on the following assumptions: - Total sales change of 3% (average of range 2% to 4% ). - Gross margin (GM) as a percentage of sales of 33% (Use this to calculate GM and back into Cost of Merchandise Sold). - Depreciation expense of $808 million - Operating Margin as a percentage of sales of 4%. (Use this to calculate Operating Income and back into total operating costs, which is the difference between gross profit and operating income. Subtract depreciation expense to back into Selling, General, and Administrative Expenses). No additional stores closing so no Impairment Expense, Gains/Losses on real estate or Gains/Losses on Extinguishment of Debt. These expenses are not expected for 2023 fiscal year. - Interest expense of $286 million - Effective tax rate of 23.05% - Ignore Net Income (loss) per share calculations. - 2023 Dividend of $2.00 per share with 111 million shares outstanding. - Operating current assets and operating current liabilities change with sales. - Capital expenditures of $625 million (increase in Property and Equipment; still need to subtract depreciation to find Property and Equipment, net). - Retirement of $111 million in bonds (reduction in LT debt). - No expected change in leases or common stock. - Retained earnings are calculated using the Statement of Retained Earnings