if possible show the excel formulas
The following sheet presents the balance sheet and value drivers of Yummy Company, which manufactures a special tomato sauce Yummy Company, Financial Model Value Drivers Sales growth Current assets/Sales Current liabilities/Sales Net fixed assets growth rate Cost of goods sold/sales Depreciation rate (of average fixed assets at cost interest rate on debt interest earned on cash balances tax rate dividend payout ratio sales WACC long term FCF growth rate 12% 22% 20% 5% 45% 20% 8% 4% 36% 25% 2000000 16% 496 Balance sheet Cash Current assets Fixed Assets 460000 440000 At cost 500000 8500000 4400000 reciation Net fixed assets total assets Current liabilities Debt Stock (1500000 shares, issued at $0.5 each accumulated retained earnings Total Liabilities Equit 400000 750000 250000 4400000 Additional model assumptions are as follows: .The FCF evaluation is for a 5-year period. In addition, a terminal value should be determined using the long-term FCF growth rate The debt principal repayments are $300,000 each year. Cash is a plug in the model. * Make a pro forma model for Yummy and computer the firm value using a DCF valuation model with year-end discounting. (Firm value: -2206428.553; stock price: -3.16) a. b. Show in a graph the sensitivity of the enterprise value to the growth in sales; The following sheet presents the balance sheet and value drivers of Yummy Company, which manufactures a special tomato sauce Yummy Company, Financial Model Value Drivers Sales growth Current assets/Sales Current liabilities/Sales Net fixed assets growth rate Cost of goods sold/sales Depreciation rate (of average fixed assets at cost interest rate on debt interest earned on cash balances tax rate dividend payout ratio sales WACC long term FCF growth rate 12% 22% 20% 5% 45% 20% 8% 4% 36% 25% 2000000 16% 496 Balance sheet Cash Current assets Fixed Assets 460000 440000 At cost 500000 8500000 4400000 reciation Net fixed assets total assets Current liabilities Debt Stock (1500000 shares, issued at $0.5 each accumulated retained earnings Total Liabilities Equit 400000 750000 250000 4400000 Additional model assumptions are as follows: .The FCF evaluation is for a 5-year period. In addition, a terminal value should be determined using the long-term FCF growth rate The debt principal repayments are $300,000 each year. Cash is a plug in the model. * Make a pro forma model for Yummy and computer the firm value using a DCF valuation model with year-end discounting. (Firm value: -2206428.553; stock price: -3.16) a. b. Show in a graph the sensitivity of the enterprise value to the growth in sales
