Make or Buy Analysis

Make or Buy Analysis

You have decided
to get into the business of selling gourmet brownies. The brownies you will
sell can either be made for you, by contracting with a commercial bakery (the
buy alternative) OR you can make them yourself (the make alternative). You
decide to build a spreadsheet that will allow you to enter the all the relevant
data to help you analyze which is the better option.

Make Option
Cost Components
– with this alternative you will buy the ingredients to make the brownies, hire
workers to perform the baking tasks and purchase baking equipment. What follows
are how to compute these 3 cost components:
a) Ingredients – it takes 10 oz. of
ingredients to make one brownie. The following table shows the cost of the
ingredients that are used to make the brownies:

Quantity in
oz.

Cost per oz.

1 – 100,000

.0135

100,001 – 150,000

.0129

150,001 – 200,000

.0125

200,001 – 300,000

.0117

300,001+

.0110

b) Labor – it takes 30 seconds of labor to make 1 brownie. Assume labor costs are $12.50/hr.

c) Equipment – you determine that
you need to purchase an oven and a mixer. An oven costs $13,000 and the mixer
costs $17,000. You will take out a loan to pay for both pieces of equipment and
pay it off in one year at an interest rate of 8.5%. You will make monthly
payments (=12 payments). Regardless of how many brownies you are making, add
the cost of one month of loan payments.

Buy Option
Cost Components
– with this alternative you will contract a commercial bakery to make the
brownies for you. There are two cost components.
a) Purchase
– the bakery will charge you
·
$0.25 per brownie for the first 10,000
brownies you purchase
·
$0.21
per brownie for each brownie over 10,000 purchased
For example, if
you purchased 13,000
brownies you would pay
(.25 * 10,000) + (.21
* 3,000)

b) Administration – $.0.03 per brownie but a minimum of $1,000.
That is, the smallest amount of money you will spend on administration is $1,000.

c) Warehousing/Shipping– you will
have to pay for storing the brownies after they are made by the commercial
bakery. The rate is .015 per brownie if you are purchasing over 20,000
brownies. Otherwise the rate is .025 per brownie.

You are unsure
as to how many brownies you think you will be selling. In order to see what the
costs will be for different amounts, display the costs for the following
quantities: 5,000, 15,000, 25,000, and 50,000.
(Hint: your
spreadsheet should have a row for each of the 4 quantities).

For each
quantity, create formulas that calculate each component cost.

Be sure to display the total cost for the Make and the Buy alternatives.
The last column should say either Make or Buy
depending on which alternative is lower in cost for that quantity of
brownies.
Also, at the bottom of the total Make and the total Buy
costs, display the averages of the 4 quantities.

In addition be
sure:
a) that you have
an assumption section of the spreadsheet that isolates the constants of the
problem. For example the labor time it takes to make a brownie.

b) follow the
Stillman Spreadsheet Guidelines for all format related requirements