Question

1) At Bradford Bed and Breakfast, the manager is looking to expand the facility. The following facts are known: Cost of the expansion $2,200,000 The Manager is paid $99,000 per year. Compensation will not change due to the expansion Property taxes will increase by $33,000 annually The expansion will add 10 rooms and it is expected all 10 will be occupied by paying guests The addition of the 10 guests each day is expected to add $800,000 of revenue annually Agarden and patio that was built last summer at a cost of $75,000 will be removed to make way for the expansion. Additional cost of staff based on 10 occupied rooms is $260,000 annually Additional cost of supplies and food based on 10 occupied rooms $80,000 annually All expenses are expected to increase 3% annually. Due to competition revenue is expected to increase only 2% annually. The useful life of the expansion is 8 years. Bradford uses a 8% discount rate. Based on this information determine the following: a. After the building is expanded, what is the incremental fixed cost incurred annually as a result of the expansion? b. What are the incremental variable expense incurred annually due to the expansion? c. What are the sunk costs of the expansion, if any? d. Create an 8 year proforma cash flow statement for the expansion project (assume there are no income taxes) e. Calculate the projects NPV and IRR. Should the project be accepted or rejected?

you should use EXCEL formula to answer the questions

Answer 1

a]

incremental fixed cost = increased property taxes = $33,000

b]

incremental variable cost = additional staff cost + addition cost of supplies = $260,000 + $80,000 = $340,000

c]

Sunk costs = cost of garden and patio + manager compensation = $75,000 + $99,000 = $174,000

d] and e]

NPV and IRR are calculated using NPV and IRR functions in Excel

NPV is $338,782.83

IRR is 11.87%

The project should be accepted because the NPV is positive and IRR is higher than the required return (discount rate)

C D E F G H I J 2,200,000 2 3 4 5 6 Cost of expansion Incremental revenue Incremental fixed cost Incremental variable cost Incremental cash flow NPV IRR 800,000 33,000 340,000 427,000 816,000 33,990 350,200 431,810 832,320 35,010 360,706 436,604 848,966 36,060 371,527 441,379 865,946 37,142 382,673 446,131 883,265 38,256 394,153 450,855 900,930 39,404 405,978 455,548 918,949 40,586 418,157 460,206 (2,200,000) 338,782.83 11.87% 8

2 5 2200000 2 Cost of expansion 3 Incremental revenue 4 Incremental fixed cost 5 Incremental variable cost 6 Incremental cash flow 7 NPV 8 IRR 800000 =C3*(1+2%) =D3*(1+2%) =E3*(1+2%) =F3*(1+2%) =G3*(1+2%) =H3*(1+2%) =13*(1+2%) 33000 =C4*(1+3%) =D4*(1+3%) =E4*(1+3%) =F4*(1+3%) =G4*(1+3%) =H4*(1+3%) =14*(1+3%) 340000 =C5*(1+3%) =D5*(1+3%) =E5*(1+3%) =F5*(1+3%) =G5*(1+3%) =H5*(1+3%) =15*(1+3%) =C3-C4-C5 =D3-D4-D5 =E3-E4-E5 =F3-F4-F5 =G3-G4-G5 =H3-H4-H5 =13-14-15 = J3-14-15 --B2 =NPV(8%,C6:J6) +B6 =IRR(B6:16)