Spreadsheet 3 You have been hired as a financial consultant by BUBBA corporation

Question

Spreadsheet 3 You have been hired as a financial consultant by BUBBA corporation. BUBBA is considering investing in a new machine to produce dog biscuits NESTMENTS BUBBA has provided you with the following information: Full price of machine is $150,000 There is no increase in net working capital. BUBBA has a 40% marginal tax rate. The machine falls into the MACRS 3-year class (33%, 45%, 15%, and 7% depreciation rates) BBA will use the machine for 6 years and then plans to sell it for $20,000 at the end of year 6 The machine is expected to increase earnings before depreciation by $35,000 a year for the life of the machine BUBBA has a WACC of 12%. Create a MS-Excel spreadsheet to calculate the NPV similar to the Cash Flow Estimation spreadsheet 40 points). This sheet should include cash flows in years 4, 5 and 6. The spreadsheet will be used by the BUBBA managers to assist them in making the investment decision. The spreadsheet should be set up to allow for a sensitivity analysis to be conducted. The cells to input the full price, increased earnings, sale price in year 6, and WACC should be easily identified and allow the BUBBA managers to change their values. Your sheet should be set up so that if the assumptions change your sheet updates appropriately. On a separate sheet in the same file, explain whether or not you would recommend purchase of the

Explanation / Answer

cost of machine

150000

Year

cost of machine

MACRS rate

annual depreciation

1

150000

33%

49500

2

150000

45%

67500

3

150000

15%

22500

4

150000

7%

10500

Year

earning before depreciation

less depreciation

after depreciation earning

less tax40%

after tax earning

add depreciation

earning after tax before depreciation

present value of earning after tax before depreciation = earning/(1+r)^n r= 12%

0

-150000

1

35000

49500

-14500

-5800

-8700

49500

40800

36428.57

2

35000

67500

-32500

-13000

-19500

67500

48000

38265.31

3

35000

22500

12500

5000

7500

22500

30000

21353.41

4

35000

10500

24500

9800

14700

10500

25200

16015.06

5

35000

35000

14000

21000

0

21000

11915.96

6

35000

35000

14000

21000

0

33000

16718.83

Net present value

-9302.87

scrap value of machine

20000

No machine should not be purchased as it results in negative NPV

less tax 40%

8000

net sale proceed after tax

12000

earning after tax before depreciation=(21000+0)

21000

earning in year 6

33000

cost of machine

150000

Year

cost of machine

MACRS rate

annual depreciation

1

150000

33%

49500

2

150000

45%

67500

3

150000

15%

22500

4

150000

7%

10500

Year

earning before depreciation

less depreciation

after depreciation earning

less tax40%

after tax earning

add depreciation

earning after tax before depreciation

present value of earning after tax before depreciation = earning/(1+r)^n r= 12%

0

-150000

1

35000

49500

-14500

-5800

-8700

49500

40800

36428.57

2

35000

67500

-32500

-13000

-19500

67500

48000

38265.31

3

35000

22500

12500

5000

7500

22500

30000

21353.41

4

35000

10500

24500

9800

14700

10500

25200

16015.06

5

35000

35000

14000

21000

0

21000

11915.96

6

35000

35000

14000

21000

0

33000

16718.83

Net present value

-9302.87

scrap value of machine

20000

No machine should not be purchased as it results in negative NPV

less tax 40%

8000

net sale proceed after tax

12000

earning after tax before depreciation=(21000+0)

21000

earning in year 6

33000

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.