Feasibility study - Car Wash
do7aim
FesibilityStudy_Finanically.xlsxContent
| 1.1 | Cash flow |
| 1.2 | Cash flow |
| 2.1 | Annuity |
| 2.2 | Annuity |
| 2.3 | Annuity |
| 3.1 | NPV Using Constant Discounting |
| 3.2 | NPV Using Constant Discounting |
| 4.1 | NPV Using General Discounting |
| 4.2 | NPV Using General Discounting |
| 5.1 | Loan Amortization |
| 5.2 | Loan Amortization |
| 9.1 | Firm and project Val |
| 9.7 | Firm and project Val |
| 15.1 | Break Even Analysis |
| 15.2 | Break Even Analysis |
&"Arial"&10&K000000&1#
&1#&"Arial"&10&K000000Saudi Aramco: Company General Use
Sheet1
| Location Evaluation | |||||||||
| Criteria | Khobar suopr foam car wach | ||||||||
| population density (15) | 15 | ||||||||
| purshusing power and social class (10) | 8 | ||||||||
| possibility of obtaining licenses (5) | 5 | ||||||||
| transferring materials to the site (5) | 3 | ||||||||
| clossness to vital places (5) | 5 | ||||||||
| labor accommedation (10) | 10 | ||||||||
| easy access (10) | 10 | ||||||||
| capacity (10) | 10 | ||||||||
| rent (10) | 9 | ||||||||
| compititors (10) | 8 | ||||||||
| total (90) | 83 | ||||||||
| Foundation expenses | |||||||||
| qty | unit price | total | |||||||
| transfer kfala fees | 19 | 2000 | 38,000.00 [$ر.س.-401] | ||||||
| visa and imgration | 19 | 1750 | 33,250.00 [$ر.س.-401] | ||||||
| waterpumps | 4 | 5500 | 22,000.00 [$ر.س.-401] | ||||||
| water hose | 7 | 500 | 3,500.00 [$ر.س.-401] | ||||||
| air compressor | 2 | 6000 | 12,000.00 [$ر.س.-401] | ||||||
| steam machine | 1 | 27000 | 27,000.00 [$ر.س.-401] | ||||||
| car vacuum | 4 | 3500 | 14,000.00 [$ر.س.-401] | ||||||
| washing tools and supplies | 1 | 5000 | 5,000.00 [$ر.س.-401] | ||||||
| logo and sinages | 1 | 26000 | 26,000.00 [$ر.س.-401] | ||||||
| decorations | 1 | 60000 | 60,000.00 [$ر.س.-401] | ||||||
| rent for 6 month | 0.5 | 100000 | 50,000.00 [$ر.س.-401] | ||||||
| labor accomodation | 1 | 10000 | 10,000.00 [$ر.س.-401] | ||||||
| other | 25,000.00 [$ر.س.-401] | ||||||||
| needed capital | 325,750.00 [$ر.س.-401] | ||||||||
| Operational Expenses | |||||||||
| Column1 | monthly | yearly | |||||||
| salary | 44300 | 531600 | |||||||
| advertismnets | 700 | 8400 | |||||||
| stationary | 100 | 1200 | |||||||
| food expenses | 1600 | 19200 | |||||||
| transportation | 600 | 7200 | |||||||
| safty equipments | 200 | 2400 | |||||||
| rent | 8333 | 99996 | |||||||
| internet | 500 | 6000 | |||||||
| hospitality | 100 | 1200 | |||||||
| electriciety | 2000 | 24000 | |||||||
| water | 3000 | 36000 | |||||||
| medical | 500 | 6000 | |||||||
| maintenance | 700 | 8400 | |||||||
| commission | 500 | 6000 | |||||||
| tools | 500 | 6000 | |||||||
| extra labor | 1200 | 14400 | |||||||
| total | 64833 | 777996 | |||||||
| Labor costs | |||||||||
| job | qty | position | skill | nationalty | salry | ministiry of labor fees | monthly for 1 | total month | total year |
| manager | 1 | A | exelant | saudi | 4000 | 0 | 4000 | 4000 | 48000 |
| supervisor | 1 | B | very good | indian | 1600 | 900 | 2500 | 2500 | 30000 |
| labor | 18 | C | good | bangladishi | 1200 | 900 | 2100 | 37800 | 453600 |
| الاجمالي | 20 | 1800 | 8600 | 44300 | 531600 | ||||
| Incom | |||||||||
| Column1 | car per day | avreg incom per car | daily incom | monthly incom | yearly incom | ||||
| year 1 | 60 | 35 | 2100 | 63000 | 756000 | ||||
| year 2 | 70 | 35 | 2450 | 73500 | 882000 | ||||
| year 3 | 80 | 40 | 3200 | 96000 | 1152000 | ||||
| year 4 | 90 | 40 | 3600 | 108000 | 1296000 | ||||
| year 5 | 100 | 40 | 4000 | 120000 | 1440000 | ||||
| Resultes | |||||||||
| Column1 | Column2 | monthly | yearly | Column3 | |||||
| year 1 | incom | 63000 | 756000 | ||||||
| cost | 64833 | 777996 | |||||||
| result | -1833 | -21996 | -6.75% | ||||||
| year 2 | incom | 73500 | 882000 | ||||||
| cost | 64833 | 777996 | |||||||
| result | 8667 | 104004 | 31.93% | ||||||
| year 3 | incom | 96000 | 1152000 | ||||||
| cost | 64833 | 777996 | |||||||
| result | 31167 | 374004 | 114.81% | ||||||
| year 4 | incom | 108000 | 1296000 | ||||||
| cost | 64833 | 777996 | |||||||
| result | 43167 | 518004 | 159.02% | ||||||
| year 5 | incom | 120000 | 1440000 | ||||||
| cost | 64833 | 777996 | |||||||
| result | 55167 | 662004 | 203.22% |
1.1
| SINGLE CASH FLOW | Present Value | |||||
| Inputs | ||||||
| Single Cash Flow | $1,000.00 | 20 | ||||
| Discount Rate / Period | 6.0% | 6 | ||||
| Number of Periods | 5 | 5 | ||||
| Present Value using a Time Line | ||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 |
| Cash Flows | ||||||
| Present Value | $747.26 | |||||
| Present Value using the Formula | ||||||
| Present Value | $747.26 | |||||
| Present Value using the PV Function | ||||||
| Present Value | $747.26 | |||||
&"Arial"&10&K000000&1#
&1#&"Arial"&10&K000000Saudi Aramco: Company General Use
Single Cash Flow - Present Value
Cash Flows 0 1 2 3 4 5 Present Value 0 1 2 3 4 5 747.25817286605684Period
1.2
| SINGLE CASH FLOW | Future Value | |||||
| Inputs | ||||||
| Single Cash Flow | $747.26 | 14 | ||||
| Discount Rate / Period | 6.0% | 6 | ||||
| Number of Periods | 5 | 5 | ||||
| Future Value using a Time Line | ||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 |
| Cash Flows | $747.26 | |||||
| Future Value | $1,000.00 | |||||
| Future Value using the Formula | ||||||
| Future Value | $1,000.00 | |||||
| Future Value using the FV Function | ||||||
| Future Value | $1,000.00 | |||||
&"Arial"&10&K000000&1#
&1#&"Arial"&10&K000000Saudi Aramco: Company General Use
Single Cash Flow - Future Value
Cash Flows 0 1 2 3 4 5 747.26 Future Value 0 1 2 3 4 5 1000.0024451173764Period
2.1
| ANNUITY | Present Value | |||||
| Inputs | ||||||
| Payment | $80.00 | 8 | ||||
| Discount Rate / Period | 6.0% | 6 | ||||
| Number of Periods | 5 | 5 | ||||
| Present Value | $336.99 | 6 | ||||
| Annuity Present Value using a Time Line | ||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 |
| Cash Flows | $80.00 | $80.00 | $80.00 | $80.00 | $80.00 | $80.00 |
| Present Value of Each Cash Flow | $75.47 | $71.20 | $67.17 | $63.37 | $59.78 | |
| Present Value | $336.99 | |||||
| Annuity Present Value using the Formula | ||||||
| Present Value | $336.99 | |||||
| Annuity Present Value using the PV Function | ||||||
| Present Value | $336.99 | |||||
&"Arial"&10&K000000&1#
Annuity
Cash Flows 0 1 2 3 4 5 80 80 80 80 80 80 Present Value of Each Cash Flow 0 1 2 3 4 5 75.471698113207538 71.199715201139185 67.169542642584133 63.367493059041635 59.780653829284553Period
2.2
| ANNUITY | Future Value | |||||
| Inputs | ||||||
| Payment | $80.00 | 8 | ||||
| Discount Rate / Period | 6.0% | 6 | ||||
| Number of Periods | 5 | 5 | ||||
| Annuity Future Value using a Time Line | ||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 |
| Cash Flows | $80.00 | $80.00 | $80.00 | $80.00 | $80.00 | |
| Future Value of Each Cash Flow | $101.00 | $95.28 | $89.89 | $84.80 | $80.00 | |
| Future Value | $450.97 | |||||
| Annuity Future Value using the Formula | ||||||
| Future Value | $450.97 | |||||
| Annuity Future Value using the FV Function | ||||||
| Future Value | $450.97 | |||||
&"Arial"&10&K000000&1#
Annuity
Cash Flows 0 1 2 3 4 5 80 80 80 80 80 Future Value of Each Cash Flow 0 1 2 3 4 5 100.99815680000003 95.281280000000024 89.888000000000005 84.800000000000011 80Period
2.3
| ANNUITY | System of Four Annuity Variables | |||||
| Inputs | ||||||
| Payment | $80.00 | 8 | ||||
| Discount Rate / Period | 6.0% | 6 | ||||
| Number of Periods | 5 | 5 | ||||
| Present Value | $336.99 | 6 | ||||
| Annuity Present Value using a Time Line | ||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 |
| Cash Flows | $80.00 | $80.00 | $80.00 | $80.00 | $80.00 | $80.00 |
| Present Value of Each Cash Flow | ||||||
| Present Value | ||||||
| Annuity Present Value using the Formula | ||||||
| Present Value | ||||||
| Annuity Present Value using the PV Function | ||||||
| Present Value | ||||||
| Payment | ||||||
| Payment using the Formula | $80.00 | |||||
| Payment using the PMT Function | $80.00 | |||||
| Discount Rate / Period | ||||||
| Discount Rate / Per using the RATE Func | 6.0% | |||||
| Number of Periods | ||||||
| Num of Periods using the NPER Function | 5 |
&"Arial"&10&K000000&1#
Annuity
Cash Flows 0 1 2 3 4 5 80 80 80 80 80 80 Present Value of Each Cash Flow 0 1 2 3 4 5Period
3.1 - 3.2
| NPV USING CONSTANT DISCOUNTING | Nominal and Real Rates | ||||||||||||||
| (in thousands of $) | |||||||||||||||
| Inputs | |||||||||||||||
| Inflation Rate | 3.0% | 3 | |||||||||||||
| Real Discount Rate | 4.854% | 4 | |||||||||||||
| Outputs | |||||||||||||||
| Nominal Discount Rate | 8.0% | ||||||||||||||
| Net Present Value using a Time Line | |||||||||||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 | |||||||||
| Cash Flows | ($100.00) | $21.00 | $34.00 | $40.00 | $33.00 | $17.00 | 10 | 2 | 3 | 4 | 3 | 1 | |||
| Present Value of Each Cash Flow | ($100.00) | $19.44 | $29.15 | $31.75 | $24.26 | $11.57 | |||||||||
| Net Present Value | $16.17 | ||||||||||||||
| Net Present Value using the NPV Function | |||||||||||||||
| Net Present Value | $16.17 | ||||||||||||||
&"Arial"&10&K000000&1#
NPV Using Constant Discounting
Cash Flows 0 1 2 3 4 5 -100 21 34 40 33 17 Present Value of Each Cash Flow 0 1 2 3 4 5 -100 19.444512860323027 29.149725017594321 31.753624816778299 24.256326525817492 11.570117896511826Period
4.1 - 4.2
| NPV USING GENERAL DISCOUNTING | Nominal and Real Rates | |||||||||||||||
| (in thousands of $) | ||||||||||||||||
| 0 | ||||||||||||||||
| Inputs | ||||||||||||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 | Add to All Periods | |||||||||
| Inflation Rate | 3.0% | 2.8% | 2.5% | 2.2% | 2.0% | 0.0% | 2 | 2 | 2 | 2 | 2 | 5 | ||||
| Real Discount Rate | 4.854% | 4.669% | 4.683% | 4.697% | 4.902% | 0.0% | 4 | 4 | 4 | 4 | 4 | 5 | ||||
| Outputs | ||||||||||||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 | ||||||||||
| Nominal Discount Rate | 8.0% | 7.6% | 7.3% | 7.0% | 7.0% | |||||||||||
| Net Present Value using a Time Line | ||||||||||||||||
| Period | 0 | 1 | 2 | 3 | 4 | 5 | ||||||||||
| Cash Flows | ($100.00) | $21.00 | $34.00 | $40.00 | $33.00 | $17.00 | 10 | 2 | 3 | 4 | 3 | 1 | ||||
| Cumulative Discount Factor | 0.0% | 8.0% | 16.2% | 24.7% | 33.4% | 42.8% | ||||||||||
| Present Value of Each Cash Flow | ($100.00) | $19.44 | $29.26 | $32.08 | $24.73 | $11.91 | ||||||||||
| Net Present Value | $17.42 | |||||||||||||||
NPV Using General Discounting
Cash Flows 0 1 2 3 4 5 -100 21 34 40 33 17 Present Value of Each Cash Flow 0 1 2 3 4 5 -100 19.444512860323027 29.258058235727582 32.079423041604514 24.734057380908478 11.908207824613447Period
5.1 - 5.2
| LOAN AMORTIZATION | Basics and Sensitivity Analysis | ||||||||||||||||||||||||||||||
| Inputs | |||||||||||||||||||||||||||||||
| Present value | $300,000 | 30 | |||||||||||||||||||||||||||||
| Interest rate / year | 8.00% | 8 | |||||||||||||||||||||||||||||
| Number of years | 30 | 30 | |||||||||||||||||||||||||||||
| Outputs | |||||||||||||||||||||||||||||||
| Year | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 |
| Beg. Principal Balance | $300,000 | $297,352 | $294,492 | $291,403 | $288,067 | $284,464 | $280,573 | $276,370 | $271,832 | $266,930 | $261,636 | $255,919 | $249,744 | $243,076 | $235,873 | $228,095 | $219,694 | $210,622 | $200,823 | $190,241 | $178,812 | $166,469 | $153,138 | $138,741 | $123,192 | $106,399 | $88,262 | $68,675 | $47,521 | $24,674 | |
| Payment | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | $26,648 | |
| Interest Component | $24,000 | $23,788 | $23,559 | $23,312 | $23,045 | $22,757 | $22,446 | $22,110 | $21,747 | $21,354 | $20,931 | $20,474 | $19,980 | $19,446 | $18,870 | $18,248 | $17,576 | $16,850 | $16,066 | $15,219 | $14,305 | $13,317 | $12,251 | $11,099 | $9,855 | $8,512 | $7,061 | $5,494 | $3,802 | $1,974 | |
| Principal Component | $2,648 | $2,860 | $3,089 | $3,336 | $3,603 | $3,891 | $4,202 | $4,539 | $4,902 | $5,294 | $5,717 | $6,175 | $6,669 | $7,202 | $7,778 | $8,401 | $9,073 | $9,798 | $10,582 | $11,429 | $12,343 | $13,331 | $14,397 | $15,549 | $16,793 | $18,136 | $19,587 | $21,154 | $22,847 | $24,674 | |
| Data Table: Sensitivity of the Interest Component to the Interest Rate / Year | |||||||||||||||||||||||||||||||
| Input Values for | Output Formula: Interest Component | ||||||||||||||||||||||||||||||
| Interest rate / year | $24,000 | $23,788 | $23,559 | $23,312 | $23,045 | $22,757 | $22,446 | $22,110 | $21,747 | $21,354 | $20,931 | $20,474 | $19,980 | $19,446 | $18,870 | $18,248 | $17,576 | $16,850 | $16,066 | $15,219 | $14,305 | $13,317 | $12,251 | $11,099 | $9,855 | $8,512 | $7,061 | $5,494 | $3,802 | $1,974 | |
| 7.00% | $21,000 | $20,778 | $20,540 | $20,285 | $20,013 | $19,722 | $19,410 | $19,076 | $18,719 | $18,337 | $17,928 | $17,491 | $17,023 | $16,522 | $15,987 | $15,413 | $14,800 | $14,144 | $13,442 | $12,690 | $11,886 | $11,026 | $10,105 | $9,120 | $8,066 | $6,939 | $5,732 | $4,441 | $3,060 | $1,582 | |
| 8.00% | $24,000 | $23,788 | $23,559 | $23,312 | $23,045 | $22,757 | $22,446 | $22,110 | $21,747 | $21,354 | $20,931 | $20,474 | $19,980 | $19,446 | $18,870 | $18,248 | $17,576 | $16,850 | $16,066 | $15,219 | $14,305 | $13,317 | $12,251 | $11,099 | $9,855 | $8,512 | $7,061 | $5,494 | $3,802 | $1,974 | |
| Data Table: Sensitivity of the Principal Component to the Interest Rate / Year | |||||||||||||||||||||||||||||||
| Input Values for | Output Formula: Principal Component | ||||||||||||||||||||||||||||||
| Interest rate / year | $2,648 | $2,860 | $3,089 | $3,336 | $3,603 | $3,891 | $4,202 | $4,539 | $4,902 | $5,294 | $5,717 | $6,175 | $6,669 | $7,202 | $7,778 | $8,401 | $9,073 | $9,798 | $10,582 | $11,429 | $12,343 | $13,331 | $14,397 | $15,549 | $16,793 | $18,136 | $19,587 | $21,154 | $22,847 | $24,674 | |
| 7.00% | $3,176 | $3,398 | $3,636 | $3,891 | $4,163 | $4,454 | $4,766 | $5,100 | $5,457 | $5,839 | $6,248 | $6,685 | $7,153 | $7,653 | $8,189 | $8,762 | $9,376 | $10,032 | $10,734 | $11,486 | $12,290 | $13,150 | $14,071 | $15,056 | $16,109 | $17,237 | $18,444 | $19,735 | $21,116 | $22,594 | |
| 8.00% | $2,648 | $2,860 | $3,089 | $3,336 | $3,603 | $3,891 | $4,202 | $4,539 | $4,902 | $5,294 | $5,717 | $6,175 | $6,669 | $7,202 | $7,778 | $8,401 | $9,073 | $9,798 | $10,582 | $11,429 | $12,343 | $13,331 | $14,397 | $15,549 | $16,793 | $18,136 | $19,587 | $21,154 | $22,847 | $24,674 | |
Principal And Interest Payments Over Time
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 21000.000000000004 20777.685526266669 20539.809039372001 20285.281198394707 20012.936408548998 19721.527483414095 19409.719933519744 19076.085855132791 18719.097391258754 18337.119734913533 17928.403642624144 17491.0774238745 17023.138369812379 16522.443581965908 15986.700158970189 15413.454696364766 14800.082051376965 14143.773321240018 13441.522979993482 12690.115114859693 11886.108699166536 11025.821834374858 10105.314889047764 9120.3724575477718 8066.4840558427804 6938.8234660184407 5732.2266349063966 4441.1680256165091 3059.73531367633 1581.6023119003378 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 3175.9210533333535 3398.2355270666885 3636.1120139613558 3890.6398549386504 4162.9846447843593 4454.3935699192625 4766.2011198136133 5099.8351982005661 5456.8236620746029 5838.8013184198244 6247.5174107092134 6684.8436294588573 7152.782683520978 7653.4774713674487 8189.2208943631686 8762.4663569685908 9375.8390019563922 10032.147732093339 10734.398073339875 11485.805938473664 12289.812354166821 13150.099218958499 14070.606164285593 15055.548595785585 16109.436997490577 17237.097587314915 18443.69441842696 19734.753027716848 21116.185739657027 22594.31874143302 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 24000 23788.141598705464 23559.334525307368 23312.222886037423 23045.34231562588 22757.111299581415 22445.821802253395 22109.62914513913 21746.541075455723 21354.405960197644 20930.900035718922 20473.5136372819 19979.536326969919 19446.040831832976 18869.865697085079 18247.596551557352 17575.545874387404 16849.73114304386 16065.851233192834 15219.260930553726 14304.94340370349 13317.480474705233 12251.020511387116 11099.243751003551 9855.3248497893001 8511.8924364779086 7060.9854301016057 5494.0058632151995 3801.6679309778801 1973.9429641615754 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 2648.2300161816893 2860.088417476225 3088.8954908743217 3336.0071301442658 3602.8877005558097 3891.1187166002746 4202.4082139282946 4538.6008710425594 4901.6889407259659 5293.8240559840451 5717.3299804627677 6174.7163788997896 6668.6936892117701 7202.1891843487138 7778.3643190966104 8400.6334646243376 9072.6841417942851 9798.4988731378289 10582.378782988855 11428.969085627963 12343.286612478199 13330.749541476456 14397.209504794573 15548.986265178139 16792.905166392389 18136.337579703781 19587.244586080084 21154.22415296649 22846.562085203808 24674.287052020114Time (Years)
Principal And Interest Components
Loan Amortization
Payment 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 26648.230016181689 Interest Component 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 24000 23788.141598705464 23559.334525307368 23312.222886037423 23045.34231562588 22757.111299581415 22445.821802253395 22109.62914513913 21746.541075455723 21354.405960197644 20930.900035718922 20473.5136372819 19979.536326969919 19446.040831832976 18869.865697085079 18247.596551557352 17575.545874387404 16849.73114304386 16065.851233192834 15219.260930553726 14304.94340370349 13317.480474705233 12251.020511387116 11099.243751003551 9855.3248497893001 8511.8924364779086 7060.9854301016057 5494.0058632151995 3801.6679309778801 1973.9429641615754 Principal Component 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 2648.2300161816893 2860.088417476225 3088.8954908743217 3336.0071301442658 3602.8877005558097 3891.1187166002746 4202.4082139282946 4538.6008710425594 4901.6889407259659 5293.8240559840451 5717.3299804627677 6174.7163788997896 6668.6936892117701 7202.1891843487138 7778.3643190966104 8400.6334646243376 9072.6841417942851 9798.4988731378289 10582.378782988855 11428.969085627963 12343.286612478199 13330.749541476456 14397.209504794573 15548.986265178139 16792.905166392389 18136.337579703781 19587.244586080084 21154.22415296649 22846.562085203808 24674.287052020114Year
9.1-9.7
| FIRM AND PROJECT VALUATION | Five Equivalent Methods | |||||||||||||||
| Inputs | ||||||||||||||||
| Valuation Object | 1 | |||||||||||||||
| Date 0 Proj Investment or Firm Cap | $800.00 | 50 | ||||||||||||||
| Tax Rate | 40.0% | 40 | ||||||||||||||
| Unlevered Cost of Equity Capital | 10.0% | 100 | ||||||||||||||
| Riskfree Rate=Cost of Riskfree Debt | 3.0% | 30 | ||||||||||||||
| Infinte Horizon Growth Rate | 5.0% | 50 | ||||||||||||||
| Include Infinite Horizon? | 1 | |||||||||||||||
| Cash Flows | 2nd Stage: | |||||||||||||||
| First Stage: Finite Horizon | Infin Horiz | |||||||||||||||
| Date | 0 | 1 | 2 | 3 | 4 | 5 | 6 | |||||||||
| Revenues | $650.00 | $690.00 | $720.00 | $755.00 | $775.00 | $840.00 | 50 | 50 | 50 | 50 | 50 | 55 | ||||
| Expenses | $410.00 | $435.00 | $445.00 | $470.00 | $470.00 | $475.00 | 50 | 50 | 50 | 50 | 50 | 50 | ||||
| Gross Earnings | ||||||||||||||||
| Depreciation | $60.00 | $60.00 | $60.00 | $60.00 | $60.00 | $60.00 | 50 | 50 | 50 | 50 | 50 | 50 | ||||
| Earnings Bef Interest & Tax (EBIT) | ||||||||||||||||
| Interest Expense | ||||||||||||||||
| Earnings Before Tax | ||||||||||||||||
| Taxes | ||||||||||||||||
| Earnings | ||||||||||||||||
| Add Back Depreciation | ||||||||||||||||
| Cash Flow from Operations | ||||||||||||||||
| New Invest in Plant and Equipment | ($60.00) | ($60.00) | ($60.00) | ($60.00) | ($60.00) | 50 | 50 | 50 | 50 | 50 | ||||||
| After-Tax Salvage Value | $0.00 | 50 | ||||||||||||||
| New Invest in Working Capital | ($10.00) | ($10.00) | ($10.00) | ($10.00) | ($10.00) | ($10.00) | 50 | 50 | 50 | 50 | 50 | 50 | ||||
| Cash Flows from Investments | ||||||||||||||||
| New Borrowing (Repayment) | $5.00 | $5.00 | $5.00 | $5.00 | $5.00 | 50 | 50 | 50 | 50 | 50 | ||||||
| Free Cash Flow to Equity (FCFE) | ||||||||||||||||
| = Dividends | ||||||||||||||||
| Interest | ||||||||||||||||
| Less New Borrowing (Repayment) | ||||||||||||||||
| Cash Flow to Debtholders (CFD) | ||||||||||||||||
| Tax Shield Benefit | ||||||||||||||||
| Free Cash Flow to the Firm (FCFF) | ||||||||||||||||
| Alternative Way to get FCFF | ||||||||||||||||
| Earnings | ||||||||||||||||
| After-tax Interest Expense | ||||||||||||||||
| Net Oper. Profit After Tax (NOPAT) | ||||||||||||||||
| Depreciation | ||||||||||||||||
| Cash Flows from Investments | ||||||||||||||||
| Free Cash Flow to the Firm (FCFF) | ||||||||||||||||
| Debt (D) | $250.00 | 50 | ||||||||||||||
| Book Value of Equity | ||||||||||||||||
| Total Capital | ||||||||||||||||
| Economic Profit | ||||||||||||||||
| Net Oper. Profit After Tax (NOPAT) | ||||||||||||||||
| Capital Charge | ||||||||||||||||
| Economic Profit | ||||||||||||||||
| (1.) Adjusted Present Value (APV) | 2nd Stage: | |||||||||||||||
| First Stage: Finite Horizon | Infin Horiz | |||||||||||||||
| Date | 0 | 1 | 2 | 3 | 4 | 5 | 6 | |||||||||
| Free Cash Flow to the Firm (FCFF) | ||||||||||||||||
| Value of the Unlevered Firm | ||||||||||||||||
| Tax Shield Benefit | ||||||||||||||||
| Value of the Tax Shield Benefit | ||||||||||||||||
| Value of the Firm (APV Method) | ||||||||||||||||
| - Date 0 Firm Capital | ||||||||||||||||
| Value Added by Firm (APV Method) | ||||||||||||||||
| (2) Free Cash Flow to Equity (FCFE) | 2nd Stage: | |||||||||||||||
| First Stage: Finite Horizon | Infin Horiz | |||||||||||||||
| Date | 0 | 1 | 2 | 3 | 4 | 5 | 6 | |||||||||
| Debt + Equity (D+E) | ||||||||||||||||
| Equity (E) | ||||||||||||||||
| Levered Cost of Equity Capital | ||||||||||||||||
| Free Cash Flow to Equity (FCFE) | ||||||||||||||||
| Value of Equity (E) | ||||||||||||||||
| Value of Debt (D) | ||||||||||||||||
| Value of the Firm (FCFE Method) | ||||||||||||||||
| - Date 0 Firm Capital | ||||||||||||||||
| Value Added by Firm (FCFE Method) | ||||||||||||||||
| (3) Free Cash Flow to the Firm (FCFF) | 2nd Stage: | |||||||||||||||
| First Stage: Finite Horizon | Infin Horiz | |||||||||||||||
| Date | 0 | 1 | 2 | 3 | 4 | 5 | 6 | |||||||||
| Equity Weight (E / (D+E)) | ||||||||||||||||
| Debt Weight (D / (D+E)) | ||||||||||||||||
| Cost of Firm Capital (WACC) | ||||||||||||||||
| Free Cash Flow to the Firm (FCFF) | ||||||||||||||||
| Value of the Firm (FCFF Method) | ||||||||||||||||
| - Date 0 Firm Capital | ||||||||||||||||
| Value Added by Firm (FCFF Method) | ||||||||||||||||
| (4) Dividend Discount Model (DDM) | 2nd Stage: | |||||||||||||||
| First Stage: Finite Horizon | Infin Horiz | |||||||||||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | |||||||||
| Dividend | ||||||||||||||||
| Value of Equity (E) | ||||||||||||||||
| Value of Debt (D) | ||||||||||||||||
| Value of the Firm (DDM Method) | ||||||||||||||||
| - Date 0 Firm Capital | ||||||||||||||||
| Value Added by Firm (DDM Method) | ||||||||||||||||
| (5) Residual Income (RI) | 2nd Stage: | |||||||||||||||
| First Stage: Finite Horizon | Infin Horiz | |||||||||||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | |||||||||
| Economic Profit | ||||||||||||||||
| Economic Profit on Salvage Value | ||||||||||||||||
| Value of the Economic Profit | ||||||||||||||||
| + Date 0 Book Value of the Firm | ||||||||||||||||
| Value of the Firm (RI Method) | ||||||||||||||||
| - Date 0 Firm Capital | ||||||||||||||||
| Value Added by Firm (RI Method) | ||||||||||||||||
(13) Free Cash Flow to Equity Enter =C34 and copy across
Firm
Project
Valuation Object
Yes
No
Infinite Horizon
15.1
| BREAK-EVEN ANALYSIS | Based On Accounting Profit | |||||
| Inputs | ||||||
| Fixed Costs | $30,000 | 30 | ||||
| Sales Revenue / Unit | $6.00 | 6 | ||||
| Variable Costs / Unit | $4.00 | 4 | ||||
| Calculate the Break-even Point using the Formula | ||||||
| Break-even Point (Unit Sales) | 15,000 | |||||
| Back solve for the Break-even Point using the Income Statement | ||||||
| Unit Sales | 15,000 | |||||
| Sales Revenue | $90,000 | |||||
| Variable Costs | $60,000 | |||||
| Gross Margin | $30,000 | |||||
| Fixed Costs | $30,000 | |||||
| Accounting Profit | $0 | |||||
| Data Table: Sensitivity of Costs, Revenues, and Acct. Profit to Unit Sales | ||||||
| Input Values for Unit Sales | ||||||
| Output Formulas: | 0 | 5,000 | 10,000 | 15,000 | 20,000 | |
| Total Costs | $90,000 | $30,000 | $50,000 | $70,000 | $90,000 | $110,000 |
| Sales Revenue | $90,000 | $0 | $30,000 | $60,000 | $90,000 | $120,000 |
| Accounting Profit | $0 | ($30,000) | ($20,000) | ($10,000) | $0 | $10,000 |
Break-Even Point Based On Acct. Profit = 0
Total Costs 0 5000 10000 15000 20000 30000 50000 70000 90000 110000 Sales Revenue 0 5000 10000 15000 20000 0 30000 60000 90000 120000 Accounting Profit 0 5000 10000 15000 20000 -30000 -20000 -10000 0 10000Unit Sales
15.2
| BREAK-EVEN ANALYSIS | Based On NPV | |||||||
| (in thousands of $) | ||||||||
| Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | |
| Key Assumptions | ||||||||
| Sales Growth Rate | 55.0% | 40.0% | 25.0% | 5.0% | -20.0% | -50.0% | ||
| Change in Sales Growth Rate | -15.0% | -15.0% | -20.0% | -25.0% | -30.0% | |||
| Inflation Rate | 2.0% | 2.5% | 3.0% | 3.5% | 4.0% | 4.0% | 4.0% | |
| Real Cost of Capital | 11.0% | 11.2% | 11.4% | 11.6% | 11.8% | 12.0% | 12.2% | |
| Tax Rate | 35.0% | 35.0% | 35.0% | 35.0% | 35.0% | 35.0% | 35.0% | |
| Discounting | ||||||||
| Discount Rate = Cost of Capital | 13.2% | 14.0% | 14.7% | 15.5% | 16.3% | 16.5% | 16.7% | |
| Cumulative Discount Factor | 0.0% | 13.2% | 29.0% | 48.1% | 71.0% | 98.9% | 131.6% | 170.3% |
| Price or Cost / Unit | ||||||||
| Unit Sales | 1,875 | 2907 | 4070 | 5087 | 5342 | 4273 | 2137 | |
| Sales Revenue / Unit | $9.70 | $9.94 | $10.24 | $10.60 | $11.02 | $11.46 | $11.92 | |
| Variable Cost / Unit | $7.40 | $7.59 | $7.81 | $8.09 | $8.41 | $8.75 | $9.10 | |
| Cash Fixed Costs | $5,280 | $5,412 | $5,574 | $5,769 | $6,000 | $6,240 | $6,490 | |
| Cash Flow Forecasts | ||||||||
| Sales Revenue | $18,192 | $28,903 | $41,678 | $53,921 | $58,881 | $48,989 | $25,474 | |
| Variable Costs | $13,879 | $22,050 | $31,796 | $41,135 | $44,920 | $37,373 | $19,434 | |
| Gross Margin | $4,314 | $6,853 | $9,882 | $12,785 | $13,962 | $11,616 | $6,040 | |
| Cash Fixed Costs | $5,280 | $5,412 | $5,574 | $5,769 | $6,000 | $6,240 | $6,490 | |
| Depreciation | $1,421 | $1,421 | $1,421 | $1,421 | $1,421 | $1,421 | $1,421 | |
| Total Fixed Costs | $6,701 | $6,833 | $6,996 | $7,191 | $7,422 | $7,662 | $7,911 | |
| Operating Profit | ($2,388) | $20 | $2,887 | $5,594 | $6,540 | $3,954 | ($1,871) | |
| Taxes | ($836) | $7 | $1,010 | $1,958 | $2,289 | $1,384 | ($655) | |
| Net Profit | ($1,552) | $13 | $1,876 | $3,636 | $4,251 | $2,570 | ($1,216) | |
| Add Back Depreciation | $1,421 | $1,421 | $1,421 | $1,421 | $1,421 | $1,421 | $1,421 | |
| Operating Cash Flow | ($131) | $1,434 | $3,298 | $5,058 | $5,672 | $3,992 | $205 | |
| Investment in Plant & Equip | ($11,350) | $1,400 | ||||||
| Cash Flows | ($11,350) | ($131) | $1,434 | $3,298 | $5,058 | $5,672 | $3,992 | $1,605 |
| Present Value of Each Cash Flow | ($11,350) | ($115) | $1,111 | $2,227 | $2,957 | $2,852 | $1,723 | $594 |
| Net Present Value | ($0) | |||||||
| Data Table: Sensitivity of Net Present Value to Year 1 Unit Sales | ||||||||
| and Year 2 Sales Growth Rate | ||||||||
| Output Formula: | Input Values for Year 1 Unit Sales | |||||||
| Net Present Value | ($0) | 1,700 | 1,900 | 2,100 | 2,300 | |||
| 45.0% | ($6,767) | ($4,692) | ($2,618) | ($543) | ||||
| Input Values for Year 2 | 50.0% | ($4,673) | ($2,352) | ($31) | $2,290 | |||
| Sales Growth Rate | 55.0% | ($2,283) | $319 | $2,921 | $5,523 | |||
| 60.0% | $442 | $3,365 | $6,288 | $9,210 | ||||
| 65.0% | $3,548 | $6,836 | $10,124 | $13,412 |
NPV Break-Even Contour (Based On NPV = 0) Across Year 1 Unit Sales And Year 2 Sales Growth Rate
1,700 0.45 0.5 0.55000000000000004 0.6 0.65 -6767.0836670711742 -4672.9277187851812 -2283.1385032181506 442.23187178181001 3547.5606264680082 1,900 0.45 0.5 0.55000000000000004 0.6 0.65 -4692.4891514404444 -2351.9619151207999 318.97897286588602 3364.9811566893713 6835.6427060445294 2,100 0.45 0.5 0.55000000000000004 0.6 0.65 -2617.8946358097064 -30.996111456413871 2921.0964489499165 6287.7304415969229 10123.724785621049 2,300 0.45 0.5 0.55000000000000004 0.6 0.65 -543.30012017896979 2289.9696922079647 5523.2139250339442 9210.4797265044799 13411.806865197566Year 2 Sales Growth Rate
Net Present Value
Year 1 Unit Sales
NPV Break-Even Contour
