In 2020, the management of Doyle and Doyle created a 2021 business plan to launch a new product into its nationwide market. Its 2021-2025 business plan calls for Doyle to make the following capital expenditures and changes in networking capital, depreciation, and interest for its horizon period 2021 through 2025 to support the rollout of its new product and to support its supply chain to its customers.

Doyle anticipates that sales and operating expenses will increase 10% annually for the years 2021 through 2025. After 2025, Doyle projects that its sales and operating expenses will grow at the rate of inflation of 3% annually. Depreciation and Interest will change according to the table above.

Doyle's most recent (2020) financial statements are:

Other financial information for Doyle:

• Income tax rate on pre-tax income 25%

• CAPM rate of return RHIs stock investors require 12%

• Pre-tax interest rate on debt 10%

• Weighted average cost of capital 8%

On a spreadsheet separate INPUT and COMPUTATIONS sections that computes using spreadsheet references / formulas / and functions Doyles free cash flows (FCFs] for the horizon period 2021 - 2015 and the constant growth period that begins in 2026.

Suppose that you calculated for Doyle the following free cash flows for 2021 - 2025 and the constant growth period after 2025.

Create a well-formatted spreadsheet that estimates Doyles INTRINSIC VALUE per SHARE.

Year Capital expenditures 225,000 200,000 2021 Net working capital increases 22,500 20,000 15,000 12,500 7,500 Depreciation 270,000 290,000 305,000 Interest 90,000 95,000 98,750 2022 2023 150,000 2024 125,000 75,000 2025 317,500 325,000 325,000 101,875 103,750 85,000 After 2025 50,000 0 2020 INCOME STATEMENTS (000s) for the years ended December 31, 2020 $6,750,000 22,500 843,750 1,383,750 2,250,000 2,250,000 4,500,000 BALANCE SHEETS (000s) at December 31. ASSETS Cash & equivalents Accounts receivable Inventories Total current assets Plant & equipment, net Total assets LIABILITIES Accounts payable Accruals Notes payable Total current liabilities Long-term bonds @ 5% Total liabilities SHAREHOLDERS' EQUITY Common stock (50 millions shs. outstanding) Retained earnings Total shareholders' equity Total liabilities & shareholders' equity REVENUES Net sales EXPENSES Operating expenses (before depreciation) Depreciation and amortization Total operating expenses Operating income (aka, EBIT) Interest expense Income before income taxes Income tax expense @ 25% Net income 5,886,450 225,000 6,111,450 638,550 84,375 135,000 315,000 247,500 697,500 1,687,500 2,385,000 554,175 138,544 $415,631 292,500 1,822,500 2,115,000 4,500,000 Year 2021 2022 2023 2024 FCF 310,000 350,000 475,000 575,000 725,000 800,000 2025 After 2025