Ins.
| Input boxes in tan |
| Output boxes in yellow |
| Given data in blue |
| Calculations in red |
| Answers in green |
| NOTE: Some functions used in these spreadsheets may require that |
| the “Analysis ToolPak” or “Solver Add-in” be installed in Excel. |
| To install these, click on “Tools|Add-Ins” and select “Analysis ToolPak” |
| and “Solver Add-In.” |
2-#18
| Chapter 2 |
| Question 18 |
| Input area: |
| Sales |
| Costs of goods sold |
| Administrative and selling expenses |
| Depreciation expense |
| Interest expense |
| Tax rate |
| Output area: |
| Income Statement |
| Sales |
$ – 0 |
| Costs |
– |
| Administrative and selling expenses |
– |
| Depreciation expense |
– 0 |
| EBIT |
$ – |
| Interest expense |
– |
| EBT |
$ – 0 |
| Taxes |
0 |
| a) |
Net income |
$ – 0 |
| b) |
Operating cash flow |
$ – 0 |
| c) |
Net income was negative because of the tax deductibility and |
| interest expense. However, the actual cash flow from operations |
| was positive because depreciation is a non-cash expense and |
| interest is a financing, not an operating, expense. |
3-#2
| Chapter 3 |
| Question 2 |
| Input area: |
| Sales |
| Total assets |
| Total debt |
| Profit margin |
| Output area: |
| Net income |
$ – |
| Return on assets |
ERROR:#DIV/0! |
| Total equity |
$ – 0 |
| Return on equity |
ERROR:#DIV/0! |
3-#8
| Chapter 3 |
| Question 8 |
| Input area: |
| Profit margin |
| Total asset turnover |
| Return on equity |
| Output area: |
| Equity multiplier |
ERROR:#DIV/0! |
| Debt/equity ratio |
ERROR:#DIV/0! |
4-#13
| Chapter 4 |
| Question 13 |
| Input area: |
Output area: |
| Present value |
Years |
Interest rate |
Future value |
| ERROR:#DIV/0! |
| Present value |
Years |
Interest rate |
Future value |
| $ – 0 |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
4-#14
| Chapter 4 |
| Question 14 |
| Input area: |
Output area: |
| Present value |
Years |
Interest rate |
Future value |
| ERROR:#DIV/0! |
4-#17
| Chapter 4 |
| Question 17 |
| Output area: |
Input area: |
| Present value |
Years |
Interest rate |
Future value |
| $ – 0 |
5-#2
| Chapter 5 |
| Question 2 |
| Input area: |
| Payment for X |
| # of years for X |
| Payment for Y |
| # of years for Y |
| Discount rate |
| Discount rate |
| Output area: |
| PV at 0% |
| Value of X |
$0.00 |
| Value of Y |
$ – 0 |
| PV at 0% |
| Value of X |
$ – 0 |
| Value of Y |
$ – 0 |
5-#28
| Chapter 5 |
| Question 28 |
| Input area: |
| Output area: |
| Discount rate |
| Year |
Cash flow |
| 1 |
| 2 |
| 3 |
| 4 |
| Present value |
$ – 0 |
5-#30
| Chapter 5 |
| Question 30 |
| Input area: |
| Loan amount |
| Loan length (months) |
| APR on loan |
| Output area: |
| Annuity payment |
ERROR:#NUM! |
6-#3
| Chapter 6 |
| Question 3 |
| Input area: |
| Coupon rate |
| Settlement date |
1/1/00 |
| Maturity date |
| Yield to maturity |
| Coupons per year |
1 |
| Face value (% of par) |
100 |
| Par value ($) |
| Output area: |
| Price |
ERROR:#NUM! |
6-#8
| Chapter 6 |
| Question 8 |
| Input area: |
| Years to maturity |
| Yield to maturity |
| Bond price |
| Coupons per year |
| Output area: |
| Present value of final payment |
ERROR:#DIV/0! |
| Present value of coupon payments |
ERROR:#DIV/0! |
| Coupon payment |
ERROR:#DIV/0! |
| Coupon rate |
ERROR:#DIV/0! |
6-#19
| Chapter 6 |
| Question 19 |
| Input area: |
| Bond Bill: |
| Coupon rate |
| Settlement date |
1/1/00 |
| Maturity date |
1/1/05 |
| Redemption (% of par) |
100 |
| # of coupons per year |
2 |
| Bond Ted: |
| Coupon rate |
0.0% |
| Settlement date |
1/1/00 |
| Maturity date |
| Redemption (% of par) |
100 |
| # of coupons per year |
2 |
| Change in interest rate |
2% |
| Output area: |
| Price of Bond Bill |
$ 905.29 |
| Price of Bond Ted |
ERROR:#NUM! |
| % change in Bond Bill |
-9.47% |
| % change in Bond Ted |
ERROR:#NUM! |
| All else same, the longer the maturity |
| of a bond, the ________ is its price sensitivity |
input if greater or lesser for blank space |
| to changes in interest rates. |
| Bond Bill |
| YTM |
Price |
| 0% |
$ 1,000.00 |
| 1% |
$ 951.35 |
| 2% |
$ 905.29 |
| 3% |
$ 861.67 |
| 4% |
$ 820.35 |
| 5% |
$ 781.20 |
| 6% |
$ 744.09 |
| 7% |
$ 708.92 |
| 8% |
$ 675.56 |
| 9% |
$ 643.93 |
| 10% |
$ 613.91 |
| Bond Ted |
| YTM |
| 0% |
ERROR:#NUM! |
| 1% |
ERROR:#NUM! |
| 2% |
ERROR:#NUM! |
| 3% |
ERROR:#NUM! |
| 4% |
ERROR:#NUM! |
| 5% |
ERROR:#NUM! |
| 6% |
ERROR:#NUM! |
| 7% |
ERROR:#NUM! |
| 8% |
ERROR:#NUM! |
| 9% |
ERROR:#NUM! |
| 10% |
ERROR:#NUM! |
YTM and Bond Price
Bond Bill 0 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 1290 1233.5298846588714 1179.9547860833316 1129.1105837259636 1080.8432650561801 1035.0082557238843 991.46979716322426 950.10036806453297 910.78014642709434 873.39650916623805 837.84356648711889 Bond Ted 2450.0000000000014 2059.4268723921059 1744.7262330909948 1489.9956330299151 1282.8124530428677 1113.4492467221712 974.2702359929923 859.2662927762741 763.69596921664038 683.80787554334461 616.62556532839983Yield to Maturity
Bond Price
7-#1
| Chapter 7 |
| Question 1 |
| Input area: |
| Dividend paid |
| Dividend growth rate |
| Required return |
| Year for price |
0 |
| Year for price |
3 |
| Year for price |
15 |
| Output area: |
| Price at Year 0 |
ERROR:#DIV/0! |
| Price at Year 3 |
ERROR:#DIV/0! |
| Price at Year 15 |
ERROR:#DIV/0! |
7-#22
| Chapter 7 |
| Question 22 |
| Input area: |
| Dividend |
$ 6.30 |
| Initial dividend growth rate |
| Years at growth rate |
| Final dividend growth rate |
| Required return |
| Today’s closing price |
$ 145.39 |
| Output area: |
| Stock price at year |
– |
ERROR:#DIV/0! |
| Year |
Dividend |
Present value |
| 1 |
$ 6.30 |
$ 6.30 |
| 2 |
$ – 0 |
$ – 0 |
| 3 |
$ – 0 |
$ – 0 |
| 4 |
$ – 0 |
$ – 0 |
| 5 |
$ – 0 |
$ – 0 |
| 6 |
$ – 0 |
$ – 0 |
| 7 |
$ – 0 |
$ – 0 |
| 8 |
$ – 0 |
$ – 0 |
| 9 |
$ – 0 |
$ – 0 |
| 10 |
$ – 0 |
$ – 0 |
| Stock price today |
$ 6.30 |
7-#27
| Chapter 7 |
| Question 27 |
| Input Area: |
| Year 1 |
Year 2 |
Year 3 |
Year 4 |
| Stock price |
| EPS |
| Earnings growth rate |
| Output Area: |
| Year 1 |
Year 2 |
Year 3 |
Year 4 |
| PE ratio |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
ERROR:#DIV/0! |
| Average PE |
ERROR:#DIV/0! |
| Next year’s EPS |
$ – 0 |
| Target stock price next year |
ERROR:#DIV/0! |
2. SBUX
| STARBUCKS & PEER RATIOS |
| Starbucks |
Company 1: |
Company 2: |
Company 3: |
| Liquidity |
| Quick Ratio |
| Current Ratio |
| Debt to Equity |
| Asset Management |
| Total Asset Turnover |
| Receivables Turnover |
| Inventory Turnover |
| Profitability |
| ROA % |
| ROE % |
| ROI |
| EBITDA Margin % |
| Tax Rate % |
| Per Share |
| Cash Flow per Share |
3. SBUX
| STARBUCKS VALUATION USING MULTIPLES |
| Starbucks |
Company 1: |
Company 2: |
Company 3: |
| Benchmark PE ratio X EPS |
| PE Ratio |
| EPS Ratio |
| Price per share |
0 |
0 |
0 |
0 |
| Benchmark price – sales ratio x Sales per share (Optional) |
| Price per share |
| Sales ratio |
| Sales per share |
| Price per share |
0 |
0 |
0 |
0 |
| Current Valuation |
| Price per share |
| Shares outstanding |
| Value |
0 |
0 |
0 |
0 |
| Future Valuation |
| PE Ratio (Appropriate) |
| EPS (Predicted) |
| Value |
0 |
0 |
0 |
0 |
