financial modeling

profilemharpermontes
FinalExamFallDataSet.xlsx
Home>Business & Finance homework help>financial modeling

INDEX

Date Coca Cola Hyatt Hotels Kroger Markets CVS Health
Stock Price Dividend Stock Price Dividend Stock Price Dividend Stock Price Dividend
12/1/18 $49.09 $71.31 $80.20
11/1/18 $50.40 $0.390 $69.20 $0.15 $29.66 $0.140 $72.39
10/1/18 $47.88 $79.59 $29.76 $78.72 $0.500
9/1/18 $46.19 $0.390 $77.36 $0.15 $29.11 $75.24
8/1/18 $44.57 $78.23 $31.50 $0.140 $64.86
7/1/18 $46.63 $77.15 $29.00 $64.35 $0.500
6/1/18 $43.86 $0.390 $81.73 $0.15 $28.45 $63.39
5/1/18 $43.00 $76.87 $24.33 $0.125 $69.83
4/1/18 $43.21 $76.26 $25.19 $62.21 $0.500
3/1/18 $43.43 $0.390 $77.27 $0.15 $23.94 $67.73
2/1/18 $43.22 $81.30 $27.12 $0.125 $78.69
1/1/18 $47.59 $73.54 $30.36 $72.50 $0.500
12/1/17 $45.88 $0.370 $72.36 $27.45 $76.60
11/1/17 $45.77 $62.66 $22.28 $0.125 $68.53
10/1/17 $45.98 $61.79 $20.70 $81.32 $0.500
9/1/17 $45.01 $0.370 $59.51 $20.06 $77.34
8/1/17 $45.55 $55.57 $21.87 $0.125 $79.93
7/1/17 $45.84 $56.21 $24.52 $80.46 $0.500
6/1/17 $44.85 $0.370 $57.70 $23.32 $76.83
5/1/17 $45.47 $55.50 $29.78 $0.120 $82.44
4/1/17 $43.15 $53.98 $29.65 $78.50 $0.500
3/1/17 $42.44 $0.370 $51.34 $29.49 $80.58
2/1/17 $41.96 $54.71 $31.80 $0.120 $78.81
1/1/17 $41.57 $55.26 $33.96 $78.91 $0.500
12/1/16 $42.00 $51.34 $34.51 $76.89
11/1/16 $40.35 $0.350 $50.79 $32.30 $0.120 $84.10
10/1/16 $42.40 $49.22 $30.98 $88.99 $0.425
9/1/16 $42.32 $0.350 $53.48 $29.68 $93.40
8/1/16 $43.43 $50.44 $31.99 $0.120 $92.72
7/1/16 $43.63 $49.14 $34.19 $95.74 $0.425
6/1/16 $45.33 $0.350 $45.91 $36.79 $96.45
5/1/16 $44.60 $47.88 $35.76 $0.105 $100.50
4/1/16 $44.80 $49.49 $35.39 $103.73 $0.425
3/1/16 $46.39 $0.350 $46.15 $38.25 $97.17
2/1/16 $43.13 $38.68 $39.91 $0.105 $96.59
1/1/16 $42.92 $47.02 $38.81 $97.77 $0.425
12/1/15 $42.96 $49.29 $41.83 $94.09
11/1/15 $42.62 $0.330 $50.40 $37.66 $0.105 $98.78
10/1/15 $42.35 $47.10 $37.80 $96.48 $0.350
9/1/15 $40.12 $0.330 $51.91 $36.07 $102.40
8/1/15 $39.32 $55.83 $34.50 $0.105 $112.47
7/1/15 $41.08 $56.69 $39.24 $104.88 $0.350
6/1/15 $39.23 $0.330 $57.46 $36.26 $102.38
5/1/15 $40.96 $58.05 $36.40 $0.090 $99.29
4/1/15 $40.56 $59.22 $34.46 $103.21 $0.350
3/1/15 $40.55 $0.330 $60.54 $38.33 $103.87
2/1/15 $43.30 $56.26 $35.58 $0.090 $98.16
1/1/15 $41.17 $60.21 $34.53 $96.31 $0.350
12/1/14 $42.22 $58.93 $32.11 $91.36
11/1/14 $44.83 $0.305 $59.22 $29.92 $0.090 $85.81
10/1/14 $41.88 $60.52 $27.86 $79.59 $0.275
9/1/14 $42.66 $0.305 $61.09 $26.00 $79.45
8/1/14 $41.72 $58.83 $25.49 $0.083 $76.36
7/1/14 $39.29 $60.98 $24.49 $75.37 $0.275
6/1/14 $42.36 $0.305 $61.16 $24.72 $78.32
5/1/14 $40.91 $56.28 $23.87 $0.083 $72.72
4/1/14 $40.79 $53.81 $23.02 $74.86 $0.275
3/1/14 $38.66 $0.305 $52.16 $21.83 $73.14
2/1/14 $38.20 $47.79 $20.97 $0.083 $67.72
1/1/14 $37.82 $49.46 $18.05 $71.57 $0.275
12/1/13 $41.31 $48.37 $19.76 $66.96
11/1/13 $40.19 $0.280 $47.60 $20.88 $0.083 $62.26
10/1/13 $39.57 $42.96 $21.42 $56.75 $0.225
9/1/13 $37.88 $0.280 $43.40 $20.17 $58.05
8/1/13 $38.18 $45.25 $18.30 $0.075 $61.49
7/1/13 $40.08 $40.36 $19.64 $57.18 $0.225
6/1/13 $40.11 $0.280 $41.12 $17.27 $57.58
5/1/13 $39.99 $42.68 $16.83 $0.075 $58.18
4/1/13 $42.33 $43.23 $17.19 $54.99 $0.225
3/1/13 $40.44 $0.280 $41.09 $16.57 $51.12
2/1/13 $38.72 $40.07 $14.61 $0.075 $51.20
1/1/13 $37.24 $38.57 $13.85 $48.35 $0.225
12/1/12 $36.25 $36.50 $13.01 $46.51
11/1/12 $37.92 $0.255 $36.50 $13.12 $0.075 $46.40

DECIDE

EQUATION

IRON

Date Price Dividend S&P500 Risk-Free
11/1/18 $ 33.97 2,740.37 0.149%
10/1/18 $ 30.61 2,924.59 0.148%
9/1/18 $ 34.52 $ 0.5875 2,901.13 0.145%
8/1/18 $ 36.10 2,813.36 0.142%
7/1/18 $ 35.11 2,718.37 0.140%
6/1/18 $ 35.01 $ 0.5875 2,734.62 0.147%
5/1/18 $ 33.29 2,654.80 0.150%
4/1/18 $ 33.94 $ 0.5875 2,648.05 0.147%
3/1/18 $ 32.28 2,640.87 0.139%
2/1/18 $ 30.90 2,713.83 0.135%
1/1/18 $ 34.41 $ 0.5875 2,823.81 0.119%
12/1/17 $ 36.49 2,673.61 0.113%
11/1/17 $ 39.53 2,584.84 0.103%
10/1/17 $ 38.69 $ 0.5500 2,575.26 0.094%
9/1/17 $ 37.11 2,519.36 0.086%
8/1/17 $ 37.60 2,471.65 0.082%
7/1/17 $ 34.75 $ 0.5500 2,470.30 0.088%
6/1/17 $ 32.25 2,423.41 0.083%
5/1/17 $ 32.77 2,411.80 0.079%
4/1/17 $ 32.62 $ 0.5500 2,384.20 0.065%
3/1/17 $ 32.94 2,362.72 0.062%
2/1/17 $ 33.57 2,363.64 0.044%
1/1/17 $ 33.06 $ 0.5500 2,278.87 0.042%
12/1/16 $ 29.51 2,238.83 0.040%
11/1/16 $ 29.98 2,198.81 0.039%
10/1/16 $ 30.65 $ 0.4850 2,126.15 0.025%
9/1/16 $ 33.68 2,168.27 0.022%
8/1/16 $ 34.47 2,170.95 0.027%
7/1/16 $ 36.99 $ 0.4850 2,173.60 0.020%
6/1/16 $ 35.27 2,098.86 0.021%
5/1/16 $ 32.54 2,096.95 0.024%
4/1/16 $ 32.35 $ 0.4850 2,065.30 0.016%
3/1/16 $ 29.55 2,059.74 0.016%
2/1/16 $ 25.60 1,932.23 0.026%
1/1/16 $ 24.00 1,940.24 0.025%
12/1/15 $ 23.53 $ 0.4850 2,043.94 0.012%
11/1/15 $ 23.79 2,080.41 0.014%
10/1/15 $ 26.24 $ 0.4750 2,079.36 0.006%
9/1/15 $ 26.12 1,920.03 0.000%
8/1/15 $ 23.87 1,972.18 0.004%
7/1/15 $ 25.31 $ 0.4750 2,103.84 0.005%
6/1/15 $ 25.72 2,063.11 0.001%
5/1/15 $ 30.26 2,107.39 0.000%
4/1/15 $ 28.61 $ 0.4750 2,085.51 0.000%
3/1/15 $ 29.88 2,067.89 0.002%
2/1/15 $ 30.10 2,104.50 0.001%
1/1/15 $ 32.63 $ 0.4750 1,994.99 0.000%
12/1/14 $ 31.28 $ 0.2550 2,058.90 0.003%
11/1/14 $ 30.55 2,067.56 0.000%
10/1/14 $ 28.99 $ 1.1140 2,018.05 0.000%
9/1/14 $ 25.33 1,972.29 0.001%
8/1/14 $ 25.79 2,003.37 0.002%
7/1/14 $ 24.02 $ 0.2700 1,930.67 0.002%
6/1/14 $ 25.17 1,960.23 0.002%
5/1/14 $ 22.11 1,923.57 0.003%
4/1/14 $ 20.19 $ 0.2700 1,883.95 0.002%
3/1/14 $ 19.37 1,872.34 0.002%
2/1/14 $ 19.11 1,859.45 0.004%
1/1/14 $ 18.55 $ 0.5400 1,782.59 0.001%
12/1/13 $ 20.91 1,848.36 0.005%
11/1/13 $ 19.37 1,805.81 0.005%
10/1/13 $ 18.28 $ 0.2700 1,756.54 0.003%
9/1/13 $ 18.42 1,681.55 0.000%
8/1/13 $ 17.59 1,632.97 0.002%
7/1/13 $ 18.96 $ 0.2700 1,685.73 0.003%
6/1/13 $ 17.95 1,606.28 0.003%
5/1/13 $ 24.18 1,630.74 0.003%
4/1/13 $ 25.54 $ 0.2700 1,597.57 0.004%
3/1/13 $ 24.30 1,569.19 0.005%
2/1/13 $ 23.09 1,514.68 0.008%

PORT

Ibar Inky Izap Ivision Istand Ipray Ialone Iweep
Ibar 0.1489 0.0042 -0.0291 0.0775 -0.0218 0.0273 -0.0635 0.0147
Inky 0.0042 0.2911 -0.0076 0.0711 0.0782 0.0211 0.0323 0.0090
Izap -0.0291 -0.0076 0.2381 0.0150 0.0483 -0.0002 0.0207 0.0819
Ivision 0.0775 0.0711 0.0150 0.6240 -0.0983 0.0469 0.0145 0.0253
Istand -0.0218 0.0782 0.0483 -0.0983 0.3754 -0.0158 0.0395 -0.0711
Ipray 0.0273 0.0211 -0.0002 0.0469 -0.0158 0.0634 -0.0105 0.0178
Ialone -0.0635 0.0323 0.0207 0.0145 0.0395 -0.0105 0.2803 -0.0408
Iweep 0.0147 0.0090 0.0819 0.0253 -0.0711 0.0178 -0.0408 0.2611
Mean 10.35% 19.95% 20.88% 25.88% 36.01% 8.14% 19.63% 18.25%

SPORT-T

Sales Growth 15.00% COGS/Sales
Cash/Sales SG&A/Sales
A/R Sales Interest/LTDebt 6.00%
Inventory/Sales Interest/ST Debt 4.00%
Net Fixed Assets/Sales Int Earned 1.50%
A/P.Sales Taxes/EBT
Other CL/Sales DPS/EPS
Shares Outstanding (in thousands) 425,500 Depr/NFA
Required Return 8.00%
Long-Term CF growth 3.00%
BALANCE SHEET 12/31/17
Assets
Current Assets
Cash And Cash Equivalents $ 681,000
Marketable Securities $ 305,100
Net Receivables $ 506,000
Inventory $ 3,338,000
Total Current Assets $ 4,830,100
Property Plant and Equipment $ 2,751,800
Total Assets $ 7,581,900
Liabilities
Current Liabilities
Accounts Payable $ 516,900
Notes Payable
Other Current Liabilities $ 469,000
Total Current Liabilities $ 985,900
Long Term Debt $ 5,100,000
Total Liabilities $ 6,085,900
Stockholders' Equity
Common Stock $ 49,000
Retained Earnings $ 1,304,000
Capital Surplus $ 143,000
Total Stockholder Equity $ 1,496,000
Total Liabilities + Net Worth $ 7,581,900
Balance Sheet Check $ - 0
INCOME STATEMENT 2017
Total Revenue $ 30,848,000
Cost of Revenue $ (23,122,000)
Gross Profit $ 7,728,017
Operating Expenses
Selling General and Administrative $ (5,626,000)
Depreciation $ (456,000)
Total Operating Expenses $ (6,082,000)
Income from Continuing Operations $ 1,646,017
Total Other Income/Expenses Net $ - 0
Earnings Before Interest And Taxes $ 1,646,017
Interest Expense $ (293,000)
Income Before Tax $ 1,353,017
Income Tax Expense $ (371,525)
Net Income From Continuing Ops $ 981,492
Net Income $ 981,492
Dividends Paid $ (151,000)
Change in Retained Earnings $ 830,492
STATEMENT OF CASH FLOWS 2017
Cash Flows from Operations
Net Income
Depreciation Expense
Decrease (Increase) in Receivables
Decrease (Increase) in Inventories
Decrease (Increase) in Other Current Assets
Increase (Decrease) in Accounts Payable
Increase (Decrease) in Other Current Liabilities
Increase (Decrease) in Other Non-Current Liabilities
Total Cash Flow From Operations
Cash Flows from Investing
(Additions to) Gross Property Plant and Equipment
Investment in Other Non-Current Assets
Total Cash Flows from Investing
Cash Flows from Financing
From Issuance (Repayment) of Short-Term Debt
From Issuance (Repayment) of Long-Term Debt
From Sale (Repurchase) of Equity
Dividends Paid to Shareholders
Cash Flows From Financing
Net Change in Cash and Marketable Securities
Beginning Cash and Marketable Securities
Ending Cash and Marketable Securities
Net Change in Cash and Marketable Securities

CAPITAL

MACRS Factors
Year 3 5
1 0.3333 0.2000
2 0.4444 0.3200
3 0.1482 0.1920
4 0.0741 0.1152
5 0.1152
6 0.0576

OPTION

POLLING

SUNNY

Asset Life
Year 3
1 0.3333
2 0.4444
3 0.1482
4 0.0741
5
6