finance
hughxinHomeworkPacket1_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.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 |