finance

profilehughxin
HomeworkPacket1_Pepperdine_FINC614IntroFinance_StudentExcelResource.xlsx

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