Antonio's Pizza is considering adding hot wings to their menu. Under consideration are two options - Make the wings from scratch with uncle Tony's secret sauce or Buy the wings frozen and already sauced from an outside supplier. For each of the two new options, the selling price to the customer will be $10.50 per dozen wings.
The Make option would require acquisition of a new stove top at a cost of $5,000. Cost per dozen wings are estimated as $2.00 for labor and $2.85 for ingredients.
The Buy option would require the acquisition of a fryer at a cost of $2,000. The cost to purchase the wings from an outside supplier would be $6.50 per dozen.
a. Set up spreadsheet models to calculate total profit for each option. Construct your models as demonstrated in the notes and podcasts.
Quantity sold is the decision variable - assume that every dozen made or purchased will be sold.
b. Use your models to answer the questions below (do separately for Make & Buy).
Note: only one model needs to be created for each option to complete all of these questions. Tables and graphs created for ii and iii will change when the model inputs are changed to do c. but if you have your Tables and graphs created correctly, they can be evaluated without issues.
If you are more comfortable copying the models to use for each question, make sure they are well organized and labelled clearly.
i. Use the Goal Seek function in Excel to determine the break-even quantity.
ii. Create a one-way table, using Excel's Data/What-If/ Data Table tool, to demonstrate the sensitivity of profit to changes in selling price per salad. Use selling prices of 9.50, 10.00, 10.50, 11.00 and 11.50 in your tables.
iii. Create a graph of the sensitivity results from part ii. (scatterplot)
c. Graph the total Profit lines for both options on one graph showing how their profits change over a quantity range from 0 to 2,500 units. Set price back to $10.50 per unit for each option when determining input for this graph.