Hi Tutor,

Can you please help my assignment below.

Hi Please Use excel to solve this assignment and have to use excel to actually calculate numeric results, nevertheless to show the procedure used, please clearly identify the names of your variables both output and input. in case a formula is need please type it near your input variables.

CAPITAL BUDGETING Conch Republic Electronics is a midsized electronics manufacturer located in Key West, Florida. The company president is Shelly Couts, who inherited the company. The company originally repaired radios and other household appliances when it was founded over 70 years ago. Over the years, the company has expanded, and it is now a reputable manufacturer of various specialty electronic items. Jay McCanless, a recent MBA graduate, has been hired by the company in its finance department. One of the major revenueproducing items manufactured by Conch Republic is a smart phone. Conch Republic currently has one smart phone model on the market and sales have been excellent. The smart phone is a unique item in that it comes in a variety of tropical colors and is preprogrammed to play Jimmy Buffett music. However, as with any electronic item, technology changes rapidly, and the current smart phone has limited features in comparison with newer models. Conch Republic spent $500,000 to develop a prototype for a new smart phone that has all the features of the existing one but adds new features such as wifi tethering. The company has spent a further $200,000 for a marketing study to determine the expected sales figures for the new smart phone. Conch Republic can manufacture the new smart phone for $175 each in variable costs. Fixed costs for the operation are estimated to run $3.1 million per year. The estimated sales volume is 75,000, 92,000, 95,000, 80,000, and 50,000 per year for the next five years, respectively. The unit price of the new smart phone will be $400. The necessary equipment can be purchased for $25.5 million and will be depreciated on a sevenyear MACRS schedule. It is believed the value of the equipment in five years will be 1.5 million. Net working capital for the smart phones will be 12 percent of sales and will occur with the timing of the cash flows for the year (i.e., there is no initial outlay for NWC). Changes in NWC will thus first occur in Year 1 with the first year's sales. Conch Republic has a 33 percent corporate tax rate and a required return of 12 percent. Shelly has asked Jay to prepare a report that answers the following questions: QUESTIONS 1. What is the payback period of the project? 2. What is the profitability index of the project? 3. What is the IRR of the project? 4. What is the NPV of the project? 5. Perform a sensitivity analysis for the price of the new smart phone. Measure the effects on NPV by moving price 5% up and down. 6. Perform a sensitivity analysis for the quantity of phones sold. Measure the effects on NPV by moving quantity 5% up and down. 7. 8. Should Conch Republic produce the new smart phone? Suppose Conch Republic loses sales on other models because of the introduction of the new model. How would this affect your analysis? FINC 3304 - Business Finance Mini Project 2 Years 1 Years 2 Years 3 75000 92000 95000 ### ### ### ### ### ### $3,100,000 $3,100,000 $3,100,000 3,643,950 6,244,950 4,459,950 ### ### ### $3,343,247 $3,747,167 $4,558,967 $6,787,804 $7,607,884 $9,256,084 $3,643,950 $6,244,950 $4,459,950 ### ### ### Units Sales VC Fixed costs Depreciation EBT Tax Net Income Depreciation OCF Net WC Beginning $0 $3,600,000 $4,416,000 Ending $3,600,000 $4,416,000 $4,560,000 Net WC (CF) -$3,600,000 -$816,000 -$144,000 Investment in Equipment -$25,500,000 CF from sale of Equipment Net Cash Flow Discounting factor Discouted cash flows 1 The payback period is : Payback period = Payback period = -$25,500,000 $6,831,754 1 0.8929 -$25,500,000 $6,099,780 2+($56,31,413/$13,572,034) 2.415 years ### ### 0.7972 0.7118 ### $9,660,305 Years 4 Years 5 80000 50000 ### $20,000,000 ### $8,750,000 $3,100,000 $3,100,000 3,184,950 2,277,150 ### $5,872,850 $3,865,967 $1,938,041 $7,849,084 $3,934,810 $3,184,950 $2,277,150 ### $6,211,960 $4,560,000 $3,840,000 $720,000 $3,840,000 $3,840,000 $2,882,387 ### $12,934,346 0.6355 0.5674 $7,469,901 $7,339,295 2 Years 1 Years 2 Years 3 75000 92000 95000 ### ### ### ### ### ### $3,100,000 $3,100,000 $3,100,000 3,643,950 6,244,950 4,459,950 ### ### ### $3,343,247 $3,747,167 $4,558,967 $6,787,804 $7,607,884 $9,256,084 $3,643,950 $6,244,950 $4,459,950 ### ### ### Units Sales VC Fixed costs Depreciation EBT Tax Net Income Depreciation OCF Net WC Beginning Ending Net WC (CF) Investment in Equipment $0 $3,600,000 $4,416,000 $3,600,000 $4,416,000 $4,560,000 -$3,600,000 -$816,000 -$144,000 ### CF from sale of Equipment Net Cash Flow Discounting factor Discouted cash flows Profitability Index = ### $6,831,754 1 0.8929 ### $6,099,780 1.606 ### ### 0.7972 0.7118 ### $9,660,305 Years 4 Years 5 80000 50000 ### ### ### $8,750,000 $3,100,000 $3,100,000 3,184,950 2,277,150 ### $5,872,850 $3,865,967 $1,938,041 $7,849,084 $3,934,810 $3,184,950 $2,277,150 ### $6,211,960 $4,560,000 $3,840,000 $3,840,000 $720,000 $3,840,000 $2,882,387 ### ### 0.6355 0.5674 $7,469,901 $7,339,295 3 IRR = 32.30% 4 NPV = ### 5 When sale price goes up by 5% Years 1 Years 2 75000 92000 ### ### ### ### $3,100,000 $3,100,000 3,643,950 6,244,950 ### ### $3,838,247 $4,354,367 $7,792,804 $8,840,684 $3,643,950 $6,244,950 ### ### Units Sales VC Fixed costs Depreciation EBT Tax Net Income Depreciation OCF Net WC Beginning Ending Net WC (CF) Investment in Equipment $0 $3,780,000 $3,780,000 $4,636,800 -$3,780,000 -$856,800 -$25,500,000 CF from sale of Equipment Net Cash Flow Discounting factor Discouted cash flows NPV = -$25,500,000 $7,656,754 1 0.8929 -$25,500,000 $6,836,387 ### 0.7972 ### $19,243,275 And the sensitivity of changes in the NPV to changes in the price is: NPV/P = ($15,462,165 - $19,243,275)/($400-$420) NPV/P = $189,055.50 For every dollar change in price of the new PDA, the NPV of the project changes $189,055.50 in the same direction When sale price goes down by 5% Years 3 Years 4 Years 5 95000 80000 50000 ### ### ### ### ### $8,750,000 $3,100,000 $3,100,000 $3,100,000 4,459,950 3,184,950 2,277,150 ### ### $6,872,850 $5,185,967 $4,393,967 $2,268,041 ### $8,921,084 $4,604,810 $4,459,950 $3,184,950 $2,277,150 ### ### $6,881,960 $4,636,800 $4,788,000 $4,032,000 $4,788,000 $4,032,000 -$151,200 $756,000 $4,032,000 oject changes $2,882,387 ### ### ### 0.7118 0.6355 0.5674 ### $8,174,055 $7,828,417 Units Sales VC Fixed costs Depreciation EBT Tax Net Income Depreciation OCF Net WC Beginning Ending Net WC (CF) Investment in Equipment CF from sale of Equipment Net Cash Flow Discounting factor Discouted cash flows NPV = And the sensitivity of changes in the NPV to changes in NPV/P = NPV/P = For every dollar change in price of the new PDA, the NP $189,055.50 wn by 5% Years 1 Years 2 Years 3 Years 4 Years 5 75000 92000 95000 80000 50000 ### ### ### ### ### ### ### ### ### $8,750,000 $3,100,000 $3,100,000 $3,100,000 $3,100,000 $3,100,000 3,643,950 6,244,950 4,459,950 3,184,950 2,277,150 $8,631,050 $9,515,050 ### ### $4,872,850 $2,848,247 $3,139,967 $3,931,967 $3,337,967 $1,608,041 $5,782,804 $6,375,084 $7,983,084 $6,777,084 $3,264,810 $3,643,950 $6,244,950 $4,459,950 $3,184,950 $2,277,150 $9,426,754 ### ### $9,962,034 $5,541,960 $0 $3,420,000 $4,195,200 $4,332,000 $3,648,000 $3,420,000 $4,195,200 $4,332,000 $3,648,000 -$3,420,000 -$775,200 -$136,800 $684,000 $3,648,000 -$25,500,000 $2,882,387 -$25,500,000 $6,006,754 ### ### ### ### 1 0.8929 0.7972 0.7118 0.6355 0.5674 -$25,500,000 $5,363,173 $9,442,629 $8,759,334 $6,765,747 $6,850,173 $11,681,056 anges in the NPV to changes in the price is: ($15,462,165 - $11,681,056)/($400-$380) $189,055.50 n price of the new PDA, the NPV of the project changes in the opposite direction 6 When quatity goes up 5% Years 1 Years 2 Years 3 78750 96600 99750 ### ### ### ### ### ### $3,100,000 $3,100,000 $3,100,000 3,643,950 6,244,950 4,459,950 ### ### ### $4,141,434 $4,726,277 $5,570,004 $8,408,366 $9,595,774 ### $3,643,950 $6,244,950 $4,459,950 ### ### ### Units Sales VC Fixed costs Depreciation EBT Tax Net Income Depreciation OCF Net WC Beginning Ending Net WC (CF) Investme $0 $3,969,000 $4,868,640 $3,969,000 $4,868,640 $5,027,400 -$3,969,000 -$899,640 -$158,760 -$25,500,000 CF from sale of Equipment Net Cash Discounti Discouted NPV = -$25,500,000 $8,083,316 1 0.8929 -$25,500,000 $7,217,246 ### 0.7972 ### ### 0.7118 ### $21,530,093 And the sensitivity of changes in the NPV to changes in the price is: NPV/P($15,462,165 - $21,530,093)/(392000-411600) NPV/P $309.59 For every new unit sold of the new PDA, the NPV of the project changes $309.59 in the same direction Years 4 Years 5 84000 $35,280,000 $14,700,000 $3,100,000 3,184,950 $14,295,050 $4,717,367 $9,577,684 $3,184,950 $12,762,634 $5,027,400 $4,233,600 $793,800 52500 $22,050,000 $9,187,500 $3,100,000 2,277,150 $7,485,350 $2,470,166 $5,015,185 $2,277,150 $7,292,335 $4,233,600 $4,233,600 $2,882,387 $13,556,434 0.6355 $8,615,359 $14,408,321 0.5674 $8,175,668 When quatity goes down 5% For every new unit sold of the new PDA, the NPV of the project changes $309.59 in the Opposite direction 7 Conch Republic should produce the new samrt phone beacuase NPV of the product is posi of the product is positive 8 The loss from the sale of other module would not afftect untill consolidated NPV is positve dated NPV is positve Majestic Mulch and Compost Company (MMCC) YEAR 0 $ $ 3 5,000 6,000 120.00 ### 14.29% 114,320 685,680 24.49% 195,920 489,760 17.49% 139,920 349,840 54,000 90,000 108,000 0 $ $ $ 2 20,000 Background Data: Unit Sales Estimates Variable Cost /unit Fixed Costs per year Sale Price per unit Tax Rate Required Return on Project Yr 0 NWC NWC % of sales Equipment cost - installed Salvage Value in year 8 1 1 2 3 600,000 300,000 25,000 195,920 79,080 26,887 52,193 195,920 248,113 (36,000) 720,000 360,000 25,000 139,920 195,080 66,327 128,753 139,920 268,673 (18,000) 3,000 60.00 25,000.00 120.00 $ 120.00 $ 34.0% 15.0% 20,000.00 15% 800,000 20% of equipment cost Depreciation Calculations: Equipment Depreciable Base MACRS % (Eqpt-7 yr) Recovery Allowance Book Value After-Tax Salvage Value Salvage Value Book Value (Year 8) Capital Gain/Loss Taxes Net SV (SV-Taxes) 800,000 20% 160,000 0 160,000 54,400 105,600 Required Net Working Capital Investment YEAR Initial Investment Equipment Cost Sales Variable Costs Fixed Costs Depreciation (Eqpt)) (800,000) EBT Taxes Net Operating Income Add back Depreciation CASH FLOW from Operations NWC investment & Recovery Salvage Value TOTAL PROJECTED CF (20,000) 360,000 180,000 25,000 114,320 40,680 13,831 26,849 114,320 141,169 (34,000) (820,000) 107,169 212,113 250,673 Discounted Cash Flows (820,000) 93,190 160,388 164,821 Cumulative Cash flows (820,000) (712,831) (500,718) (250,046) NPV IRR $65,483 17.24% Scenario Analysis Units Price/unit Variable cost/unit Fixed cost/year $ $ $ Base 6,000 80.00 $ 60.00 $ 50,000 $ BASE Lower 5,500 75.00 $ 58.00 $ 45,000 $ BEST Upper 6,500 85.00 62.00 55,000 WORST Initial investment $ 200,000 Depreciated to salvage value of 0 over 5 years Deprec/yr $ 40,000 Project Life 5 years Tax rate 34% Required return 12% Units Price/unit Variable cost/unit Fixed Cost Sales Variable Cost Fixed Cost Depreciation EBIT Taxes Net Income + Deprec $ $ $ $ BASE 6,000 80.00 $ 60.00 $ 50,000 $ WORST 5,500 75.00 $ 62.00 $ 55,000 $ BEST 6,500 85.00 58.00 45,000 480,000 $ 360,000 50,000 40,000 30,000 10,200 19,800 40,000 412,500 $ 341,000 55,000 40,000 (23,500) (7,990) (15,510) 40,000 552,500 377,000 45,000 40,000 90,500 30,770 59,730 40,000 TOTAL CF 59,800 24,490 99,730 NPV 15,566 (111,719) 159,504 15.1% -14.4% 40.9% IRR NOTE: Note in WORST CASE, tax credit for negative earnings Sensitivity Analysis Units Price/unit Variable cost/unit Fixed cost/year $ $ $ Base 6,000 80 60 50,000 Units 5,500 80 60 50,000 Fixed Cost 6,000 80 60 55,000 Initial investment $ 200,000 Depreciated to salvage value of 0 over 5 years Deprec/yr $ 40,000 Tax rate Required Return 34% 12% Units Price/unit Variable cost/unit Fixed cost BASE 6,000 80 $ 60 $ 50,000 $ Sales Variable Cost Fixed Cost Depreciation EBIT Taxes Net Income + Deprec $ $ $ $ TOTAL CF NPV 480,000 360,000 50,000 40,000 30,000 10,200 19,800 40,000 $ UNITS 5,500 80 $ 60 $ 50,000 $ 440,000 330,000 50,000 40,000 20,000 6,800 13,200 40,000 $ 59,800 $ 53,200 15,566 $ (8,226) $ FC 6,000 80 60 55,000 480,000 360,000 55,000 40,000 25,000 8,500 16,500 40,000 56,500 3,670 % Change in NPV % Change in Variable -152.8% -8.3% -76.4% 10.0% SENSITIVITY RATIO 18.34 DIRECT -7.64 INVERSE Sensitivity Ratio = (% Change in NPV)/% Change in Variable Positive = Direct relationship Negative = Inverse relationship $ (200,000) $ (200,000) $ (200,000) 59,800 53,200 56,500 59,800 53,200 56,500 59,800 53,200 56,500 59,800 53,200 56,500 59,800 53,200 56,500 PV NPV $ $ 215,566 $ 15,566 $ 191,774 $ (8,226) $ 203,670 3,670