Please answer using EXCEL format

Question 1

• Year 1:
  • Sales Revenue = 4,000 units * $45/unit = $180,000
  • Variable Costs = 4,000 units * $25/unit = $100,000
  • Advertising Expenses = $70,000
  • Other Fixed Costs = $127,500
  • Net Cash Inflow = $180,000 - $100,000 - $70,000 - $127,500 = $-117,500 (negative due to initial investment)
• Year 2:
  • Sales Revenue = 7,000 units * $45/unit = $315,000
  • Variable Costs = 7,000 units * $25/unit = $175,000
  • Advertising Expenses = $70,000
  • Other Fixed Costs = $127,500
  • Net Cash Inflow = $315,000 - $175,000 - $70,000 - $127,500 = $-57,500 (negative due to initial investment)
• Year 3:
  • Sales Revenue = 10,000 units * $45/unit = $450,000
  • Variable Costs = 10,000 units * $25/unit = $250,000
  • Advertising Expenses = $50,000
  • Other Fixed Costs = $127,500
  • Net Cash Inflow = $450,000 - $250,000 - $50,000 - $127,500 = $22,500
• Year 4-12:
  • Sales Revenue = 12,000 units * $45/unit = $540,000
  • Variable Costs = 12,000 units * $25/unit = $300,000
  • Advertising Expenses = $40,000
  • Other Fixed Costs = $127,500
  • Net Cash Inflow = $540,000 - $300,000 - $40,000 - $127,500 = $72,500

Question 2

To determine the net present value (NPV) of the proposed investment, one needs to discount the net cash inflows calculated in the previous step back to their present value, considering the time value of money and then subtract the initial investment. Given that the required rate of return (discount rate) is 20%, discount each year's net cash inflow back to present value using the formula for present value of annuity:

PV= PMT * [(1-(1+r)^-n)]/r

Where:

PMT = Net cash inflow for the year

r = Discount rate (required rate of return)

n = Number of years

The Calculations yielded the following figures:

Year 1 = 0.1667=19,675

Year 2 = 727,250

Year 3 = 1,207,425

Year 4 = 505,380

Calculate the NPV by summing up the present values of all cash flows and subtracting the initial investment:

NPV=19,675727,250+1,207,425+505,380100,000

NPV=966,880NPV=966,880

Since the NPV is positive ($966,880), the proposed investment in the smoke detector is profitable and would generate a positive return. Therefore, I would recommend that Englewood Company accept the smoke detector as a new product.