financial modelling assignment

profilekkrtiksjthia77
SP19TakeHomeFinalExamDataSet1.xlsx
Home>Business & Finance homework help>financial modelling assignment

INDEX

Date Pepsico Hyatt Hotels Kroger Markets CVS Health
Stock Price Dividend Stock Price Dividend Stock Price Dividend Stock Price Dividend Price Average Date
12/1/18 $117.09 $71.31 $29.66 $80.20
11/1/18 $120.01 $69.20 $0.15 $29.66 $0.140 $72.39
10/1/18 $110.60 $79.59 $29.76 $78.72 $0.500
9/1/18 $109.12 $0.928 $77.36 $0.15 $29.11 $75.24
8/1/18 $109.33 $78.23 $31.50 $0.140 $64.86
7/1/18 $112.25 $77.15 $29.00 $64.35 $0.500
6/1/18 $106.26 $0.928 $81.73 $0.15 $28.45 $63.39
5/1/18 $96.96 $76.87 $24.33 $0.125 $69.83
4/1/18 $97.63 $76.26 $25.19 $62.21 $0.500
3/1/18 $104.79 $0.805 $77.27 $0.15 $23.94 $67.73
2/1/18 $105.35 $81.30 $27.12 $0.125 $78.69
1/1/18 $115.50 $73.54 $30.36 $72.50 $0.500
12/1/17 $115.13 $0.805 $72.36 $27.45 $76.60
11/1/17 $111.10 $62.66 $22.28 $0.125 $68.53
10/1/17 $105.10 $61.79 $20.70 $81.32 $0.500
9/1/17 $106.24 $0.805 $59.51 $20.06 $77.34
8/1/17 $109.58 $55.57 $21.87 $0.125 $79.93
7/1/17 $110.41 $56.21 $24.52 $80.46 $0.500
6/1/17 $109.35 $0.805 $57.70 $23.32 $76.83
5/1/17 $109.90 $55.50 $29.78 $0.120 $82.44
4/1/17 $106.53 $53.98 $29.65 $78.50 $0.500
3/1/17 $104.47 $0.753 $51.34 $29.49 $80.58
2/1/17 $103.09 $54.71 $31.80 $0.120 $78.81
1/1/17 $96.93 $55.26 $33.96 $78.91 $0.500
12/1/16 $97.72 $0.753 $51.34 $34.51 $76.89
11/1/16 $92.80 $50.79 $32.30 $0.120 $84.10
10/1/16 $99.39 $49.22 $30.98 $88.99 $0.425
9/1/16 $100.84 $0.753 $53.48 $29.68 $93.40
8/1/16 $98.27 $50.44 $31.99 $0.120 $92.72
7/1/16 $100.27 $49.14 $34.19 $95.74 $0.425
6/1/16 $96.80 $0.753 $45.91 $36.79 $96.45
5/1/16 $92.44 $47.88 $35.76 $0.105 $100.50
4/1/16 $94.08 $49.49 $35.39 $103.73 $0.425
3/1/16 $92.98 $0.703 $46.15 $38.25 $97.17
2/1/16 $88.75 $38.68 $39.91 $0.105 $96.59
1/1/16 $90.09 $47.02 $38.81 $97.77 $0.425
12/1/15 $90.03 $0.703 $49.29 $41.83 $94.09
11/1/15 $90.24 $50.40 $37.66 $0.105 $98.78
10/1/15 $92.07 $47.10 $37.80 $96.48 $0.350
9/1/15 $84.31 $0.703 $51.91 $36.07 $102.40
8/1/15 $83.68 $55.83 $34.50 $0.105 $112.47
7/1/15 $86.14 $56.69 $39.24 $104.88 $0.350
6/1/15 $82.84 $0.703 $57.46 $36.26 $102.38
5/1/15 $85.58 $58.05 $36.40 $0.090 $99.29
4/1/15 $84.42 $59.22 $34.46 $103.21 $0.350
3/1/15 $84.30 $0.655 $60.54 $38.33 $103.87
2/1/15 $87.26 $56.26 $35.58 $0.090 $98.16
1/1/15 $82.68 $60.21 $34.53 $96.31 $0.350
12/1/14 $82.82 $0.655 $58.93 $32.11 $91.36
11/1/14 $87.67 $59.22 $29.92 $0.090 $85.81
10/1/14 $84.23 $60.52 $27.86 $79.59 $0.275
9/1/14 $80.95 $61.09 $26.00 $79.45
8/1/14 $80.43 $58.83 $25.49 $0.083 $76.36
7/1/14 $76.62 $60.98 $24.49 $75.37 $0.275
6/1/14 $77.12 $0.655 $61.16 $24.72 $78.32
5/1/14 $76.25 $56.28 $23.87 $0.083 $72.72
4/1/14 $74.14 $53.81 $23.02 $74.86 $0.275
3/1/14 $71.57 $0.568 $52.16 $21.83 $73.14
2/1/14 $68.63 $47.79 $20.97 $0.083 $67.72
1/1/14 $68.88 $49.46 $18.05 $71.57 $0.275
12/1/13 $70.61 $0.568 $48.37 $19.76 $66.96
11/1/13 $71.90 $47.60 $20.88 $0.083 $62.26
10/1/13 $71.59 $42.96 $21.42 $56.75 $0.225
9/1/13 $67.20 $0.568 $43.40 $20.17 $58.05
8/1/13 $67.40 $45.25 $18.30 $0.075 $61.49
7/1/13 $70.62 $40.36 $19.64 $57.18 $0.225
6/1/13 $68.66 $0.568 $41.12 $17.27 $57.58
5/1/13 $67.80 $42.68 $16.83 $0.075 $58.18
4/1/13 $69.23 $43.23 $17.19 $54.99 $0.225
3/1/13 $66.41 $0.538 $41.09 $16.57 $51.12
2/1/13 $63.15 $40.07 $14.61 $0.075 $51.20
1/1/13 $60.72 $38.57 $13.85 $48.35 $0.225
12/1/12 $56.59 $0.538 $36.50 $13.01 $46.51
11/1/12 $58.07 $36.50 $13.12 $0.075 $46.40

DECIDE

Discount Rate 10%
YEAR CASH FLOWS FRACTIONAL PAYBACK PERIOD DISOUNTED CASHFLOWS FRACTIONAL PAYBACK PERIOD
0 -12,000,000 -12,000,000
1 1000000 - -11,000,000
2 3000000 - -8,000,000
3 5000000 - -3,000,000
4 1500000 - -1,500,000
5 3500000 0.4285714286 2,000,000
6 6500000 0.3076923077 8,500,000
7 9000000 0.9444444444 17,500,000
8 4500000 3.8888888889 22,000,000
9 3900000 5.641025641 25,900,000
10 6400000 4.046875 32,300,000

EQUATION

NEWELL

Date Price Dividend S&P500 Risk-Free
11/1/18 $ 22.86 2,740.37 0.149%
10/1/18 $ 15.51 2,924.59 0.148%
9/1/18 $ 19.83 $ 0.2300 2,901.13 0.145%
8/1/18 $ 21.00 2,813.36 0.142%
7/1/18 $ 25.32 2,718.37 0.140%
6/1/18 $ 24.94 $ 0.2300 2,734.62 0.147%
5/1/18 $ 22.59 2,654.80 0.150%
4/1/18 $ 26.47 2,648.05 0.147%
3/1/18 $ 24.41 $ 0.2300 2,640.87 0.139%
2/1/18 $ 24.41 2,713.83 0.135%
1/1/18 $ 25.12 2,823.81 0.119%
12/1/17 $ 29.36 $ 0.2300 2,673.61 0.113%
11/1/17 $ 29.20 2,584.84 0.103%
10/1/17 $ 38.45 2,575.26 0.094%
9/1/17 $ 40.24 $ 0.2300 2,519.36 0.086%
8/1/17 $ 45.31 2,471.65 0.082%
7/1/17 $ 49.48 2,470.30 0.088%
6/1/17 $ 50.32 $ 0.2300 2,423.41 0.083%
5/1/17 $ 49.48 2,411.80 0.079%
4/1/17 $ 44.61 2,384.20 0.065%
3/1/17 $ 44.08 $ 0.1900 2,362.72 0.062%
2/1/17 $ 45.63 2,363.64 0.044%
1/1/17 $ 44.05 2,278.87 0.042%
12/1/16 $ 41.55 $ 0.1900 2,238.83 0.040%
11/1/16 $ 43.58 2,198.81 0.039%
10/1/16 $ 44.51 2,126.15 0.025%
9/1/16 $ 48.82 $ 0.1900 2,168.27 0.022%
8/1/16 $ 49.03 2,170.95 0.027%
7/1/16 $ 48.46 2,173.60 0.020%
6/1/16 $ 44.87 $ 0.1900 2,098.86 0.021%
5/1/16 $ 43.88 2,096.95 0.024%
4/1/16 $ 41.90 2,065.30 0.016%
3/1/16 $ 40.75 $ 0.1900 2,059.74 0.016%
2/1/16 $ 34.80 1,932.23 0.026%
1/1/16 $ 35.50 1,940.24 0.025%
12/1/15 $ 40.35 $ 0.1900 2,043.94 0.012%
11/1/15 $ 40.71 2,080.41 0.014%
10/1/15 $ 38.68 2,079.36 0.006%
9/1/15 $ 36.20 $ 0.1900 1,920.03 0.000%
8/1/15 $ 38.23 1,972.18 0.004%
7/1/15 $ 39.27 2,103.84 0.005%
6/1/15 $ 37.30 $ 0.1900 2,063.11 0.001%
5/1/15 $ 35.69 2,107.39 0.000%
4/1/15 $ 34.43 2,085.51 0.000%
3/1/15 $ 35.28 $ 0.1900 2,067.89 0.002%
2/1/15 $ 35.31 2,104.50 0.001%
1/1/15 $ 33.13 1,994.99 0.000%
12/1/14 $ 34.23 $ 0.1700 2,058.90 0.003%
11/1/14 $ 32.48 2,067.56 0.000%
10/1/14 $ 29.81 2,018.05 0.000%
9/1/14 $ 30.78 $ 0.1700 1,972.29 0.001%
8/1/14 $ 29.83 2,003.37 0.002%
7/1/14 $ 28.90 1,930.67 0.002%
6/1/14 $ 27.58 $ 0.1700 1,960.23 0.002%
5/1/14 $ 25.90 1,923.57 0.003%
4/1/14 $ 26.64 1,883.95 0.002%
3/1/14 $ 26.45 $ 0.1500 1,872.34 0.002%
2/1/14 $ 28.27 1,859.45 0.004%
1/1/14 $ 27.21 1,782.59 0.001%
12/1/13 $ 28.54 $ 0.1500 1,848.36 0.005%
11/1/13 $ 26.59 1,805.81 0.005%
10/1/13 $ 25.96 1,756.54 0.003%
9/1/13 $ 24.09 $ 0.1500 1,681.55 0.000%
8/1/13 $ 22.03 1,632.97 0.002%
7/1/13 $ 23.53 1,685.73 0.003%
6/1/13 $ 22.86 $ 0.1500 1,606.28 0.003%
5/1/13 $ 23.42 1,630.74 0.003%
4/1/13 $ 22.82 1,597.57 0.004%
3/1/13 $ 22.61 $ 0.1500 1,569.19 0.005%
2/1/13 $ 20.08 1,514.68 0.008%

PORT

Ibar Inky Izap Ivision Istand Ipray Ialone Iweep WEIGHTS
Ibar 0.1489 0.0042 -0.0291 0.0775 -0.0218 0.0273 -0.0635 0.0147 1.0000 10.35% {=mmult(minverse(b3:k12),n3;n12)}
Inky 0.0042 0.2911 -0.0076 0.0711 0.0782 0.0211 0.0323 0.0090 1.0000 19.95%
Izap -0.0291 -0.0076 0.2381 0.0150 0.0483 -0.0002 0.0207 0.0819 1.0000 20.88%
Ivision 0.0775 0.0711 0.0150 0.6240 -0.0983 0.0469 0.0145 0.0253 1.0000 25.88%
Istand -0.0218 0.0782 0.0483 -0.0983 0.3754 -0.0158 0.0395 -0.0711 1.0000 36.01%
Ipray 0.0273 0.0211 -0.0002 0.0469 -0.0158 0.0634 -0.0105 0.0178 1.0000 8.14%
Ialone -0.0635 0.0323 0.0207 0.0145 0.0395 -0.0105 0.2803 -0.0408 1.0000 19.63%
Iweep 0.0147 0.0090 0.0819 0.0253 -0.0711 0.0178 -0.0408 0.2611 1.0000 18.25%
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 -74.95%
Cash/Sales 2.21% SG&A/Sales -18.24%
A/R Sales 1.64% Interest/LTDebt 6.00%
Inventory/Sales 10.82% Interest/ST Debt 4.00%
Net Fixed Assets/Sales 8.92% Int Earned 1.50%
A/P.Sales 1.68% Taxes/EBT -0.78%
Other CL/Sales 1.52% DPS/EPS -0.32%
Shares Outstanding (in thousands) 475,000 Depr/NFA -16.57%
Required Return (WACC) 9.00%
Long-Term CF growth rate 3.25%
Risk free rate 2.50%
BALANCE SHEET 12/31/17 12/31/18 12/31/19 12/31/20 12/31/21 12/31/22
Year 1 2 3 4 5
Assets
Current Assets
Cash And Cash Equivalents $ 681,000 783150 900622.5 1035715.875 1191073.25625 1369734.2446875
Marketable Securities $ 305,100 305,100 305,100 305,100 305,100 305,100
Net Receivables $ 506,000 581900 669185 769562.75 884997.1625 1017746.736875
Inventory $ 3,338,000 3838700 4414505 5076680.75 5838182.8625 6713910.291875
Total Current Assets $ 4,830,100 $ 4,830,100 $ 5,508,850 $ 6,289,413 $ 7,187,059 $ 8,219,353
Property Plant and Equipment $ 2,751,800 3164570 3639255.5 4185143.825 4812915.39875 5534852.7085625
Total Assets $ 7,581,900 $ 7,994,670 $ 9,148,106 $ 10,474,556 $ 11,999,975 $ 13,754,206
Liabilities
Current Liabilities
Accounts Payable $ 516,900 594435 683600.25 786140.2875 904061.330625 1039670.53021875
Notes Payable - - - - - -
Other Current Liabilities $ 469,000 539350 620252.5 713290.375 820283.93125 943326.5209375
Total Current Liabilities $ 985,900 $ 1,133,785 $ 1,303,853 $ 1,499,431 $ 1,724,345 $ 1,982,997
Long Term Debt $ 5,100,000 5,100,000 $ 5,100,000.0000 5,100,000 510000 510000
Total Liabilities $ 6,085,900 $ 6,233,785 $ 6,403,853 $ 6,599,431 $ 2,234,345 $ 2,492,997
Stockholders' Equity
Common Stock $ 49,000 49,000 49,000 49,000 49,000 49,000
Retained Earnings $ 1,304,000 $ 56,437,328 $ 119,793,146 $ 192,604,827 $ 276,016,622 $ 371,933,797
Capital Surplus $ 143,000 143,000 143,000 143,000 143,000 143,000
Total Stockholder Equity $ 1,496,000 $ 56,629,328 $ 119,985,146 $ 192,796,827 $ 276,208,622 $ 372,125,797
Total Liabilities + Net Worth $ 7,581,900 $ 62,863,113 $ 126,388,999 $ 199,396,257 $ 278,442,968 $ 374,618,794
Balance Sheet Check $ - 0 $ (54,868,443) $ (117,240,893) $ (188,921,701) $ (266,442,993) $ (360,864,588)
INCOME STATEMENT 2017 2018 2019 2020 2021 2022
Total Revenue $ 30,848,000 35,475,200.00 40,796,480.00 46,915,952.00 53,953,344.80 62,046,346.52
Cost of Revenue $ (23,122,000) (26,590,300.00) (30,578,845.00) (35,165,671.75) (40,440,522.51) (46,506,600.89)
Gross Profit $ 53,970,000 $ 62,065,500 $ 71,375,325 $ 82,081,624 $ 94,393,867 $ 108,552,947
Operating Expenses
Selling General and Administrative $ (5,626,000) (6,469,900.00) (7,440,385.00) (8,556,442.75) (9,839,909.16) (11,315,895.54)
Depreciation $ (456,000) -524400 -603060 -693519 -797546.85 -917178.8775
Total Operating Expenses $ (6,082,000) $ (6,994,300) $ (8,043,445) $ (9,249,962) $ (10,637,456) $ (12,233,074)
Income from Continuing Operations $ 47,888,000 $ 55,071,200 $ 63,331,880 $ 72,831,662 $ 83,756,411 $ 96,319,873
Total Other Income/Expenses Net $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Earnings Before Interest And Taxes $ 47,888,000 $ 55,071,200 $ 63,331,880 $ 72,831,662 $ 83,756,411 $ 96,319,873
Interest Expense $ (293,000) 318204 318204 318204 42804 42804
Income Before Tax $ 47,595,000 $ 55,389,404 $ 63,650,084 $ 73,149,866 $ 83,799,215 $ 96,362,677
Income Tax Expense $ (371,525) (432,367.86) (496,850.46) (571,005.44) (654,133.91) (752,203.88)
Net Income From Continuing Ops $ 47,223,475 $ 54,957,036 $ 63,153,234 $ 72,578,861 $ 83,145,081 $ 95,610,473
Net Income $ 47,223,475 $ 54,957,036 $ 63,153,234 $ 72,578,861 $ 83,145,081 $ 95,610,473
Dividends Paid $ (151,000) (176,292.25) (202,584.17) (232,819.88) (266,714.41) (306,701.13)
Change in Retained Earnings $ 47,072,475 $ 54,780,744 $ 62,950,649 $ 72,346,041 $ 82,878,367 $ 95,303,772
STATEMENT OF CASH FLOWS 2017 2018 2019 2020 2021
Cash Flows from Operations
Net Income $ 54,957,036 $ 63,153,234 $ 72,578,861 $ 83,145,081
Depreciation Expense -524400 -603060 -693519 -797546.85
Decrease (Increase) in Receivables $ (75,900)
Decrease (Increase) in Inventories $ (500,700)
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

COSTS
MALE FEMALE
DEMOCRATIC 9.5 9.75
REPUBLICAN 10.25 11.75
INDEPENDENT 12.5 13.5
QUANTITY
MALE FEMALE PROPORTIONS
DEMOCRATIC 500 1000 1500 10.00% 20.00% 30.00%
REPUBLICAN 1000 500 1500 20.00% 10.00% 30.00%
INTEPENDENT 1500 500 2000 30.00% 10.00% 40.00%
3000 2000 30.00% 40.00%
TOTOAL 5000
EXPENSES
MALE FEMALE
DEMOCRATIC 4750 9750
REPUBLICAN 10250 5875
INDEPENDENT 18750 6750
TOTAL £56,125.00

SUNNY

Variable Distribution Mean Standard Deviation Asset Life
Initial Outlay Normal $ 1,000,000 $ 10,000 Year 3
Initial Sales Normal $ 1,600,000 $ 200,000 1 0.3333
Growth in Sales Normal 4% 2% 2 0.4444
COGS/Sales Normal 70% 4% 3 0.1482
Initial Fixed Cost Normal $ 100,000 $ 10,000 4 0.0741
Growth in Fixed Cost Normal 3% 2% 5
Working Capital/Sales Normal 5% 1% 6
Salvage Normal $ 100,000 $ 5,000
Discount Rate Normal 12% 2%
Minimum Maximum
Tax Rate Uniform 22% 30%
GENERATED VALUES
COST OF EQUIPMENT
INITIAL SALES
GROWTH IN SALES
COGS/SALES
INITIAL FIXED COST
GROWTHIN FIXED COSTS
WORKING CAPITAL
SALVAGE
DISCOUNT RATE
TAX RATE
INITIAL OUTLAY