Every year my snow removal company sends me a contract for their services for the coming winter season (November through March). The contract includes two options for the service: a fixed price contract where I pay a set fee each month regardless of the number of snowfall events, and a variable cost contract where I pay for service only when there is a snowfall event. Using historical data from Minnesota Department of Natural Resources on snowfall in the Twin Cities recommendation of which service (fixed or variable) you recommend. The fixed cost option has a monthly fee of $180 per month over the five months of service. The snow removal company will come out and provide their service for any event of greater than two inches. Alternatively, with the variable cost option a customer would only pay when the company provides their service. The service rate is tiered based on the amount of snowfall as follows: The snowfall tables provide the historical number of days per month for each minimum threshold of snowfall. Please note that the data need to be cleaned so as not to double count snowfall events from the DNR data. For example, a snowfall event of 7" would be counted by the DNR as an event of 2 or more, 4 " or more, and 6 or more. Using these data sources, be sure to count only the snowfall events within the given ranges. Using the historical snowfall event data, along with the service costs, please evaluate the two service options using Monte Carlo Simulation. Please note that there are a few methods for building the discrete distribution from which to sample. Using the results from the simulation, make a recommendation on which service to contract. How often would the recommended service cost less than the alternative? What is the range of cost (worst case and best case) for the two services? How might our assumptions impact the results of the model? What additional factors should be considered? Criteria Your group should submit both an Executive Memo (i.e., Managerial Report) and the completed Excel file. The case study will be graded using the Case Study rubric. The report should be submitted as a PDF and should not exceed 3 pages (excluding exhibits and visual aids). If you created your report using Microsoft Word, please submit both the PDF version and the .doc/.docx version. If you used a different word processing software, submitting just the PDF is fine. Every year my snow removal company sends me a contract for their services for the coming winter season (November through March). The contract includes two options for the service: a fixed price contract where I pay a set fee each month regardless of the number of snowfall events, and a variable cost contract where I pay for service only when there is a snowfall event. Using historical data from Minnesota Department of Natural Resources on snowfall in the Twin Cities recommendation of which service (fixed or variable) you recommend. The fixed cost option has a monthly fee of $180 per month over the five months of service. The snow removal company will come out and provide their service for any event of greater than two inches. Alternatively, with the variable cost option a customer would only pay when the company provides their service. The service rate is tiered based on the amount of snowfall as follows: The snowfall tables provide the historical number of days per month for each minimum threshold of snowfall. Please note that the data need to be cleaned so as not to double count snowfall events from the DNR data. For example, a snowfall event of 7" would be counted by the DNR as an event of 2 or more, 4 " or more, and 6 or more. Using these data sources, be sure to count only the snowfall events within the given ranges. Using the historical snowfall event data, along with the service costs, please evaluate the two service options using Monte Carlo Simulation. Please note that there are a few methods for building the discrete distribution from which to sample. Using the results from the simulation, make a recommendation on which service to contract. How often would the recommended service cost less than the alternative? What is the range of cost (worst case and best case) for the two services? How might our assumptions impact the results of the model? What additional factors should be considered? Criteria Your group should submit both an Executive Memo (i.e., Managerial Report) and the completed Excel file. The case study will be graded using the Case Study rubric. The report should be submitted as a PDF and should not exceed 3 pages (excluding exhibits and visual aids). If you created your report using Microsoft Word, please submit both the PDF version and the .doc/.docx version. If you used a different word processing software, submitting just the PDF is fine
