Assignment:
1. 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).
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.
2. We are thinking of opening a food truck to make and serve fried meat pies. It costs us $5000 to rent a fryer for a year. Ingredients and labor are estimated at $2.05 per pie. Other fixed costs of running the truck amount to $400 per month. We charge an average of $5.00 per pie. We are open 365 days of the year. Each fryer can make up to 50,000 pies per year.
a. Build a spreadsheet model for the food truck shop to calculate Annual Profit, given two unknown variables: Number of fryers and Daily demand
Use Excel to calculate ALL output values
Number of fryers is a variable that dictates your capacity, daily demand is an unknown variable. The model should be designed so it will adjust correctly for any values for these variables.
Use IF function. Remember, you can't sell what is not demanded, you can't sell what you don't have the capacity to make.
Make sure you do all the appropriate calculations to adjust for a consistent time frame (annual).
b. Show the sensitivity of profit to the number of fryers and the daily demand using a two-way table.
Using Excel Data/What-If/Data Table
1, 2, 3, 4 and 5 as the number of fryers in column input
daily demand of 100, 200, 300, 400 and 500 in row input.