Hot Bagels makes and sells bagels for $0.79. Each bagel costs $0.19 for materials, Labor to make and sell the bagels is $8.00 per person per hour or $64 a day (8-hour workday). Fixed costs for rent, utilities, etc. average $120 per day. A minimum of two workers (one to cook and one to sell) are needed. If the demand is more than 500, they need an additional person. In fact each additional 500 units of demand require another person be added. What is the company's profit per day if they sell an average of 420 bagels a day? Base Case is given in excel file "HW4_HotBagels_BaseModel" 1. Build a one-way table to see how changing demand affects labor costs, material costs (variable costs) and profit. a) Demand range is 260-1300 b) Increment = 80 2. Build a two-way table to see influence of material cost (per unit) and demand on profit a) Demand range (260,1300). Increment 80 b) Material cost range (50.09, 50.35). Increment 0.02 3. Scenario analysis. Considering following two scenarios: MSIS 212 Assignment4 English (United States) Focus 3 - IN10 TO@@O MacBook Air a) Competitor: A competitor opens up nearby. The demand would drop to 350 bagels a day, and the price will be cut to $0.7 b) Growth: Business is good and need to expand. Hot Bagels might be selling 1200 bagels a day and decide to install some more automated cooking equipment that would increase the daily fixed cost to $135 but means each additional employee can handle up to 1500 bagels a day. Using "Scenario Manager" to set up these two scenarios and effects on Labor cost and Profits. Please change the cell names before scenario analysis to show the meaningful names in the generated table. ad table English (United States) Focus 3 = MacBook Air * o 1 Hot Bagels 3 Demand 4 Price 5 Material cost 420 $0.79 $0.19 500 7 Min. # employees 8 Bagels/day/person 9 Rate/day/person 10 employees needed 11 Fixed costs/day 2 $120,00 13 Revenue 14 Variable costs 15 Labor costs 16 Fixed costs 17 Profit/(loss) $331.80 $79.80 $128.00 $120.00 $4.00 Model MacBool Hot Bagels makes and sells bagels for $0.79. Each bagel costs $0.19 for materials, Labor to make and sell the bagels is $8.00 per person per hour or $64 a day (8-hour workday). Fixed costs for rent, utilities, etc. average $120 per day. A minimum of two workers (one to cook and one to sell) are needed. If the demand is more than 500, they need an additional person. In fact each additional 500 units of demand require another person be added. What is the company's profit per day if they sell an average of 420 bagels a day? Base Case is given in excel file "HW4_HotBagels_BaseModel" 1. Build a one-way table to see how changing demand affects labor costs, material costs (variable costs) and profit. a) Demand range is 260-1300 b) Increment = 80 2. Build a two-way table to see influence of material cost (per unit) and demand on profit a) Demand range (260,1300). Increment 80 b) Material cost range (50.09, 50.35). Increment 0.02 3. Scenario analysis. Considering following two scenarios: MSIS 212 Assignment4 English (United States) Focus 3 - IN10 TO@@O MacBook Air a) Competitor: A competitor opens up nearby. The demand would drop to 350 bagels a day, and the price will be cut to $0.7 b) Growth: Business is good and need to expand. Hot Bagels might be selling 1200 bagels a day and decide to install some more automated cooking equipment that would increase the daily fixed cost to $135 but means each additional employee can handle up to 1500 bagels a day. Using "Scenario Manager" to set up these two scenarios and effects on Labor cost and Profits. Please change the cell names before scenario analysis to show the meaningful names in the generated table. ad table English (United States) Focus 3 = MacBook Air * o 1 Hot Bagels 3 Demand 4 Price 5 Material cost 420 $0.79 $0.19 500 7 Min. # employees 8 Bagels/day/person 9 Rate/day/person 10 employees needed 11 Fixed costs/day 2 $120,00 13 Revenue 14 Variable costs 15 Labor costs 16 Fixed costs 17 Profit/(loss) $331.80 $79.80 $128.00 $120.00 $4.00 Model MacBool
