Hello, my question has been answered several times in Chegg, you can easily find
ID: 2658128 • Letter: H
Question
Hello, my question has been answered several times in Chegg, you can easily find the answers. My problem is they do not explain how they got these answers. If you decide to answer my question could you please explain how you got it. Please tell me the formula you used to get it in excel. If its blurry or letters are to small, you can easily see it by zooming in from computer or looking at with your phone.
2 Chapter: 3 Problem 11 18 Estimating Cash Flows and Analyzing Risk Webmasters.com has developed a powerful new server that would be used for corporations' Internet activities. It would cost $10 million at Year 0 to buy the equipment necessary to manufacture the server. The project would require net working capital at the beginning of each year in an amount equal to 10% of the year's projected sales: for example, NWC.-10%( Sales,). The servers would sell for $24,000 per unit, and Webmasters believes that variable costs would amount to $17,500 per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 3%. The company's nonvariable costs would be $1 million at Year 1 and would increase with inflation 13The server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 14 years. Also, the project's returns are expected to be highly correlated with returns on the firm's other assets. The 15 firm believes it could sell 1,000 units per year. The equipment would be depreciated over a 5-year period, using MACRS rates. The estimated market value of 18 the equipment at the end ofthe project's 4-year life is $500,000, webmasters, federal-plus-state tax rate is 40%. Its 19 cost of capital is 10% for average-risk projects, defined as projects with a coefficient of variation of NPV between 0.8 land 1.2. Low-risk projects are evaluated with a WACC of 8%, and high-risk projects at 13%. 21 22 a. Develop a spreadsheet model, and use it to find the project's NPV, IRR, and payback 23 24 Input Data (in thousands of dollars 25 Equipment cost 26 |Net operating working capital/Sales 27 First year sales (in units 28 Sales price per unit 29 Variable cost per unit (excl. depr 30 Nonvariable costs (excl. depr 31 Market value of equipment at Year 4 32 Tax rate 33 WACC $10,000 Key Results: 10 1,000 IRRE Payback $17.50 $1,000 $500 10% Page 1Explanation / Answer
Webmaster.com S.No. INPUT DATA (in Thousands of US Dollars) KEY RESULTS 1 Equipment cost 10,000.00 NPV= $ 3,463 2 Net Operating Working Capital / Sales 10% IRR= 21.1% 3 First year sales (in units) 1,000 PAYBACK= 2.9 4 Sales Price/ Unit $ 24.00 5 Variable Cost / Unit (Excldg Depreciation) $ 17.50 6 Non-Variable costs (Excldg Depreciation) $ 1,000.00 7 Market Value of Equipment at the end of year 4 $ 500.00 8 Tax Rate 40% 9 WACC 10% 10 Inflation in prices and costs 3% 11 Salvage Value $ 500.00 Immediate calculations: Years-----> S.No. Particulars. Formula Used 0 1 2 3 4 A. Units Sold 1,000 1,000 1,000 1,000 B. Sales Price per Unit (Excldg Dep) Sales Price* (1+Infl Rate%) $ 24.00 24.72 25.46 26.23 C. Variable cost/ Unit (Excldg Dep) Var cost*(1+Infl Rate%) $ 17.50 18.03 18.57 19.12 D. Non-Variable Costs/Excldg Dep) Non-Var Cost* (1+Infl rate%) 1,000 1,030 1,061 1,092 1 Sales Revenue (Sales Price * Unit Sold $ 24,000 $ 24,720 $ 25,462 $ 26,225 2 Required Level of Net Operating Working Capital 10% of Previous Yr sales 2400 2472 2546 2623 0 3 Basis for Depreciation Given 10,000 4 Annual Equipment Depreciation Rate (MACRS) MACRS Rate 20% 32% 19.20% 11.52% 5 Annual Depreciation Expense (Or Charge) Eqpt cost* Dep rate 2000 3200 1920 1152 6 Ending Book Value (Cost - Acc. Dep) Cost - Acc Dep 8,000 4,800 2,880 1,728 7 Salvage Value Given $ 500 8 Profit / (Loss) on Salvage Salvge Val -Ending Bk Val(yr 4) -1,228 9 Tax on profit/ (Loss) 40%*(Row 8) -491 10 Net Cash Flow due to Salvage Row 7- (-Row 10) $ 991 Cash Flow Forecast Years-----> S.No. Particulars. Formula Used 0 1 2 3 4 1 Sales Sale Price/Unit * Units Sold $ 24,000 $ 24,720 $ 25,462 $ 26,225 2 Variable Costs Var cost/Unit* Units Sold 17,500 18,025 18,566 19,123 3 Non-Variable Operating Costs Immediate Calc (Row D) 1,000 1,030 1,061 1,093 4 Depreciation on Equipment Immediate Calc (Row 5) 2,000 3,200 1,920 1,152 5 Operating Income before Taxes(EBIT) Cash Flw For (1-2-3-4) 3,500 2,465 3,915 4,858 6 Taxes on Operating Income Cash Flw For (Row 5*40%) 1,400 986 1,566 1,943 7 Non-Operating Profit after Taxes Cash Flw For (Row6-7) 2,100 1,479 2,349 2,915 8 Add: Depreciation on Equipment See Cash Flw For. (Row 4) 2,000 3,200 1,920 1,152 9 Equipment purchases Given -10,000 10 Cash Flow due to change in NOWC Immed Cal (Prev yr-Next Yr) -2400 -72 -74 -76 2,623 11 Net Cash Flow due to Salvage Immed Cal (Row 10) $ 991 12 Net Cash Flow Cash Flw For (Row 7+8+9+10+11) -12,400 4,028 4,605 4,193 7,681 Key Results : Appraisal of the Project: Net Present Value at 10% @NPV(WACC, Net Cash Flow k12:N12)+j12 $ 3,463.34 IRR @IRR(Cash Flow (E12:I12) 21.09% MIRR @MIRR(Cash Flow J12:N12, WACC,WACC) 16.99% Payback See Calculation Below Data for Payback calculation Years-----> S.No. Particulars. Formula Used 0 1 2 3 4 1 Net Cash Flow (see Cash Flow - Row 12) -12,400 4,028 4,605 4,193 7,681 2 Cumulative Cash Flow Row1 (Prev col - next col) -12,400 -8,372 -3,767 425 8,106 3 Payback period including part of year reqd IF(k61Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.