1. After completing the Aggregate Planning Template for the data provided in the template (name the worksheet DEMO), make a copy of that worksheet (in the same excel file) and name that worksheet ASSIGNMENT, and use the following information to create another Aggregate Plan. Cost Information (given): Inventory Carrying Cost $ 12.00 per unit per month Subcontracting cost per unit $ 50.00 per unit Average pay rate $ 16.50 per hour Working hours per day 8 hours Overtime pay rate (over 8 hrs/day) $ 30.00 per hour Labor hours to produce unit 2.4 hours/unit Cost of increasing daily production rate (hiring and training) $ 500.00 per unit Cost of decreasing daily production rate (layoffs) $1,000.00 per unit Month Jul Aug Sep Oct NOV Dec Demand data (glven): Expected Demand Production Days 1200 21 1400 22 2500 21 2800 22 1600 20 1400 20 . Copy and paste the daily and average demand graph into the document (as a picture not an excel object). Interpret the graph in which months are you building Inventory and in which months you burning off inventory? Copy and paste the summary cost table into the document (as a picture not an excel object). Which plan would you suggest the operations manager use? Explain. Compare your Plan 1 tables between the DEMO and the ASSIGNMENT worksheets. What differences do you notice (aside from the actual numbers)? Focus on the Ending inventory column. How does this impact the validity of you inventory carrying costs? What is occurring? . What would the Average Demand per day need to be changed to (integer value only) so that the ending inventory in any month would not be negative? Would this change the plan that you would suggest to the Operations manager? Copy and paste the NEW summary cost table into the document (as a picture not an excel object). What would the subcontracting cost have to be increased to make the cost of Plan 2 be the same as Plan 3? Copy and paste the NEW Plan 3 table into the document (as a picture not an excel object). **You need to submit the Aggregate Planning template with the DEMO and ASSIGNMENT worksheets included. No credit for this problem if the completed Excel file is not submitted. 2. Watch Dynamic Pricing in Airline Industry and Revenue Management in the NBA videos (in the Videos section) and answer the following questions: (there is additional information about the NBA team's dynamic pricing approach on pp. 562). What is the difference between leisure and business travelers for the airlines' pricing models? How do airlines use advance purchase discounts and fare buckets in their pricing models? Explain two additional factors, in addition to advance purchase discounts and fare buckets, that impact the airline's ticket prices. Explain how the NBA's revenue management model is different from the airline industry. What is the major type of statistical technique that is used in the NBA's Dynamic Pricing Model? What are the dependent and independent variables used in their model? The video discusses a specific data analytics technique, a heat map. How is the heat map used to change ticket pricing? . 3. A major airline files between Asheville and Atlanta. The airplanes they use have a capacity to carry 90 passengers. In the past all seats were priced at $125. An average of 78 passengers are on every flight. The variable cost of a filled seat is $18. A new approach is to use a yield revenue approach with three price points. Early booked seats are priced at $90, seats booked between one and two weeks before the flight are priced at $135, and seats booked within one week are priced at $165. The estimate is that 60% of the seats will be priced at early booking rates, 25% will be priced at between one and two weeks before the flight, and the last 15% will be priced at the highest price. Variable costs will not change. What are the expected sales for a flight using the old method and using the new yield revenue approach? which approach would you suggest being implemented? Are there any concerns (e.g. non-price related factors)? You should have come up with the expected sales from the yield management approach was less than the old method. The operations manager is very interested in the yield revenue approach but want to make sure it w generate at least the same amount of revenue as the old method. The only variable that can be changed is the pricing for early booked seats. The two other prices remain unchanged. What would the pricing for early booked weats need to be increased to cause the yield revenue method to generate the same value of expected sales as the old method. Explain how you determined this value fou will need to do some cakulations) . 1. After completing the Aggregate Planning Template for the data provided in the template (name the worksheet DEMO), make a copy of that worksheet (in the same excel file) and name that worksheet ASSIGNMENT, and use the following information to create another Aggregate Plan. Cost Information (given): Inventory Carrying Cost $ 12.00 per unit per month Subcontracting cost per unit $ 50.00 per unit Average pay rate $ 16.50 per hour Working hours per day 8 hours Overtime pay rate (over 8 hrs/day) $ 30.00 per hour Labor hours to produce unit 2.4 hours/unit Cost of increasing daily production rate (hiring and training) $ 500.00 per unit Cost of decreasing daily production rate (layoffs) $1,000.00 per unit Month Jul Aug Sep Oct NOV Dec Demand data (glven): Expected Demand Production Days 1200 21 1400 22 2500 21 2800 22 1600 20 1400 20 . Copy and paste the daily and average demand graph into the document (as a picture not an excel object). Interpret the graph in which months are you building Inventory and in which months you burning off inventory? Copy and paste the summary cost table into the document (as a picture not an excel object). Which plan would you suggest the operations manager use? Explain. Compare your Plan 1 tables between the DEMO and the ASSIGNMENT worksheets. What differences do you notice (aside from the actual numbers)? Focus on the Ending inventory column. How does this impact the validity of you inventory carrying costs? What is occurring? . What would the Average Demand per day need to be changed to (integer value only) so that the ending inventory in any month would not be negative? Would this change the plan that you would suggest to the Operations manager? Copy and paste the NEW summary cost table into the document (as a picture not an excel object). What would the subcontracting cost have to be increased to make the cost of Plan 2 be the same as Plan 3? Copy and paste the NEW Plan 3 table into the document (as a picture not an excel object). **You need to submit the Aggregate Planning template with the DEMO and ASSIGNMENT worksheets included. No credit for this problem if the completed Excel file is not submitted. 2. Watch Dynamic Pricing in Airline Industry and Revenue Management in the NBA videos (in the Videos section) and answer the following questions: (there is additional information about the NBA team's dynamic pricing approach on pp. 562). What is the difference between leisure and business travelers for the airlines' pricing models? How do airlines use advance purchase discounts and fare buckets in their pricing models? Explain two additional factors, in addition to advance purchase discounts and fare buckets, that impact the airline's ticket prices. Explain how the NBA's revenue management model is different from the airline industry. What is the major type of statistical technique that is used in the NBA's Dynamic Pricing Model? What are the dependent and independent variables used in their model? The video discusses a specific data analytics technique, a heat map. How is the heat map used to change ticket pricing? . 3. A major airline files between Asheville and Atlanta. The airplanes they use have a capacity to carry 90 passengers. In the past all seats were priced at $125. An average of 78 passengers are on every flight. The variable cost of a filled seat is $18. A new approach is to use a yield revenue approach with three price points. Early booked seats are priced at $90, seats booked between one and two weeks before the flight are priced at $135, and seats booked within one week are priced at $165. The estimate is that 60% of the seats will be priced at early booking rates, 25% will be priced at between one and two weeks before the flight, and the last 15% will be priced at the highest price. Variable costs will not change. What are the expected sales for a flight using the old method and using the new yield revenue approach? which approach would you suggest being implemented? Are there any concerns (e.g. non-price related factors)? You should have come up with the expected sales from the yield management approach was less than the old method. The operations manager is very interested in the yield revenue approach but want to make sure it w generate at least the same amount of revenue as the old method. The only variable that can be changed is the pricing for early booked seats. The two other prices remain unchanged. What would the pricing for early booked weats need to be increased to cause the yield revenue method to generate the same value of expected sales as the old method. Explain how you determined this value fou will need to do some cakulations)