Show the work on Excel please. The following sheet presents the balance sheet and value drivers of Yummy Company, which manufactures a very special tomato sauce. (You might want to copy the table into Excel.)
|
Yummy Company, Financial Model
|
|
Value Drivers
|
|
|
Sales growth
|
12%
|
|
Current assets/Sales
|
22%
|
|
Current liabilities/Sales
|
20%
|
|
Net fixed assets growth rate
|
5%
|
|
Cost of goods sold/sales
|
45%
|
|
Depreciation rate (of average fixed assets at cost)
|
20%
|
|
interest rate on debt
|
8%
|
|
interest earned on cash balances
|
4%
|
|
tax rate
|
36%
|
|
dividend payout ratio
|
25%
|
|
sales
|
2000000
|
|
WACC
|
16%
|
|
long term FCF growth rate
|
4%
|
|
|
|
Balance sheet
|
|
|
Cash
|
460000
|
|
Current assets
|
440000
|
|
Fixed Assets
|
|
|
At cost
|
4000000
|
|
Depreciation
|
500000
|
|
Net fixed assets
|
3500000
|
|
total assets
|
4400000
|
|
|
|
Current liabilities
|
400000
|
|
Debt
|
3000000
|
|
Stock (1500000 shares, issued at $0.5 each)
|
750000
|
|
accumulated retained earnings
|
250000
|
|
Total Liabilities + Equity
|
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.
please show work on excel
Make a pro forma model for Yummy and computer the firm value using a DCF valuation model with year-end discounting.
Show in a graph the sensitivity of the enterprise value to the growth in sales;
Show in a graph the sensitivity of the enterprise value to the company's WACC.