finance

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.62556532839983

Yield 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