graded project

profileNtassa21
500289_Project_Files.xls
Home>Business & Finance homework help>Management homework help>graded project

Part 1 Financial Information

2018 2019
Cost of Goods Sold 235,942 297,915
Cash 36,542 51,940
Depreciation 61,056 69,011
Interest Expense 13,877 15,905
Selling and Admin Exp 40,952 58,569
Accounts Payable 32,194 33,999
Net Fixed Assets 269,369 328,185
Sales 482,155 587,715
Accounts Receivable 24,120 24,089
Notes Payable 24,866 26,972
Long-Term Debt 142,148 161,000
Inventory 32,766 58,798
New Equity 0 16,000

Part 2 Loan Amortization Sched

Part 2 Chart

Part 3 Data Report

Equipment 10,500,000
Pretax salvage value 1,100,000
R&D 200,000
Marketing study 150,000
Year 1 Year 2 Year 3 Year 4 Year 5
Sales (units) 75,000 85,000 80,000 70,000 60,000
Sales of old product 45,000 25,000
Lost sales 10,000 10,000
Depreciation rate 14.29% 24.49% 17.49% 12.49% 8.93%
Price 240
VC 140
FC 2,100,000
Price of old product 150
Product price after reduction 120
VC of old product 95
Tax rate 30%
NWC percentage 22%
Required return 10%
Sales Year 1 Year 2 Year 3 Year 4 Year 5
New 18,000,000 20,400,000 19,200,000 16,800,000 14,400,000
Lost sales 1,500,000 1,500,000
Lost revenue 1,050,000 450,000
Net sales 15,450,000 18,450,000 19,200,000 16,800,000 14,400,000
VC
New 10,500,000 11,900,000 11,200,000 9,800,000 8,400,000
Lost sales 950,000 950,000
Total VC 9,550,000 10,950,000 11,200,000 9,800,000 8,400,000
Sales 15,450,000 18,450,000 19,200,000 16,800,000 14,400,000
VC 9,550,000 10,950,000 11,200,000 9,800,000 8,400,000
Fixed costs 2,100,000 2,100,000 2,100,000 2,100,000 2,100,000
Dep 1,500,450 2,571,450 1,836,450 1,311,450 937,650
EBT 2,299,550 2,828,550 4,063,550 3,588,550 2,962,350
Tax 689,865 848,565 1,219,065 1,076,565 888,705
NI 1,609,685 1,979,985 2,844,485 2,511,985 2,073,645
+Dep 1,500,450 2,571,450 1,836,450 1,311,450 937,650
OCF 3,110,135 4,551,435 4,680,935 3,823,435 3,011,295
NWC
Beg 0 3,399,000 4,059,000 4,224,000 3,696,000
End 3,399,000 4,059,000 4,224,000 3,696,000 - 0
NWC CF (3,399,000) (660,000) (165,000) 528,000 3,696,000
Net CF (288,865) 3,891,435 4,515,935 4,351,435 6,707,295
BV of equipment 2,342,550
Salvage 1,100,000
Taxes on sale of equipment 372,765
Salvage CF 1,100,000
Cash flow on sale of equipment 1,472,765
Year 0 1 2 3 4 5
CF (10,500,000) (288,865) 3,891,435 4,515,935 4,351,435 8,180,060

Regression Analysis

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.9998079443
R Square 0.9996159255
Adjusted R Square 0.9996091873
Standard Error 0.0005856135
Observations 59
ANOVA
df SS MS F Significance F
Regression 1 0.0508761946 0.0508761946 148351.691553502 4.76453834183864E-99
Residual 57 0.0000195478 0.0000003429
Total 58 0.0508957424
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 0.0017718704 0.0000799626 22.1587355338 1.07663390122623E-29 0.001611748 0.0019319928 0.001611748 0.0019319928
-0.007555786 1.0019621389 0.0026013876 385.1644993422 4.76453834183877E-99 0.9967529502 1.0071713277 0.9967529502 1.0071713277
RESIDUAL OUTPUT
Observation Predicted -0.00634033265878447 Residuals
1 -0.0610602325 0.0009066832
2 0.0413627165 -0.0002479857
3 0.0143357149 -0.0005251669
4 0.0214988714 0.0008820005
5 0.0259887813 -0.0001120393
6 -0.0181482156 -0.0005110237
7 0.0045239769 0.0011674828
8 0.008820571 0.0002509901
9 0.0522571654 -0.0004294793
10 0.0127874246 0.0006938766
11 0.0377634094 -0.0003639393
12 0.0198513766 -0.0006851173
13 0.0225587223 0.0007772413
14 -0.013281911 -0.0001372665
15 0.0513143023 -0.0005422863
16 -0.0296209584 0.0006002882
17 0.0315630669 -0.0002073811
18 0.0463632795 -0.0004808496
19 0.0298346306 0.000543272
20 0.025364196 -0.0000786351
21 -0.0338975533 -0.0007363123
22 0.0449317536 0.0007371102
23 0.0086758895 -0.000347978
24 0.0079681268 -0.0006378686
25 0.0228183657 0.00059959
26 0.0208508978 -0.000209047
27 -0.0133542483 -0.0004663135
28 0.0394760018 0.0004583494
29 -0.0137807568 -0.0002562337
30 0.0250021563 -0.0006271704
31 0.0263201987 0.0005887958
32 -0.0024499851 -0.0001124952
33 -0.0293465411 -0.0006696042
34 0.0567553888 0.0006565788
35 -0.0156750695 -0.0002113274
36 0.0102927098 -0.000777489
37 0.0122754887 0.000567638
38 -0.0192893795 -0.0000401731
39 0.0215275876 -0.0005977385
40 -0.0609651388 0.0006044362
41 -0.0247311954 -0.0002036661
42 0.0849094629 -0.0005392439
43 0.0022192825 0.0007192952
44 -0.0159346561 -0.0000914467
45 -0.0492550441 -0.0006097376
46 -0.0025816822 0.0010117119
47 0.0676837266 -0.0000462678
48 0.0043179215 -0.0005956091
49 0.016934499 0.0007726223
50 0.0025008858 -0.0000838948
51 0.0372344511 -0.0006381612
52 0.0003331431 0.0008583487
53 0.0003763534 -0.0003808898
54 -0.0178923172 -0.000581368
55 0.0357629572 0.0010088186
56 0.0196576583 -0.0002457759
57 0.0192738359 -0.0007531437
58 0.0386422338 0.0006321969
59 0.0008308304 -0.0002572014

Ely Analysis

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.4413890568
R Square 0.1948242995
Adjusted R Square 0.18069841
Standard Error 0.075547291
Observations 59
ANOVA
df SS MS F Significance F
Regression 1 0.0787163796 0.0787163796 13.7920022488 0.0004663344
Residual 57 0.3253214113 0.0057073932
Total 58 0.4040377909
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 0.0021489468 0.0103156076 0.2083199424 0.8357217669 -0.0185077012 0.0228055948 -0.0185077012 0.0228055948
-0.007555786 1.2463113691 0.3355930028 3.7137585071 0.0004663344 0.5742979726 1.9183247656 0.5742979726 1.9183247656
RESIDUAL OUTPUT
Observation Predicted -0.0932535711115112 Residuals
1 -0.0760060663 -0.021910649
2 0.0513948411 0.0192158233
3 0.0177767452 -0.088901216
4 0.0266867858 0.0223056862
5 0.0322716533 0.0354877964
6 -0.0226290649 -0.0882117598
7 0.0055722114 0.2308004374
8 0.0109166191 -0.0465582116
9 0.0649461272 -0.0557571163
10 0.0158508722 0.0099054605
11 0.0469177683 -0.0618653247
12 0.0246375153 -0.0125946107
13 0.0280051032 -0.0040846572
14 -0.0165760112 -0.0228651313
15 0.0637733273 0.0273954299
16 -0.0368996739 0.0020373958
17 0.0392053439 -0.0103203392
18 0.0576148951 0.1262823702
19 0.0370553925 -0.0750740867
20 0.0314947499 0.0092292866
21 -0.042219205 0.0113603792
22 0.0558342618 -0.0240006078
23 0.010736654 0.2029983655
24 0.0098562887 -0.1576224159
25 0.0283280661 -0.1064509401
26 0.0258807904 0.0115090661
27 -0.0166659894 -0.0698892265
28 0.0490480119 -0.0464361929
29 -0.0171965108 -0.0314320163
30 0.0310444193 0.0518118939
31 0.0326838937 -0.0863140281
32 -0.0031024957 0.0422137936
33 -0.0365583342 0.0962819705
34 0.0705413355 0.0311576433
35 -0.0195527909 0.0808060643
36 0.0127477694 0.0029753928
37 0.01521409 -0.0390125371
38 -0.0240485253 -0.0289259018
39 0.0267225051 -0.0021389853
40 -0.0758877821 0.0430781863
41 -0.0308174408 -0.0256881628
42 0.105561364 0.086046953
43 0.0027054696 0.011343849
44 -0.0198756831 -0.0457421847
45 -0.0613219381 -0.0142413498
46 -0.0032663099 0.0249337196
47 0.0841347749 -0.0584714775
48 0.0053159052 0.0186485461
49 0.0210092966 0.0570564329
50 0.0030557477 0.0116712828
51 0.0462598127 0.0015157589
52 0.0003593561 0.0676290638
53 0.000413104 0.0160660081
54 -0.0223107604 -0.0984749495
55 0.0444294645 0.1462876543
56 0.0243965547 -0.1226889014
57 0.0239191293 0.0094233704
58 0.0480109124 -0.1552291755
59 0.0009784147 0.093427075

Part 4 Stock Data

Date Risk-Free Monthly Risk-Free S&P 500 S&P 500 Return Stock Price Return Mutual Fund Price (VINIX) Mutual Fund Return S&P Risk Premium ELY Risk Premium Mutual Fund Risk Premium
2012-03-01 0.00060 0.00005 1408.469971 6.593309 115.374893
2012-04-01 0.00070 0.00006 1397.910034 -0.0074974527 5.978844 -0.0931952378 114.650108 -0.0062819993 -0.00756 -0.09325 -0.00634
2012-05-01 0.00070 0.00006 1310.329956 -0.0626507256 5.393764 -0.097858382 107.760185 -0.060095216 -0.06271 -0.09792 -0.06015
2012-06-01 0.00050 0.00004 1362.160034 0.0395549821 5.774846 0.0706523311 112.195206 0.0411563974 0.03951 0.07061 0.04111
2012-07-01 0.00070 0.00006 1379.319946 0.0125975741 5.36445 -0.0710661375 113.751228 0.0138688813 0.01254 -0.07112 0.01381
2012-08-01 0.00090 0.00008 1406.579956 0.0197633697 5.62767 0.049067472 116.305611 0.0224558719 0.01969 0.04899 0.02238
2012-09-01 0.00080 0.00007 1440.670044 0.0242361537 6.009373 0.0678261163 119.322975 0.0259434087 0.02417 0.06776 0.02588
2012-10-01 0.00110 0.00009 1412.160034 -0.0197894099 5.34384 -0.110749158 117.107437 -0.0185675726 -0.01988 -0.11084 -0.01866
2012-11-01 0.00120 0.00010 1416.180054 0.002846717 6.607512 0.2364726489 117.78566 0.0057914597 0.00275 0.23637 0.00569
2012-12-01 0.00040 0.00003 1426.189941 0.0070682305 6.37223 -0.0356082592 118.858086 0.0091048944 0.00703 -0.03564 0.00907
2013-01-01 0.00050 0.00004 1498.109985 0.0504280965 6.43105 0.0092306775 125.023178 0.0518693528 0.05039 0.00919 0.05183
2013-02-01 0.00080 0.00007 1514.680054 0.0110606492 6.597119 0.0258229994 126.716988 0.0135479679 0.01099 0.02576 0.01348
2013-03-01 0.00080 0.00007 1569.189941 0.0359877235 6.498948 -0.0148808897 131.464584 0.0374661367 0.03592 -0.01495 0.03740
2013-04-01 0.00050 0.00004 1597.569946 0.0180857679 6.577485 0.0120845712 133.989746 0.019207926 0.01804 0.01204 0.01917
2013-05-01 0.00020 0.00002 1630.73999 0.0207628117 6.734931 0.0239371127 137.118759 0.0233526303 0.02075 0.02392 0.02334
2013-06-01 0.00030 0.00003 1606.280029 -0.0149993016 6.469466 -0.0394161425 135.282166 -0.0133941775 -0.01502 -0.03944 -0.01342
2013-07-01 0.00020 0.00002 1685.72998 0.0494620798 7.059387 0.091185424 142.152969 0.0507886827 0.04945 0.09117 0.05077
2013-08-01 0.00040 0.00003 1632.969971 -0.031298019 6.813516 -0.0348289448 138.032333 -0.0289873369 -0.03133 -0.03486 -0.02902
2013-09-01 0.00020 0.00002 1681.550049 0.0297495232 7.010438 0.0289016713 142.362732 0.0313723524 0.02973 0.02889 0.03136
2013-10-01 0.00110 0.00009 1756.540039 0.0445957526 8.300281 0.1839889319 148.90773 0.0459740966 0.04450 0.18390 0.04588
2013-11-01 0.00050 0.00004 1805.810059 0.0280494716 7.985061 -0.0379770275 153.437439 0.0304195692 0.02801 -0.03802 0.03038
2013-12-01 0.00020 0.00002 1848.359985 0.0235627916 8.310378 0.0407407032 157.319748 0.0253022276 0.02355 0.04072 0.02529
2014-01-01 0.00020 0.00002 1782.589966 -0.0355829057 8.054068 -0.030842159 151.873779 -0.0346171989 -0.03560 -0.03086 -0.03463
2014-02-01 0.00050 0.00004 1859.449951 0.04311703 8.310794 0.0318753206 158.81601 0.0457105305 0.04308 0.03183 0.04567
2014-03-01 0.00050 0.00004 1872.339966 0.0069321656 10.087448 0.2137766861 160.145233 0.0083695781 0.00689 0.21374 0.00833
2014-04-01 0.00020 0.00002 1883.949951 0.006200789 8.597033 -0.1477494605 161.321808 0.0073469249 0.00618 -0.14777 0.00733
2014-05-01 0.00030 0.00003 1923.569946 0.02103028 7.925623 -0.078097874 165.103668 0.0234429557 0.02101 -0.07812 0.02342
2014-06-01 0.00020 0.00002 1960.22998 0.0190583317 8.222093 0.0374065231 168.514465 0.0206585174 0.01904 0.03739 0.02064
2014-07-01 0.00020 0.00002 1930.670044 -0.0150798306 7.510565 -0.0865385492 166.188309 -0.0138038951 -0.01510 -0.08656 -0.01382
2014-08-01 0.00030 0.00003 2003.369995 0.0376552955 7.530369 0.0026368189 172.829086 0.0399593512 0.03763 0.00261 0.03993
2014-09-01 0.00010 0.00001 1972.290039 -0.0155138372 7.164241 -0.0486201938 170.404526 -0.0140286572 -0.01552 -0.04863 -0.01404
2014-10-01 0.00020 0.00002 2018.050049 0.0232014608 7.757963 0.0828729798 174.560974 0.0243916526 0.02318 0.08286 0.02437
2014-11-01 0.00040 0.00003 2067.560059 0.0245335888 7.342161 -0.0535968011 179.264053 0.0269423279 0.02450 -0.05363 0.02691
2014-12-01 0.00030 0.00003 2058.899902 -0.0041885879 7.629506 0.0391362979 178.809174 -0.0025374803 -0.00421 0.03911 -0.00256
2015-01-01 0.00020 0.00002 1994.98999 -0.0310408058 8.085295 0.059740303 173.444992 -0.0299994787 -0.03106 0.05972 -0.03002
2015-02-01 0.00020 0.00002 2104.5 0.054892511 8.907696 0.1017156455 183.405701 0.0574286342 0.05488 0.10170 0.05741
2015-03-01 0.00020 0.00002 2067.889893 -0.0173961069 9.45347 0.0612699401 180.495102 -0.0158697302 -0.01741 0.06125 -0.01589
2015-04-01 0.00020 0.00002 2085.51001 0.0085208197 9.602266 0.0157398289 182.215561 0.0095318875 0.00850 0.01572 0.00952
2015-05-01 0.00010 0.00001 2107.389893 0.0104913824 9.373827 -0.0237901137 184.557297 0.01285146 0.01048 -0.02380 0.01284
2015-06-01 0.00010 0.00001 2063.110107 -0.0210116724 8.877332 -0.0529660938 180.991425 -0.0193212193 -0.02102 -0.05297 -0.01933
2015-07-01 0.00030 0.00003 2103.840088 0.0197420297 9.09579 0.0246085198 184.784073 0.0209548491 0.01972 0.02458 0.02093
2015-08-01 0.00040 0.00003 1972.180054 -0.0625808182 8.797664 -0.0327762624 173.636536 -0.0603273692 -0.06261 -0.03281 -0.06036
2015-09-01 0.00010 0.00001 1920.030029 -0.0264428316 8.30062 -0.0564972702 169.30838 -0.0249265281 -0.02645 -0.05651 -0.02493
2015-10-01 0.00010 0.00001 2079.360107 0.0829831178 9.891157 0.1916166503 183.594376 0.0843785523 0.08297 0.19161 0.08437
2015-11-01 0.00070 0.00006 2080.409912 0.0005048693 10.030698 0.0141076519 184.144592 0.002996911 0.00045 0.01405 0.00294
2015-12-01 0.00170 0.00014 2043.939941 -0.0175301852 9.373926 -0.0654762012 181.219559 -0.0158844361 -0.01767 -0.06562 -0.01603
2016-01-01 0.00230 0.00019 1940.23999 -0.050735322 8.667398 -0.0753716212 172.217819 -0.049673115 -0.05093 -0.07556 -0.04986
2016-02-01 0.00260 0.00022 1932.22998 -0.0041283604 8.857076 0.0218840764 171.984756 -0.0013533036 -0.00435 0.02167 -0.00157
2016-03-01 0.00250 0.00021 2059.73999 0.0659911146 9.086223 0.0258716308 183.653198 0.0678457921 0.06578 0.02566 0.06764
2016-04-01 0.00190 0.00016 2065.300049 0.0026993985 9.305408 0.0241227846 184.365891 0.0038806457 0.00254 0.02396 0.00372
2016-05-01 0.00230 0.00019 2096.949951 0.0153246024 10.033625 0.0782573961 187.665817 0.017898788 0.01513 0.07807 0.01771
2016-06-01 0.00220 0.00018 2098.860107 0.0009109211 10.18323 0.0149103639 188.153809 0.0026003244 0.00073 0.01473 0.00242
2016-07-01 0.00260 0.00022 2173.600098 0.0356098011 10.671946 0.0479922382 195.080307 0.0368129566 0.03539 0.04778 0.03660
2016-08-01 0.00260 0.00022 2170.949951 -0.0012192431 11.399827 0.0682050865 195.355011 0.0014081585 -0.00144 0.06799 0.00119
2016-09-01 0.00190 0.00016 2168.27002 -0.0012344508 11.589491 0.0166374455 195.385056 0.0001537969 -0.00139 0.01648 -0.00000
2016-10-01 0.00240 0.00020 2126.149902 -0.0194256793 10.191964 -0.1205857099 191.814651 -0.0182736852 -0.01963 -0.12079 -0.01847
2016-11-01 0.00300 0.00025 2198.810059 0.0341745222 12.138294 0.1909671188 198.91597 0.0370217758 0.03392 0.19072 0.03677
2016-12-01 0.00420 0.00035 2238.830078 0.0182007622 10.949441 -0.0979423468 202.846924 0.0197618824 0.01785 -0.09829 0.01941
2017-01-01 0.00500 0.00042 2278.870117 0.0178843582 11.319085 0.0337591663 206.688309 0.0189373589 0.01747 0.03334 0.01852
2017-02-01 0.00480 0.00040 2363.639893 0.0371981603 10.11 -0.1068182631 214.88855 0.0396744307 0.03680 -0.10722 0.03927
2017-03-01 0.00660 0.00055 2362.719971 -0.0003891972 11.07 0.0949554896 215.130005 0.001123629 -0.00094 0.09441 0.00057