Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please show all Excel formulas. 2. Company Valuation Fley Airline Supply is trading at $15/share but you think that price may not be right. You
Please show all Excel formulas. 2. Company Valuation Fley Airline Supply is trading at $15/share but you think that price may not be right. You have the following data and you want to use it to calculate its share price: Gross Sales $124M COGS 54% of sales General & Admin $3.5M Annual Sales Growth Rate 3.0% Advertising, Promotion & Selling $5M Yearly Inflation for non-COGS expenses 3.3% Tax Rate 21% Discount Rate 8% Cash Balance $3M Debt $5M Share o/s $44M Cash Flow Year Year Year Year Year Year Adjustment 1 2 3 4 5 6 Working Capital 2.0 (2.0) (1.2) (0.3) (1.0) (2.0) Capital (2.0) (2.0) (3.0) (4.0) (4.0) (4.0) Expenditures Calculate the per share price and run sensitivities for growth rates of 3.0%, 3.5%, and 4% as well as discount rates of 8%, 9%, and 10%. Put these in a matrix. Prob. 1 Prob. 2 Prob. 3 Prob. 4 Fley Airline Supply Valuation Yrs. 1-6 Cash Flows Sales growth Costs (% of sales): Cost of Goods Sold Advert, Prom., & Selling General & Administrative Rates: Tax Discount Inflation Results PV of NCF (incl. TV) + Cash - Debt Total Equity (MS) - # of shares outstanding (M) - Price/share (5) Sales Cost of Goods Sold Advert., Prom., & Selling General & Administrative Net Income before Tax Taxes Net Income after Tax Cash flow adjustments: Working Capital Capital Expenditures Net Cash Flows NCF (incl. terminal value) TV Growth rate 3.5% 4.0% 3.0% Price/share (S) for: 8.0% 9.0% 10.0% Disc. Rate
Please show all Excel formulas.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started