CaseProblem1SnowRemovalContract-DataAnalytics.docx
Case Problem: Snow Removal Contract
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 (Links to an external site.)
, develop a model that evaluates the two contracts and make a 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. Alternatively, the most recent
daily data (Links to an external site.)
can be sourced directly from the Applied Climate Information System (where the DNR aggregate their data from). Using data from either source, 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?
For this assignment, submit both a Managerial Report and the completed Excel file.
The case study will be graded using the Case Study rubric.
Rubric
Case Study Rubric More Detailed
|
Case Study Rubric More Detailed
|
|
Criteria
|
Ratings
|
Pts
|
|
This criterion is linked to a Learning OutcomeIntroduction
The introduction clearly states the purpose of the memo. It should present the main request, recommendation or conclusion, and then summarizes then previews the main facts, arguments and evidence.
|
|
20 to >15.0 pts
Above Standards
1-2 paragraphs in length. Covers all of the main high level points you will be discussing in more detail in the body. Covers the overall problem statement of the case and the key metrics used in the analysis.
|
15 to >10.0 pts
Meets Standards
1-2 paragraphs in length. Covers some of the main high level points you will be discussing in more detail in the body. Covers the overall problem statement of the case and the key metrics used in the analysis.
|
10 to >5.0 pts
Approaching Standards
Over 2 paragraphs in length. Covers some of the main high level points you will be discussing in the case. Covers the overall problem statement of the case but does not include the key metrics used to analyze the case.
|
5 to >0 pts
Below Standards
More than 2 paragraphs in length. Does not cover all of the main high level points of the case. Does not include the overall problem statement of the case and the key metrics used in the analysis.
|
|
20 pts
|
|
This criterion is linked to a Learning OutcomeBody
The body of the memo is where you should describe the problem and your approach to solving it. Make sure your body focuses on the most important information that the decision-maker needs.
|
|
20 to >15.0 pts
Above Standards
Includes 3-5 salient takeaways from the case. Covers all questions asked in the case. Includes data/statistics and visual support for each takeaway. 1-2 paragraphs per salient takeaway. Listed in order of impact, greatest to least.
|
15 to >10.0 pts
Meets Standards
Includes 3-5 salient takeaways from the case. Covers all questions asked in the case. Includes data/statistics and visual support for at least one of the takeaways. 1-2 paragraphs per salient takeaway. Listed in order of impact, greatest to least.
|
10 to >5.0 pts
Approaching Standards
Includes 3-5 salient takeaways from the case. Covers some of the questions asked in the case. Includes data/statistics and visual support for at least 1 takeaway. 1-2 paragraphs per salient takeaway. Not listed in order of impact, greatest to least.
|
5 to >0 pts
Below Standards
Does not includes 3-5 salient takeaways from the case. Covers none of the questions asked in the case. Does not includes data/statistics and visual support for each takeaway.
|
|
20 pts
|
|
This criterion is linked to a Learning OutcomeRecommendations
The recommendation of the memo should discuss the recommendations and outline the next steps.
|
|
20 to >15.0 pts
Above Standards
1-2 paragraphs in length. Focuses on key points from your body. Includes impact to key metrics for each recommendation. Recommendations listed in order of significance. Does not introduce new concepts not covered in body.
|
15 to >10.0 pts
Meets Standards
1-2 paragraphs in length. Focuses on key points from your body. Does not include impact to key metrics for each recommendation. Recommendations are not listed in order of significance. Does not introduce new concepts not covered in body.
|
10 to >5.0 pts
Approaching Standards
More than 2 paragraphs in length. Focuses on key points from your body. Does not include impact to key metrics for each recommendation. Recommendations are not listed in order of significance. Does not introduce new concepts not covered in body.
|
5 to >0 pts
Below Standards
More than 2 paragraphs in length. Does not focuses on key points from your body. Does not include impact to key metrics for each recommendation. Recommendations are not listed in order of significance. Introduces new concepts not covered in body.
|
|
20 pts
|
|
This criterion is linked to a Learning OutcomeVisual Aids
Accuracy, clarity and necessity of the visual aids, including tables, graphs, exhibits, and appendices.
|
|
20 to >15.0 pts
Above Standards
Visuals are well thought out and meaningful to the case. All visuals include axis titles. Visuals utilize the Tufte principles. Visuals convey key metrics from the case. Visuals are professional looking.
|
15 to >10.0 pts
Meets Standards
Visuals are thought out and meaningful to the case, but lack some depth. All visuals include axis titles. Visuals utilize the Tufte principles. Some, but not all visuals convey key metrics from the case. Visuals are professional looking.
|
10 to >5.0 pts
Approaching Standards
Visuals are thought out and meaningful to the case, but lack depth. Some visuals are missing axis titles. Visuals do not follow at least one of the Tufte principles. Some, but not all visuals convey key metrics from the case. Visuals lack a finished, professional looking appearance.
|
5 to >0 pts
Below Standards
Visuals are not well thought out or meaningful to the case (or missing entirely). Visuals do not include axis titles. Visuals do not utilize the Tufte principles. Visuals do not convey key metrics from the case. Visuals are not professional looking.
|
|
20 pts
|
|
This criterion is linked to a Learning OutcomeExcel Model
I will grade the accuracy and appearance of the worksheets.
|
|
20 to >15.0 pts
Above Standards
Excel File is easy to navigate. It has all of the visuals used in the memo included in the file. All of the calculations within the Excel file are done correctly. File includes all formulas - no hard coded numbers or pasted values
|
15 to >10.0 pts
Meets Standards
Excel File is somewhat easy to navigate. It has most of the visuals used in the memo included in the file. All of the calculations within the Excel file are done correctly. File includes all formulas - no hard coded numbers or pasted values
|
10 to >5.0 pts
Approaching Standards
Excel File is somewhat to navigate. It has few of the visuals used in the memo included in the file. Most of the calculations within the Excel file are done correctly. File includes all formulas - no hard coded numbers or pasted values
|
5 to >0 pts
Below Standards
Excel File is hard to navigate. It has few or none oof the visuals used in the memo included in the file. Many calculations within the Excel file are done incorrectly. File does not include all formulas - has hard coded numbers or pasted values
|
|
20 pts
|
|
Total Points: 100
|
Fredablyee
