How do I format and solve this in excel? Thank you in advance.

Capital Budgeting Problem 3 Karsten Golf is a smaller sports equipment company that specializes in high-quality, lower-priced golf clubs. The total market value of the company (debt, preferred and equity combined) is $100 million. The market value of outstanding debt for the firm is $40 million. The bonds issued by the firm will mature in 10 years, have a coupon rate of 6.2% (paid semiannually), have a current market price of $1098, and have a face value of $1000. Karsten has 250,000 shares of preferred stock outstanding with a current market price of $100 (that is, the current market price is par value). The return required on the preferred stock (that is, the cost of capital for preferred, Ip) is 6%. The current market price of a share of Karsten equity is selling for $80. Earnings next year are expected to be $10 per share and the growth rate in earnings is expected to be about 4% per year forever. The firm has a dividend payout ratio of 40 percent. Treasury bills (the risk-free asset) are currently providing a return of about 3.7% per year. The tax rate for Karsten is 35 percent Karsten Golf has decided to sell a new line of golf clubs. The clubs will sell for $600 per set and have a variable cost of $240 per set. The company has spent $150,000 for a marketing study that determined the company will sell 50,000 of the new line of clubs per year for seven years. The marketing study also determined that the company will lose sales of 12,000 sets per year of its high-priced clubs. The high-priced clubs sell at $1000 and have variable costs of $550. The company will also increase sales of its cheap clubs by 10,000 sets per year. The cheap clubs sell for $300 and have variable costs of $100 per set. The fixed costs associated with the manufacturing of the new line of clubs will be $8,000,000 per year. The plant and equipment for the new line of clubs will cost $28,000,000 and will be depreciated on a straight-line basis. The expected salvage value of the equipment at that time is zero. The new clubs will also require an increase in net working capital of $800,000 that will be returned at the end of the project. Do you recommend that Karsten proceed with this project? Clearly present and justify your recommendation. Year 1 Year 2 Year 7 Change in Sales Change in Cost of Goods Sold Change in Depreciation Change in EBIT Change in Taxes Change in Net Income Change in Depreciation Change in Operating CF Flow Change in Net Working Capital Capital Expenditures (CAPEX) Project Cash Flow SIMULATION: There is a significant probability that there will be increasing softness in the price of golf equipment; the cost forecasts are expected not to change. The standard deviation in price for all of the different sets is forecast to be 15% of the club expected set price. In addition, the sales forecasts are forecast to have a standard deviation of 10% of the forecast expected sales. The realizations for all of these variables will be realized in the first year and then carried throughout the life of the project. Do you recommend that Karsten proceed with the project? What additional insights can you provide? Capital Budgeting Problem 3 Karsten Golf is a smaller sports equipment company that specializes in high-quality, lower-priced golf clubs. The total market value of the company (debt, preferred and equity combined) is $100 million. The market value of outstanding debt for the firm is $40 million. The bonds issued by the firm will mature in 10 years, have a coupon rate of 6.2% (paid semiannually), have a current market price of $1098, and have a face value of $1000. Karsten has 250,000 shares of preferred stock outstanding with a current market price of $100 (that is, the current market price is par value). The return required on the preferred stock (that is, the cost of capital for preferred, Ip) is 6%. The current market price of a share of Karsten equity is selling for $80. Earnings next year are expected to be $10 per share and the growth rate in earnings is expected to be about 4% per year forever. The firm has a dividend payout ratio of 40 percent. Treasury bills (the risk-free asset) are currently providing a return of about 3.7% per year. The tax rate for Karsten is 35 percent Karsten Golf has decided to sell a new line of golf clubs. The clubs will sell for $600 per set and have a variable cost of $240 per set. The company has spent $150,000 for a marketing study that determined the company will sell 50,000 of the new line of clubs per year for seven years. The marketing study also determined that the company will lose sales of 12,000 sets per year of its high-priced clubs. The high-priced clubs sell at $1000 and have variable costs of $550. The company will also increase sales of its cheap clubs by 10,000 sets per year. The cheap clubs sell for $300 and have variable costs of $100 per set. The fixed costs associated with the manufacturing of the new line of clubs will be $8,000,000 per year. The plant and equipment for the new line of clubs will cost $28,000,000 and will be depreciated on a straight-line basis. The expected salvage value of the equipment at that time is zero. The new clubs will also require an increase in net working capital of $800,000 that will be returned at the end of the project. Do you recommend that Karsten proceed with this project? Clearly present and justify your recommendation. Year 1 Year 2 Year 7 Change in Sales Change in Cost of Goods Sold Change in Depreciation Change in EBIT Change in Taxes Change in Net Income Change in Depreciation Change in Operating CF Flow Change in Net Working Capital Capital Expenditures (CAPEX) Project Cash Flow SIMULATION: There is a significant probability that there will be increasing softness in the price of golf equipment; the cost forecasts are expected not to change. The standard deviation in price for all of the different sets is forecast to be 15% of the club expected set price. In addition, the sales forecasts are forecast to have a standard deviation of 10% of the forecast expected sales. The realizations for all of these variables will be realized in the first year and then carried throughout the life of the project. Do you recommend that Karsten proceed with the project? What additional insights can you provide