Question: 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.
I need to know how to set up the spreadsheet model for the food truck, and where the number of fryers and demand fall within that. As well as the IF Function for those two variables.