Assigned: 04/19/06
Due: 04/24/06 at the start of class (time will only be available in class on 4/19)
Remember: late penalties are heavy
Assignment:
Imagine that you have been asked to create the spreadsheet and graph and scenario summary report shown on the attached pages. It evaluates the accuracy of high temperature predictions for Philadelphia in January 2006. Note that formulas are required in all cells except for the basic info that cannot be calculated. Print the spreadsheet and the graph and the scenario summary report. Also, answer the question below by using the scenario summary report (answers can be handwritten on the print out of your spreadsheet or on the assignment sheet).
Turn In:
1) Prints Out of Spreadsheet and graph and scenario summary report
2) Disk
3) Answer to the following question – obtained using what if analysis using Scenarios
“The weather person wants to brag about having 80% accuracy. How high does the threshold have to be in order for accuracy for 1 day ahead forecasts to be 80%?”
Details:
1) The beginnings of the spreadsheet can be downloaded from the assignment page of my www site. This will save typing a bunch of numbers into the spreadsheet.
2) The spreadsheet should be formatted roughly as shown, including title, headings, totals, etc. A little of this can be accomplished using Simple AutoFormat, but many adjustments will be necessary. The title should be centered across the main left columns using merge and center and be Bold, 18 pt. Column headings are bold text,, and each is in exactly one cell!! Border lines should be included as shown. Top headings (Weather Prediction Accuracy, Predicted 1 Day Ahead and Predicted 2 Days Ahead) should be merged and centered.
3) Dates should be filled in as show – these should be able to be done using Auto Fill (There’s one day for which there are no predictions (1/18); that’s why I have two dates entered.
4) Put the Assumption in the bottom of the spreadsheet and format to have a light blue background and a solit boundary around it. This threshold is the number of degrees a prediction can be off and still be considered “correct” (good enough).
5) The sections for 1 day ahead predictions and 2 day ahead predictions have basically the same formulas. If you do formulas correctly, as described below, the formulas (6, 7, 8, and 9) from one should be able to be copied into the other.
6) The difference column is the difference between actual and predicted. Your formula should use mixed reference for the actual temperature – so that copying down causes the address row to be adjusted down, but copying to the other section (i.e. 1 day ahead to 2 days ahead) does not cause the address column to be adjusted. Mixed references have some $ and some no $.
7) The absolute value is the absolute value of the difference. You should be able to find a function to do this.
8) Count As Correct must be calculated using formulas. The formulas need to have an If function in them, and they must use Absolute references to refer to the Assumption at the bottom of the page. Basically, we are seeing if the amount the prediction was off from the actual temperature is within the threshold specified in the assumptions. Hint: in class when we did IF, the true and false parts had calculations; here only words are needed – put them in quotes in the formula.
9) At the bottom, calculate various summary info for both sections
· Average for each of the Difference and Absolute Value columns. Control the number of decimal places, as seen in the sample.
· Correlation for each of the two sections. There should be an Excel Function to help with this formula. You are basically checking to see if the actual and predicted move together (we’d like them too). If the range for actual values is specified with Absolute references, then the 1 day ahead formula can be copied to get the 2 days ahead formula. Control the number of decimal places, as seen in the sample.
· Number correct and incorrect. There is an Excel Function that will do the job – I’ll tell you this one – COUNTIF
· Percent correct is to be calculated using formulas. Format as a percent.
10) Put your name where it says YOUR NAME
11) Make sure that the whole spreadsheet prints on one page. Adjust under Page Setup if necessary.
12) Put the current date and time, file name and sheet name, page in the header and footer as shown – using fields so they automatically update.
13) Create the XY scatter graph from the (Actual and 1 Day Ahead Predicted High Temperatures) data as shown.
14) Make X axis, Y-axis, and Graph Title as shown – including YOUR ACTUAL NAME where the example has YOUR NAME HERE.
15) Adjust the X-axis and Y-axis as shown – so that the scales are as shown, and so minor ticks are shown outside the box.
16) Make sure that the background color on the graph is white!!!
17) Fit a line to the data plotted, and include the equation and the R-squared value on the chart. Make sure that they show up clearly (move them to a decent readable spot)
18) Create 7 scenarios for the spreadsheet using Excel’s capabilities – “0 Threshold”, “1 Threshold”, “2 Threshold”, “3 Threshold”, “4 Threshold”, “5 Threshold”, and “6 Threshold”. Use values for the threshold in scenarios as shown in the printed scenario summary report (basically reflecting the name of the scenario).
19) Create the scenario summary report.
20) Add “Autogenerated by Microsoft Excel under the direction of Your Name” to the bottom of the report (except use your actual name instead of “Your Name”). This is the only thing that you should type on this page! (you may change the addresses under “Changing Cells” and “Result Cells” to descriptive names if you like)
21) Print the spreadsheet and graph and Scenario Summary report. Make sure you turn in the answer to the question too!! (along with the file and print outs)
NOTE: Predicted and Actual temperatures are courtesy of ForecastWatch.com and Forecast Advisor.com which is a public site with some basic forecast accuracy information from ForecastWatch. Thanks to Eric Floehr for supplying the data.