Can someone please help me on this problem. I suck at excel and can't find how to do this problem anywhere. Please Please Please help me out! Thanks
+ KO -Y (fx 100% Q Search in Sheet At Home Format Layout Tables Charts SmartArt Formulas Data Review Developer Number Edit Font Alignment Format Cells Themes Fill Calibri (Body) - 10.... A- A abc Wrap Text General Text Indent C Normal Bad XI B I Clear A = = = Paste .00 - % % ) Merge 4.0 .00 Good Neutral Calculation Conditional Formatting Insert Delete Format Themes Aa- D E F G H K L M N o Q R S T U v W 0 0 I buy Widgets from a wholesaler at $3.50 each and sell them from my store in a strip mall for $10.00. It costs $1.50 to ship them to my store, so clear $5.00 per Widget. Demand for my Widgets is 50,000 units, so my Gross Profits are $250,000/year. According to a trade organization, Price Elasticity models show if I could sell them for $8.00, I could sell 100,000 units. I'd only make $3.00 per unit, but my gross profit would increase to $300,000. However, the increase in market share would eventually translate to more sales when customers return to buy new Widgets to replace their worn out Widgets. The risk of this model being wrong is very low since stores in other states have experienced similar volume increases. New With a sizeable investment, I could practically eliminate the shipping costs, and I know I could make them cheaper if I had the equipment. I would have to buy $50,000 in raw materials. Land about a mile away is available for $150,000 and it would cost $350,000 for the equipment and a building. My shipping costs would drop to only 15 cents per Widget, and my cost per unit would only be $1.00. I'm not good a financial stuff, so a $550,000 up front cost doesn't seem worth it to me, so I've asked you for your opinion. The Building and Equipment depreciate over 5 years with a salvage value of $250,000. The land would appreciate in value to 275,000. My tax rate is 25% and I can get a loan at 3.8% and my Money Market Savings Account earns 1.29% For the terminal cash flow, assume I will have and can sell my raw materials for the same $50,000. This is not an annual expense since the cost of this material is in the cost of Goods Sold (Cost per Widget). What do you think? Should I do it? years TextBox 5 X fx A B 1 2 Perform the Cost Benefit Analysis for the given Scenario 3 140 Points) 4 5 6 7 Current 8 Demand for Widgets 9 Selling Price 10 11 Cost per Widget 12 Shipping Cost per Widget 13 Per Widget GP 14 Gross Profit 15 Investment in raw materials 16 Investment in Land 17 Salvage value of land 18 Actual Sale of Land 19 Plant and Equipment 20 Salvage value of Plant & Equipment 21 Actual Sale of Plant & Equipment 22 Depreciable P&E 23 Life of Project 24 25 Tax Rate 26 WACC 27 Investment/Savings Rate 28 What's my initial outlay? 29 Annual Change in After-tax Cash Flow 30 Terminal Cash Flow 31 32 Payback Period 33 Discounted Payback 34 NPV 35 Profitability Index 36 IRR 37 MIRR 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 o NPV (10 points) 0 (5 points) (5 points) (5 points) (5 points) (5 points) (5 points) 0 0 0 0 0 0 CF Year o 1 2 3 4 5 54 55 56 57 58 CO Be WACC Cost Benefit Analysis + 200 RRRR Start Here Ready Normal View Sum-0 + KO -Y (fx 100% Q Search in Sheet At Home Format Layout Tables Charts SmartArt Formulas Data Review Developer Number Edit Font Alignment Format Cells Themes Fill Calibri (Body) - 10.... A- A abc Wrap Text General Text Indent C Normal Bad XI B I Clear A = = = Paste .00 - % % ) Merge 4.0 .00 Good Neutral Calculation Conditional Formatting Insert Delete Format Themes Aa- D E F G H K L M N o Q R S T U v W 0 0 I buy Widgets from a wholesaler at $3.50 each and sell them from my store in a strip mall for $10.00. It costs $1.50 to ship them to my store, so clear $5.00 per Widget. Demand for my Widgets is 50,000 units, so my Gross Profits are $250,000/year. According to a trade organization, Price Elasticity models show if I could sell them for $8.00, I could sell 100,000 units. I'd only make $3.00 per unit, but my gross profit would increase to $300,000. However, the increase in market share would eventually translate to more sales when customers return to buy new Widgets to replace their worn out Widgets. The risk of this model being wrong is very low since stores in other states have experienced similar volume increases. New With a sizeable investment, I could practically eliminate the shipping costs, and I know I could make them cheaper if I had the equipment. I would have to buy $50,000 in raw materials. Land about a mile away is available for $150,000 and it would cost $350,000 for the equipment and a building. My shipping costs would drop to only 15 cents per Widget, and my cost per unit would only be $1.00. I'm not good a financial stuff, so a $550,000 up front cost doesn't seem worth it to me, so I've asked you for your opinion. The Building and Equipment depreciate over 5 years with a salvage value of $250,000. The land would appreciate in value to 275,000. My tax rate is 25% and I can get a loan at 3.8% and my Money Market Savings Account earns 1.29% For the terminal cash flow, assume I will have and can sell my raw materials for the same $50,000. This is not an annual expense since the cost of this material is in the cost of Goods Sold (Cost per Widget). What do you think? Should I do it? years TextBox 5 X fx A B 1 2 Perform the Cost Benefit Analysis for the given Scenario 3 140 Points) 4 5 6 7 Current 8 Demand for Widgets 9 Selling Price 10 11 Cost per Widget 12 Shipping Cost per Widget 13 Per Widget GP 14 Gross Profit 15 Investment in raw materials 16 Investment in Land 17 Salvage value of land 18 Actual Sale of Land 19 Plant and Equipment 20 Salvage value of Plant & Equipment 21 Actual Sale of Plant & Equipment 22 Depreciable P&E 23 Life of Project 24 25 Tax Rate 26 WACC 27 Investment/Savings Rate 28 What's my initial outlay? 29 Annual Change in After-tax Cash Flow 30 Terminal Cash Flow 31 32 Payback Period 33 Discounted Payback 34 NPV 35 Profitability Index 36 IRR 37 MIRR 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 o NPV (10 points) 0 (5 points) (5 points) (5 points) (5 points) (5 points) (5 points) 0 0 0 0 0 0 CF Year o 1 2 3 4 5 54 55 56 57 58 CO Be WACC Cost Benefit Analysis + 200 RRRR Start Here Ready Normal View Sum-0
