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