Problem 5 – PW Analysis Your friend brings you the following business proposal t
ID: 2721283 • Letter: P
Question
Problem 5 – PW Analysis
Your friend brings you the following business proposal to be partners in a fast-food joint he wants to open. He says that the shop will require an initial investment cost of 600,000 TL (you will rent the shop, not buy it), and it will generate monthly profit (after all costs are deducted) of 10,000 TL. He predicts that the monthly profit should grow by about 0.6% every month (due to mostly price inflation). His plan is to operate the restaurant for 6 years (i.e. 72 months) and sell it. He also predicts that the shop, with all the equipment and staff in it, could be sold for 500,000 TL after 6 years. He says that if you contribute X% of the initial investment cost, he will give you X% of all the monthly profits and the selling price of the shop in 5 years. To make your decision, perform the following analyses. Show your calculations analytically and using relevant Excel functions.
What is the PW of the investment if you use a MARR equal to a local banks’ monthly interest rate of 0.8% for long-term deposits?
There are several questionable assumptions in your friend’s proposal. One is the value of the shop after having been used for 6 years (all the equipment and furniture will wear down). You do a market research, and find out that restaurant equipment loose roughly half their original value in 6 years. That is the shop can only sell for about 300,000 TL after that period. You also want to do “legitimate” business and pay taxes and other fees, which you estimate to be about 30% of the monthly profits. That is, first month’s profit will rather be 7,000 TL (and grow by 0.5% every month). Calculate the PW of the investment under this new scenario. Is it still an attractive investment?
Explanation / Answer
PW Analysis Amts in TL Given Scenario 1 Given Scenario 2 Months PV factor @0.8% Investment Monthly profit with 0.6% increase Resale value Net Cash flow PV of Cash flows Investment Monthly profit with 0.5% increase Resale value Net Cash flow PV of Cash flows Now 1 (600,000) (600,000.0) (600,000) (600,000) (600,000) (600,000) Month 1 0.992 10,000 10,000.0 9,921 7,000 7,000 6,944 Month 2 0.984 10,060 10,060.0 9,901 7,035 7,035 6,924 Month 3 0.976 10,120 10,120.4 9,881 7,070 7,070 6,903 Month 4 0.969 10,181 10,181.1 9,862 7,106 7,106 6,883 Month 5 0.961 10,242 10,242.2 9,842 7,141 7,141 6,862 Month 6 0.953 10,304 10,303.6 9,823 7,177 7,177 6,842 Month 7 0.946 10,365 10,365.4 9,803 7,213 7,213 6,821 Month 8 0.938 10,428 10,427.6 9,784 7,249 7,249 6,801 Month 9 0.931 10,490 10,490.2 9,764 7,285 7,285 6,781 Month 10 0.923 10,553 10,553.1 9,745 7,321 7,321 6,761 Month 11 0.916 10,616 10,616.5 9,726 7,358 7,358 6,741 Month 12 0.909 10,680 10,680.2 9,706 7,395 7,395 6,720 Month 13 0.902 10,744 10,744.2 9,687 7,432 7,432 6,700 Month 14 0.894 10,809 10,808.7 9,668 7,469 7,469 6,681 Month 15 0.887 10,874 10,873.6 9,649 7,506 7,506 6,661 Month 16 0.880 10,939 10,938.8 9,629 7,544 7,544 6,641 Month 17 0.873 11,004 11,004.4 9,610 7,581 7,581 6,621 Month 18 0.866 11,070 11,070.5 9,591 7,619 7,619 6,601 Month 19 0.860 11,137 11,136.9 9,572 7,658 7,658 6,582 Month 20 0.853 11,204 11,203.7 9,553 7,696 7,696 6,562 Month 21 0.846 11,271 11,270.9 9,534 7,734 7,734 6,543 Month 22 0.839 11,339 11,338.6 9,515 7,773 7,773 6,523 Month 23 0.833 11,407 11,406.6 9,496 7,812 7,812 6,504 Month 24 0.826 11,475 11,475.0 9,478 7,851 7,851 6,484 Month 25 0.819 11,544 11,543.9 9,459 7,890 7,890 6,465 Month 26 0.813 11,613 11,613.1 9,440 7,930 7,930 6,446 Month 27 0.806 11,683 11,682.8 9,421 7,969 7,969 6,427 Month 28 0.800 11,753 11,752.9 9,403 8,009 8,009 6,407 Month 29 0.794 11,823 11,823.4 9,384 8,049 8,049 6,388 Month 30 0.787 11,894 11,894.4 9,365 8,089 8,089 6,369 Month 31 0.781 11,966 11,965.7 9,347 8,130 8,130 6,350 Month 32 0.775 12,038 12,037.5 9,328 8,170 8,170 6,332 Month 33 0.769 12,110 12,109.8 9,310 8,211 8,211 6,313 Month 34 0.763 12,182 12,182.4 9,291 8,252 8,252 6,294 Month 35 0.757 12,256 12,255.5 9,273 8,294 8,294 6,275 Month 36 0.751 12,329 12,329.0 9,254 8,335 8,335 6,256 Month 37 0.745 12,403 12,403.0 9,236 8,377 8,377 6,238 Month 38 0.739 12,477 12,477.4 9,218 8,419 8,419 6,219 Month 39 0.733 12,552 12,552.3 9,199 8,461 8,461 6,201 Month 40 0.727 12,628 12,627.6 9,181 8,503 8,503 6,182 Month 41 0.721 12,703 12,703.4 9,163 8,546 8,546 6,164 Month 42 0.716 12,780 12,779.6 9,145 8,588 8,588 6,146 Month 43 0.710 12,856 12,856.3 9,127 8,631 8,631 6,127 Month 44 0.704 12,933 12,933.4 9,109 8,674 8,674 6,109 Month 45 0.699 13,011 13,011.0 9,090 8,718 8,718 6,091 Month 46 0.693 13,089 13,089.1 9,072 8,761 8,761 6,073 Month 47 0.688 13,168 13,167.6 9,054 8,805 8,805 6,055 Month 48 0.682 13,247 13,246.6 9,036 8,849 8,849 6,037 Month 49 0.677 13,326 13,326.1 9,019 8,893 8,893 6,019 Month 50 0.671 13,406 13,406.1 9,001 8,938 8,938 6,001 Month 51 0.666 13,486 13,486.5 8,983 8,983 8,983 5,983 Month 52 0.661 13,567 13,567.4 8,965 9,027 9,027 5,965 Month 53 0.656 13,649 13,648.8 8,947 9,073 9,073 5,947 Month 54 0.650 13,731 13,730.7 8,929 9,118 9,118 5,930 Month 55 0.645 13,813 13,813.1 8,912 9,164 9,164 5,912 Month 56 0.640 13,896 13,896.0 8,894 9,209 9,209 5,894 Month 57 0.635 13,979 13,979.3 8,876 9,255 9,255 5,877 Month 58 0.630 14,063 14,063.2 8,859 9,302 9,302 5,859 Month 59 0.625 14,148 14,147.6 8,841 9,348 9,348 5,842 Month 60 0.620 14,232 14,232.5 8,824 9,395 9,395 5,825 Month 61 0.615 14,318 14,317.9 8,806 9,442 9,442 5,807 Month 62 0.610 14,404 14,403.8 8,789 9,489 9,489 5,790 Month 63 0.605 14,490 14,490.2 8,771 9,537 9,537 5,773 Month 64 0.601 14,577 14,577.2 8,754 9,584 9,584 5,756 Month 65 0.596 14,665 14,664.6 8,736 9,632 9,632 5,738 Month 66 0.591 14,753 14,752.6 8,719 9,680 9,680 5,721 Month 67 0.586 14,841 14,841.1 8,702 9,729 9,729 5,704 Month 68 0.582 14,930 14,930.2 8,685 9,777 9,777 5,687 Month 69 0.577 15,020 15,019.7 8,667 9,826 9,826 5,670 Month 70 0.572 15,110 15,109.9 8,650 9,875 9,875 5,654 Month 71 0.568 15,201 15,200.5 8,633 9,925 9,925 5,637 Month 72 0.563 15,292 500,000 515,291.7 290,332 9,974 3,000 12,974 7,310 Total PW 347,942 PW (147,649) So PW of the investment under first scenario= $ 347,942 So PW of the investment under second scenario= $ (147,649) So under second scenario the investment is not an attractive one.