Problem 9. Please provide excel worksheets WITH AND WITHOUT FORMULAS. I will not be able to complete this problem without them. If provided, a thumbs up will be given.

Problem 9 Intro Historical financial statements for Idle Mind Inc. are given below. Income statement: (in 5 million) 2018 2019 2020 Revenue 6,499 6,824 7,165 Cost of goods sold 4.159 4367 4,586 Gross profit 2,340 2,457 2.579 Fixed expenses 300 300 300 Variable expenses 1,430 1,501 1,576 Depreciation 263 292 307 Operating income 347 364 396 Other income 78 78 78 EBIT 425 442 474 Interest expenses 126 105 109 EBT 299 337 365 Taxes 90 101 110 Net Income 209 236 255 Dividends paid 80 80 80 Add to ret eam 129 156 175 Balance sheet: (in 5 million) 2018 2019 2020 Cash 130 136 143 Accounts receivable 910 955 1,003 Inventories 1,690 1,774 1,863 Current assets 2,730 2,866 3,009 Net fixed assets 2,925 3,071 3,224 Total assets 5,655 5,937 6,233 Accounts payable 1,170 1,228 1,290 Short-term debt 120 99 104 Current liabilities 1,290 1,327 1,394 Long-term debt 1.985 2,074 2,128 Total liabilities 3,275 3,401 3,522 Paid-in capital 200 200 200 Retained earnings 2,180 2,336 2,511 Total equity 2,380 2,536 2,711 Total liab. & equity 5,655 5,937 6,233 . For the pro forma income statement, assume the following Revenue will grow by 5% per year for the next 5 years . Cost of goods sold will be 64% of revenue Fixed expenses will stay constant at $300 million per year Variable expenses will be 22% of revenue Depreciation will be 10% of net fixed assets from the previous year. Other income will stay constant at $78 million . Interest expenses are 5% of short-term and long-term debt from the previous year Taxes will be 30% of taxable income The company will pay out S80 million in dividends each year For the pro forma balance sheet assume the following Cash will be 2% of revenue Accounts receivable will be 14% of revenue Inventories will be 26% of revenue Nat fixed assets will be 45% of revenue Accounts payable will be 18% of revenue Short-term debt will be 5% of long-term debt from the previous year The company will issue and repurchase long-term debt (bonds) to dose any funding gaps and use up any surplus capital Paid-in capital will stay constant at $200 million ie there won't be any share issues or repurchases - Attempt 1/10 for 10 pts Part 1 What is net income in 2021 (in S million)? 0+ decimals Submit Part 2 B Attempt 1/10 for 10 pts. What level of long-term debt is needed to balance the balance sheet in 2021 (in $ million)? 0+ decimals Submit Part 3 E- Attempt 1/10 for 10 pts. What level of long-term debt is needed to balance the balance sheet in 2022 (in $ million)? 0. decimals Submit Part 4 B Attempt 1/10 for 10 pts What is the discretionary financing needed in 2023 (in 5 miliony? Submi Part 5 IB Attempt 1/10 for 10 pts. What is the discretionary financing needed in 2025 (in 9 million)? 2+ decimals Submit