Question

Tiffany Lamps is considering producing a specialty lamp for the next Olympics, which will occur in two years. The new production line will require a $100,000 investment in fixed assets, which will be depreciated straight line to zero over five years. The new lamp will be sold for only three years. At the end of the project, the fixed assets will be sold for $25,000. The project will incur fixed costs of $150,000 per year, and NWC requirements will run at 20 percent of the sales during the year. The firm has a 7 percent cost of capital and a 39 percent marginal tax rate. 3. (EXCEL TEMPLATE) A marketing firm has identified the estimated unit sales, selling price, and variable costs as shown below. What are your worst-case, base-case, and best-case scenarios for the NPV of the project? Unit Sales Sales Price per Unit Variable Cost per Unit Lower Bound 50,000 $6.25 $3.50 Base Case 100,000 $8.00 $4.50 Upper Bound 150,000 $10.00 $5.00

Answer 1

Con = New Microsoft Office Excel Worksheet - Microsoft Excel - 0 X Page Layout Formulas Data Review View = = = D Accounting Home Insert * Cut La Copy Paste Format Painter Clipboard H18 Wrap Text 1 Merge & Center - Fill 11 - 11 - A A B I USA Font ZI Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format Clear Sort & Find & Filter Select Editing Alignment Number Cells А B C D E K L M N. 1 Scenario analysis bound upper 2.00 4 5 6 7 8 life required return tax rate intial cost salvage value base case 50000.00 * 3.50 ₹ 6.25 unit sales variable cost selling price 20% lower bound 100000.00 4.50 * 8.00 upper bound 150000.00 * 5.00 10.00 39% 1,00,000.00 25,000.00 * unit sales variable cost sellingprice ₹ ₹ 300000.00 13.00 24.25 Year 1 Year 2 Year 3 *26,12,500.00 13,75,000.00 1,50,000.00 * 25,000.00 10,62,500.00 * 4,14,375.00 * 6,48,125.00 * 25,000.00 * 6,73,125.00 Year 0 Initial cost * 1,00,000.00 NWC * 5,22,500.00 OCF. ATSV NWC Total cash flow * 6,22,500.00 * 6,73,125.00 10 income statement 11 Sales 12 variable cost 13 fixed cost 14 depreciation 15 ebit 16 tax 17 net income 18 plus depreciation 19 OCF 20 NPV 21 22 23 S heet6 Sheets Ready * 6,73,125.00 * 6.73,125.00 5,22,500.00 11.95 625.00 * 6.73,125.00 6.73.125.00 H Sheet4 Sheet1 Sheet2 Sheet3 30 + Type here to search o Ei e a D x 110% 0 16:45 x ENG 20 29-02-2020 9 ^ o E 6