|
|
|
Using Excel Goal Seek |
Chapter 11 presents information about using spreadsheets as a decision model. Goal Seek is a tool in Excel that allows you to designate a given total, then designate a given cell to change to meet that total. For more sophisticated decisions you can install the Excel feature, "Solver."
An example of goal seek is included below. If you wish a copy of the actual spreadsheet you can download it by clicking here.
The following example demonstrates the use of Goal Seek in problem solving. The numbers in this example are NOT based on a real situation. They have been created only to demonstrate the problem solving abilities of Excel's "Goal Seek." Using these numbers, we will work through a case in which the objective is to arrive at the number of FTEs (full time equivalents) for a pay period that are necessary to produce an Effectiveness Rating of 12. The factor termed "Effectiveness Rating" (10.36 with the figures in the tables below) is based on the number of each type of personnel at each level of achievement times the percentage of efficiency that each staff level produces. The percentages of efficiency were based on a time study and are seen below in table 1. An RN working at Level 1 is said to have an efficiency of 88%, that is 88% of the Level 1 RN's time can be used. The other 12% is downtime that results from the skills not yet mastered. Table 2 shows the number of FTEs currently in each category.
| |
A | B | C | D | E |
| 1 | Percentage of Time that the skills of personnel at a given level are able to be used effectively. | ||||
| 2 | Level | 1 | 2 | 3 | 4 |
| 3 | RN | 88% | 93% | 96% | 100% |
| 4 | LPN(LVN) | 70% | 72% | 74% | 75% |
| 5 | NA | 55% | 57% | 59% | 60% |
Table 1 - Percentages of Efficiency for Each Category of Personnel
| |
A | B | C | D | E | F |
| 8 | Current number of FTEs at each level | |||||
| 9 | Level | 1 | 2 | 3 | 4 | Total |
| 10 | RN | 1 | 2.5 | 2 | 1 | 6.5 |
| 11 | LPN(LVN) | 2 | 2 | 0 | 0 | 4 |
| 12 | NA | 2 | 1 | 0.5 | 0 | 3.5 |
| 13 | 5 | 5.5 | 2.5 | 1 | 14 | |
Table 2 - Number of FTEs Currently in Each of the Categories
Table 3 below calculates the actual Effectiveness Rating. To arrive at this, a relative formula was placed in each cell from B18 to E20. This formula multiplies the number of FTEs at each level by the appropriate percentage. For example the formula in Cell B18 that produced the effectiveness rating of 0.88 seen in cell B18 (below) was a result of multiplying the number of FTE's of nurses at level 1 from cell B10 (1) by the efficiency of a level one nurse from cell B3 (.88) for an effectiveness rating at that level of .88. Then the results were added together to produce the totals seen in F18 to F20 and then totaled to give the number of 10.36 seen in cell F21 (below). Your task now, is to increase the total Effectiveness Rating to 12. There are numerous solutions to this problem, some cost effective, some not. With a spreadsheet and a little effort you can try them all.
| A | B | C | D | E | F | |
| 16 | Effectiveness Ratings for the Current Staff Mix | |||||
| 17 | Level | 1 | 2 | 3 | 4 | Total |
| 18 | RN | 0.88 | 2.325 | 1.92 | 1 | 6.13 |
| 19 | LPN(LVN) | 1.4 | 1.44 | 0 | 0 | 2.84 |
| 20 | NA | 1.1 | 0 | 0.295 | 0 | 1.40 |
| 21 | Total Effectiveness Rating | 10.36 | ||||
Table 3 - The Effectiveness Rating
(NOTE: The figure 10.36 in cell F21 looks inaccurate when the 3 figures in cells F18 to F20 are added. The discrepancy is a result of rounding. The spreadsheet actually stores the numbers in a more precise manner than is seen here.)
You decide to work on the solution by changing just one of the FTE numbers, the number of level 4 RNs. You tell the cell that contains the formula that produces the Effectiveness Rating (F21) that you want a level of 12 instead of the 10.36 and would like to see the number of level 4 RNs (E10) changed to meet that level. To do this:
If you also have the spreadsheet set to give you a salary cost for an 80 hour pay period based on the FTEs, when you change the number of level 4 RNs to 2.64, using the salaries in the chart below, the salary costs for an 80 hour period go from $29,670 to $39,221.36.
| B | C | D | E | ||
| 24 | Salary per hour | ||||
| 25 | Level | 1 | 2 | 3 | 4 |
| 26 | RN | 17.00 | 18.00 | 19.00 | 20.00 |
| 27 | LPN(LVN) | 12.00 | 13.00 | 14.00 | 15.00 |
| 28 | NA | 8.50 | 9.00 | 9.50 | 10.00 |
Table 4 - Salary per hour for each level
You've been told to try and make do with people at a lower salary scale, so again you play. This time you go to the other extreme and ask to attain an Effectiveness Rating of 12 by changing the number of level 1 nursing assistants. First you change the number of RNs at level 4 back to 1. Then you use Goal Seek to obtain an Effectiveness Rating of 12 when the number of NAs at level 1 is the variable (Cell B12). You find that you need 4.98 NAs at level 1 to meet this Effectiveness Rating and the cost is $43,826.59 You can continue to experiment until you find the ideal combination of personnel and cost. Playing with these figures in an actual spreadsheet will make these concepts clearer (and more fun).
In this scenario, we have omitted the costs of benefits and operated under the assumption that the Effectiveness Rating of 12 would yield safe care. This may or may not be so. In all probability there are other factors that determine safe care which would need to be factored into the scenario. Those that can be identified and quantified can be used in computation. No formulas, of course, will ever be perfect, but neither is human judgment.
You can download the actual Excel spreadsheet to experiment with if you wish. In the actual spreadsheet, the cells A1 to F23 contain fixed amounts, that is these cells contain the numbers on which the formulas are based. The contents of cells K4 to P24 are based on formulas which access the numbers in the cells A1 to F23. Before experimenting with Goal Seek, you may find it helpful to examine the formulas.
Last Updated:March 18, 2003
For questions or broken links please email the author .
Copyright 2003/2008 Linda Q. Thede
All rights reserved