Develop a spreadsheet analysis to:

1. Identify all price promotion scenarios based on the timing of promotions undertaken by Gulmarg and Kitz. Develop monthly demand values for each scenario. Include the scenarios where Gulmarg and Kitz both promote in October and both promote in December.

2. Evaluate ALL promotion scenarios and develop optimal production plans.

3. Based on your analysis above, when should Gulmarg promote. What additional factors will you consider while finalizing sales and operations (S&OP) plans for Gulmarg.

Please provide Scenarios and Formulas within excel.

S\&OP Challenges at Gulmarg Ski Management at Gulmarg Skis was surprised in the previous Each pair of skis used material worth $300, mostly in the form of season when a competitor, Kitz, discounted their skis by $50 expensive carbon fiber, plastic, and alloys. Carrying a pair of in October. In a market in which discounting was rare, this was skis in inventory from one month to the next cost $10. Given the an unusual move by Kitz. As a result, Gulmarg saw a seasonal nature of demand, Gulmarg started October with an significant drop in sales between October and January. The inventory of 2,000 pairs of skis and preferred to end in March company did not want to be caught unprepared for the with no inventory to carry over. Any leftover inventory at the end of upcoming season and was planning its response. Two March would require very heavy discounting to sell it. Customers alternatives being considered by Gulmarg were to promote were not willing to wait for skis, so Gulmarg lost all sales that in October or December. Gulmarg could not precisely it could not meet in a month because of insufficient predict what Kitz would do regarding promotions but felt inventory and production. Lost sales cost Gulmarg its profit that Kitz was likely to repeat its October promotion, given margin ( $430 ). Gulmarg's skis were normally priced at $800 a pair. its success in the previous year. Before making its production plans, Gulmarg had done market Gulmarg and Kitz competed in high-performance research to fully understand the impact of promotions on customer skis and sold direct to end consumers. The companies prided behavior. Dropping price from $800 to $750 attracted new customers, themselves on outstanding craftsmanship, using only the best but also resulted in existing customers shifting the timing of their materials. Both were known for the high quality of their skis purchase to take advantage of the discount. Customer behavior and the fact that customers could design their own top sheet. was also affected by actions taken by the competitor, Kitz. Although each company had a loyal following, there was a If only one of the two companies promoted in a given month, significant fraction of customers who were happy to buy skis it saw a 40 percent increase in sales for the month and a from either. It is this group that the two companies were forward movement of 20 percent of demand from each of competing for through price discounts. the three following months. In other words, If Gulmarg The sale of skis was highly seasonal, with all sales promoted in October but Kitz did not, Gulmarg observed occurring between October and March, see Table 1. a 40 percent increase in October demand and a shift of Production capacity at the manufacturing plant was 20 percent of demand from November, December, and January limited by the number of employees that Gulmarg hired. to October. Employees were paid $15/ hour for regular time and $23/ The competitor that did not promote experienced a 20 hour for overtime. Each pair of skis required 4 percent drop in sales for the promotion month and a 10 percent hours of work from an employee. drop in sales for each of the three following months. If one of The plant worked 20 days a month, 8 hours a day on regular the companies promoted in October and the other in time. Overtime was restricted to a maximum December, changes in demand were cumulative, with the of 40 hours per employee per month. October promotion having the first impact, followed by Gulmarg employed a total of 60 workers and felt that it the December promotion. In other words, demand for each could not let any of them go, even in months when demand company shifted from that provided in Table 1 based on the was below the capacity provided by 60 workers. October promotion. Given the high skill requirements, the company had The December promotion then affected the revised difficulty finding suitable people and as a result demand. For example, if Kitz promoted in October and could hire only up to a maximum of 10 temporary Gulmarg chose to promote in December, Gulmarg would observe a employees. In other words, the number of twenty percent drop in demand in October and ten percent drop employees could fluctuate between 60 and in demand in November, December, and January compared 70. Hiring each temporary employee cost $500, with the figures in Table 1 . and letting each one go cost another $800. The December promotion would then increase demand TABLE 1 Demand Forecast for Gulmarg Skis in December by 40 percent ofi the reduced amount (because of the earlier Kitz promotion). Similarly, forward buying from January would also be based on the reduced amount because of the October promotion by Kitz. Forward buying from February and March would be based on demand not affected by the October promotion by Kitz If both companies promoted in a given month, each experienced a growth of ten percent for that month and forward buying equivalent to twenty percent of demand from each of the three following months