Exercise 1 - Business Analysis PDF

Preview

Summary

Business Analysis...

Description

MGS3100: Exercises – Introduction 1. Identifying relevant variables to include in a model

You are a manager with a pharmaceutical firm, BUGS Inc. Your boss, Kevin Dodge, has given you an analytical task. R&D scientists at BUGS have invented a new drug, code named Critter. Your task is to help with the decision of either a. Drop the product b. Go into full scale production You have made a preliminary list of things that your analysis should perhaps consider. Determine which of the following variables are relevant, which are not relevant to the decision at hand? Why? Variable

Relevant/Irrelevant

Reason

1. Cost of production facility 2. Cost of Manufacturing Equipment 3. Corporate Overhead allocation 4. Price structure for the product 5. Cost of Raw Materials 6. BUGs investment in R&D for critter 7. Worker recruitment and training costs 8. Demand forecasts for critter 9. Your salary 10. Sales commissions 11. Distribution costs 12. Cost of BUG institutional advertising

Influence diagram and break-even/cross-over analysis 2. You own a motel with a hundred rooms in Atlanta. Fixed daily cost is $1000 (includes mortgage, staff salaries, maintenance). Variable cost per room is $10 per room per day (includes extra utility cost, room cleanup, etc). You charge customers $50 per room. 1) Draw an influence diagram leading up to your profit. 2) What is the breakeven point? (That is, how many rooms must be occupied for you to breakeven?) 1

3) You have the option of subcontracting to improve room quality and the surroundings, but that would increase fixed costs to $1800, with no change to variable costs. You will, however, be able to charge $70 per room per day. At what point will you be indifferent between your current situation and the new option? (a) If the estimated demand is around 50, which option should you choose? (b) If the estimated demand is around 32, which option should you choose? 4. You manage a daycare center in Atlanta. Fixed monthly cost is $6,000 for rent and utilities, $5,000 in salaries, and $1,000 in insurance and other expenses. Also, each child that is enrolled adds about $90 per month in expenses in materials used (paint, paper, and other items). You charge $690 per month for each child. 1) How many children must be enrolled for you to break even? 2) You are considering moving your daycare to another location in a more expensive neighborhood, but that would increase rent/utilities to $10,000, salaries to $6,000, and insurance/other expenses to $2,000 per month. Variable costs would also rise to 150 per child per month. You will, however, be able to charge $1,000 per child per month. At what point will you be indifferent between your current mode of operation and the new option? 5. The fixed costs for a project are $100,000. If the Breakeven point is 500 units, what is the profit contribution per unit? 6. Influence Diagram and Spreadsheet Modeling (Vacation Budget) You want to project the total expenses for a trip to New York. There are two categories of the expenses: (1) living expenses for food and lodging and (2) vacation expenses for travel and entertainment. For planning purposes, you plan to spend $90 per day for food and $170 per day for lodging. Travel will cost $375, including airline tickets and transportation to and from both airports. Entertainment costs are estimated to be $50 per day, plus $140 for two “big evenings” on Broadway. Tentatively, you plan to have a five-day trip but if the total expenses are over the budget, then you may change the number of days for the trip. 1) Draw an influence diagram for the total expenses. 2) Build a spreadsheet model for this problem. Answer the following questions (a) What is the total expense for this 5-day trip? (b) What is the total expense if you stay in a relatively cheap hotel with daily rate of $100. (c) What is the total expense if you decide to stay two more days? (d) What is the maximum number of days you can stay if your budget is $3,000?

2

Solutions 1.

Variable 1. Cost of production facility 2. Cost of Manufacturing Equipment 3. Corporate Overhead allocation 4. Price structure for the product 5. Cost of Raw Materials 6. BUGs investment in R&D for critter 7. Worker recruitment and training costs 8. Demand forecasts for critter 9. Your salary 10. Sales commissions 11. Distribution costs 12. Cost of BUG institutional advertising

Relevant/Irrelevant Depends

Depends Irrelevant

Reason If existing facility with idle time, then not relevant. If new one to be built, it is relevant Same as above

Relevant

Not a direct cost of producing this product. Part of Revenue

Relevant

Cost of production

Irrelevant

Sunk cost

Relevant

Part of the cost structure

Relevant

Needed for revenue estimates

Irrelevant Relevant Relevant Irrelevant

Not attributable to this project Part of the cost structure Part of the cost structure Corporate Overhead. Not attributable to this project.

2. 1) Profit

Total Revenue

Total Cost

Price/Room

Total Variable Cost

Total Fixed Cost

Variable Cost/Room

Number of Rooms

3

2) Let X be the number of rooms rented Revenue = 50X FC=1000 VC=10X Total Cost = 10X + 1000 Profit = 50X – [10X +1000] = 40X – 1000 At Breakeven, Profit = 0 40X – 1000 = 0 X = 1000/40 = 25 Rooms. 3) Now we have two options with the following information Option 1 Option 2 (Current situation) (Subcontract) Price/Room 50 70 VC/Room 10 10 FC 1000 1800 Profit for Option 1 = 40X – 1000 Profit for Option 2 = 60X – 1800 At crossover point (point of indifference), the profits for both options are equal. Hence 40X – 1000 = 60X – 1800 20X = 800 X = 40 rooms. At 40 rooms, the profit is the same for both options. (a) If the demand is 50, then Profit1 = 40(50) – 1000 = 1000, and Profit2 = 60(50) – 1800 = 1200, so choose Option 2. (b) If the demand is 32, then Profit1 = 40(32) – 1000 = 280, and Profit2 = 60(32) – 1800 = 60, so choose Option 1. 4. 1) Let X be the number of children enrolled Revenue = 690X FC=12000 VC=90X Total Cost = 90X + 12000 Profit = 690X – [90X +12000] = 600X – 12000 At Breakeven, Profit = 0 600X – 12000 = 0 X = 12000/600 = 20 Children.

4

2) Now we have two options with the following information: Option 1 Option 2 (As is) (Subcontract) Price/Child 690 1000 VC/Child 90 150 FC 12000 18000 Profit for Option 1 = 600X – 12000 Profit for Option 2 = 850X – 18000 At crossover point (point of indifference), the profits of both options are equal. Hence 600X – 12000 = 850X – 18000 250X = 6000 X = 24 Children. 5. Profit contribution per unit is also known as Contribution Margin or CM Profit Contribution = Price/unit – VC/unit = FC/BE. Profit Contribution = 100,000/500 = $ 200 per unit. 6. 1)

2)

5

Spreadsheet model  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Spreadsheet model with formulas

A Vacation Budget

 Numberofdays  Foodcostperday Lodgingcostperday Entertainmentcost/day Bigeventsexpenses Travelexpenses  Foodexpenses Lodgingexpenses Entertainmentexpenses  Livingexpenses Vacationexpenses  Totalexpenses 

(a) (b) (c) (d)

B   5  $90 $170 $50 $140 $375  $450 $850 $390  $1,300 $765  $2,065 

 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

A Vacation Budget

 Numberofdays  Foodcostperday Lodgingcostperday Entertainmentcost/day Bigeventsexpenses Travelexpenses  Foodexpenses Lodgingexpenses Entertainmentexpenses  Livingexpenses Vacationexpenses  Totalexpenses 

B   5  $90 $170 $50 $140 $375  =B5*B3 =B6*B4 =B7*B3+B8  =B11+B12 =B13+B9  =B15+B16 

The total expense for the five-day trip is $2,065. If hotel daily rate is $100, the total expense will be 1,715. If the number of days is 7, then the total expense is $2,685 Using Goal Seek, Set Cell B18 to the value of 3000 by Changing Cell B3, we find that 8 days are the answer.

6...