You are the executive officers of Big Buns, Inc. (BBI). BBI manufactures differe

Question

You are the executive officers of Big Buns, Inc. (BBI). BBI manufactures different types of chairs and accessories. You have had several good years and have decided to expand. BBI is looking at acquiring another company as a subsidiary. As executives (financially savvy and adept in capital budgeting procedures :-)) you have narrowed your choices to two possible acquisitions; Hineys R Us, a seat cushion manufacturer, and Flaberific, a heavy duty lawn chair manufacturer. You only have $500,000 to spend, which happens to be the price tag for each of the companies. It is your job to determine which company you should acquire. BBI's cost of capital is 10%. The raw data for each company is as follows: Hineys R Us (HRU) ----------------- HRU is projected to sell 50,000 units (seat cushions) in year one at a retail price of $15 per unit with an increase of 30% per year. Variable costs consist of Cost of goods sold, estimated at 25% of sales; advertising, estimated at 10% of sales; other variable costs, estimated at 5% of sales. Fixed costs consist of labor for year one estimated at $200,000 with an increase of 10% per year; rent for year one estimated at $60,000 with an increase of 5% per year; depreciation estimated at $10,000 per year; and other fixed costs for year one estimated at $5,000 with an increase of 5% per year. HRU's tax rate is estimated at 30%. You also project that you will have to spend an additional $20,000 in year 2 and $50,000 in year 5 in capital expenditures. To be conservative you are using a discount rate 2 points above BBI's cost of capital. Flaberific (FLB) ---------------- FLB is projected to sell 40,000 units (lawn chairs) in year one at a retail price of $25 per unit with an increase of 30% per year. Variable costs consist of Cost of goods sold, estimated at 30% of sales; advertising, estimated at 15% of sales; other variable costs, estimated at 5% of sales. Fixed costs consist of labor for year one estimated at $250,000 with an increase of 10% per year; rent for year one estimated at $80,000 with an increase of 5% per year; depreciation estimated at $20,000 per year; and other fixed costs for year one estimated at $5,000 with an increase of 5% per year. FLB's tax rate is estimated at 30%. You also project that you will have to spend an additional $75,000 in year 2 and $175,000 in year 5 in capital expenditures. You consider FLB to be slightly more risky than HRU so you will use a discount rate 3 points above BBI's cost of capital. **************WHAT IS REQUIRED***************** MS Excel For both companies (HRU and FLB): 1) A 5 year projected Income Statement. 2) A 5 year projected Cash Flow. 3) Net Present Value 4) Internal Rate of Return 5) Payback Period 6) Profitability Index 7) Discounted Payback 8) Modified Internal rate of Return 9) Breakeven for year 1

Explanation / Answer

1)

2)

7)

Projected Income Statement for 5 years year HRU 1 2 3 4 5 Sale Value 750000 975000 1267500 1647750 2142075 Less Variable Cost Cost of Goods Sold 187500 243750 316875 411938 535519 Advirtising Exp. 75000 97500 126750 164775 214208 Total Variabl Cost (VC) 262500 341250 443625 576713 749726 Contribution Sale - VC 487500 633750 823875 1071038 1392349 Less Fixed Cost Labor 200000 220000 242000 266200 292820 Rent 60000 63000 66150 69458 72930 Depreciation 10000 10000 10000 10000 10000 Other 5000 5250 5513 5788 6078 Total FC 275000 298250 323663 351446 381828 Profit before tax Contribution - FC 212500 335500 500213 719592 1010521 Less Tax @ 30% 63750 100650 150064 215878 303156 PAT 148750 234850 350149 503714 707365 FLB year FLB 1 2 3 4 5 Sale Value 1000000 1300000 1690000 2197000 2856100 Less Variable Cost Cost of Goods Sold 300000 390000 507000 659100 856830 Advirtising Exp. 150000 195000 253500 329550 428415 Other 50000 65000 84500 109850 142805 Total Variabl Cost (VC) 500000 650000 845000 1098500 1428050 Contribution Sale - VC 500000 650000 845000 1098500 1428050 Less Fixed Cost Labor 250000 275000 302500 332750 366025 Rent 80000 84000 88200 92610 97241 Depreciation 20000 20000 20000 20000 20000 Other 5000 5250 5513 5788 6078 Total FC 355000 384250 416213 451148 489343 Profit before tax Contribution - FC 145000 265750 428788 647352 938707 Less Tax @ 30% 43500 79725 128636 194206 281612 PAT 101500 186025 300151 453146 657095

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

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