Please help on my assignment, need excel formula and please follow the excel template as an example.

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 University of Houston-Victoria School of Business FINC 3304 Business Finance Dr. Xavier Garza Gmez Spring 2016 Mini Project 2 (Capital Budgeting) General Instructions: You'll have to use Microsoft Excel to solve this assignment and you'll submit your Excel file. Face to face students must submit it via email prior to class on the day that is due. Online students must upload Excel file into Blackboard Notes about Excel: You have to use Excel to actually calculate numeric results. Nevertheless, to show the procedure used, please clearly identify the names of your variables (both input and output). In case a formula is needed, please type it near your input variables. Highlight or put a box around your main answers. Write your name and date on each sheet. Suggestions for people with problems understanding how to solve this assignment: Review the Self-Test at the end of each chapter. Ask your classmates or contact me directly with your questions. FINC 3304 - Business Finance Mini Project 2 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