Corporate Finance Project

profilemajk_v
500306_Project_Files310.6.xls
Home>Business & Finance homework help>Management homework help>Corporate Finance Project

Part 1 Financial Information

2015 2016
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 6,100,000
Taxes on sale of equipment (1,127,235)
Salvage CF 6,100,000
Cash flow on sale of equipment 4,972,765
Year 0 1 2 3 4 5
CF (10,500,000) (288,865) 3,891,435 4,515,935 4,351,435 11,680,060

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