1



“What-if” Spreadsheet Work Plan

1. What is the goal, and what output (information) would best help meet that goal? For example, in the GPA calculator, the goal was to see how predictions on grades for courses this semester would affect the cumulative GPA; cumulative GPA was the appropriate output. In the Race Pace calculator, the goal was to see what finish time would result from a race of a certain distance run at a certain pace; the final race time was the appropriate output.

2. How will user engage with this spreadsheet? What set of assumptions constitutes a “scenario”? For example, in the GPA calculator, the assumptions that comprised a scenario were the past cumulative GPA and the number of credits along with the current set of courses and the expected grades. In the Race Pace calculator, the assumptions that comprised the scenario were the race distance (including units) and the pace of the run (in minutes per mile).

3. In what format (e.g., units) will the user prefer to see and enter information? For example, in the GPA calculator, the user will enter letter grades, not the grade point equivalent, and will see the cumulative GPA on a scale of 0-4 with two decimal places. For the Race Pace calculator, the user will enter the race distance in either miles or kilometers (user’s choice) and will enter the pace in average time (in minutes and seconds) per mile.

4. What other facts or conversion factors are needed? For example, in the GPA calculator, we needed to know the grade point equivalents for each letter grade. For the Race Pace calculator, we needed to know the number of kilometers in a mile.

5. What data are needed to calculate the outputs from step 1, and how are those calculations made? Build a data tree so that the top node includes sought output information and the bottom most node on each branch shows required data that is available to the user for input. Become operationally attuned to the process of going from inputs to outputs, before turning to Excel. For example, in the GPA calculator, you needed to know how weighted averages are calculated. For the Race Pace calculator, you needed to know how to convert distances from kilometers to miles and time from minutes and seconds (user input) to just minutes (for the calculations) and back again (for the output).

6. In Excel, create a template for displaying the outputs from step 1 and the user inputs from step 2 and 4, given the formatting required in step 3. Fill in reasonable test data as you go. Remember, you can rearrange the information at the end. Choose an organizational structure at this point that is most clear to you, the developer. For example, in the GPA calculator, we had locations for the final GPA, past credits and GPA, current course information (name, credits, grade), and letter grade to grade point mapping. For the Race Pace calculator, we had locations for the pace (minutes and seconds), the race distance including units, and the resulting race time (in hours, minutes, and seconds).

7. Looking at the bottom nodes of your data tree, perform the calculations needed to get to the next level in the data tree. Test for errors as you go. Do this in the smallest increments for simplicity and to avoid errors. For example, in the GPA calculator, we first calculated the number of grade point that was equivalent to each letter grade, and then we weighted and averaged those grade points. For the Race Pace calculator, we first converted race pace from minutes and seconds to minutes (with decimals), and then we converted the race distance to miles. That allowed us to calculate the next step, the finish time in minutes (with decimals). Our last step was to convert that finish time back to the hours, minutes, and seconds format required by the user.

8. Reformat the spreadsheet: organization, borders and shading, labeling, and protection.

9. Test for usability and accuracy.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download