Editing a Data Set

AHTQMan-Hour Forecaster

9d

123456 UNASSIGNED MAC 19 $343,201 .00 1/1196 6

. . . . ............ . .... . 654321 UNASSIGNED MIA 10... ^{. . . . . . . . . . ..}$47,820.00 !1.19

ABCDEF UNASSIGNED MIC 19 $271,387.00 1/1/96 6 2 FEDCB, UNASSIGNED MAC 7 $159,177.00 10!1/96 10

..................... . .. .. ;

Figure 11 The window displayed by clicking the Edit Data Set button in the Manage Data Sets window .

The Edit Data Sets window is shown in Figure 11 . This window displays all the job records currently in the data set in a tabular format . Each row of the table, otherwise known as a job record, represents a job in the data set . If there are too many records in the data set to fit in the window, a scroll bar will appear to give access to those records which are not visible .

This window allows you to edit the information in a data set by changing or deleting an existing job records or adding new job records. The procedures for performing these tasks are outlined below.

AHTD Man-Hour Forecaster Users Manual

Editing a Data Set (continued)

Editing a Job Record

To edit the information contained in an existing job record, perform the following steps :

1 . Use the scroll bars (if visible) to position the record you wish to edit in the display area.

1. Click on the field in the job record you wish to edit . This places the cursor in that field.

2. Use the keyboard to edit the information in the field .

3. Click on another record to cause the changes to be saved to the database. If you have made an error in the format of the data, the application will notify you . You must correct the error to continue.

Deleting a Job Record

To delete an existing job record from the data set, perform the following steps :

1. Use the scroll bars (if visible) to position the record you wish to delete in the display area.

2. Click on any field in the record you wish to delete . Then, click on the arrow in the leftmost column. The entire record you wish to delete should be highlighted, and a flashing cursor should be in one of the fields of the record .

3. If you are certain you wish to delete the highlighted record, press the Delete key on your keyboard. The record will be permanently removed from the data set.

Adding a New Job Record

To add a new job record to the data set, perform the following steps :

I . Use the scroll bars (if visible) to view the last record in the data set . Below the last record is an empty record with an asterisk (*). Click on a field in the empty record and begin typing a value to add a record .

1. Fill in the remaining fields in the new record. To move between fields, you can use either the mouse to click on the destination field or the keyboard arrow keys to position the cursor in the destination field.

2. After you have filled each field in the new job record, click on another record (even the empty one) to save the new record to the database. If you have made an error in the format of the data, the application will notify you . You must correct the error to continue.

AHTD Man-Hour Forecaster User's Manual

Editing a Data Set (continued)

AHTD Man-Hour Forecas er

Figure 12 The window displayed by clicking the Done button in the Edit Data Set window .

When you have completed editing the data set, click the Done button . The window in Figure 12 will appear .

If you wish to change the name or description of the data set you are editing, type the new information into the appropriate boxes . To change the text in a box, click in the box and use the keyboard to type the new text. You can not change the Created and Last Modified fields, for these are updated automatically .

Click on the Done button to continue . This returns you to the Manage Data Sets window .

Deleting a Data Set

AHTD Man-Hour Forecaster,

Figure 13 The warning message displayed by clicking the Delete Data Set button in the Manage Data Sets window.

The following steps enable you to delete a data set :

1. In the main window, click on the Manage Data Sets button. This displays the Manage Data Sets window (Figure 6).

2. In the Manage Data Sets window, highlight the name of the data set you wish to delete in the list of available data sets by clicking on its name .

3. Click the Delete Data Set button. The window in Figure 13 will appear.

4. If you are certain you wish to delete the highlighted data set click on the Yes button . You can cancel the operation by clicking on the No button.

Forecasting Labor Needs :AHTDMan4JorForecaster :

Figure 14 The window displayed by clicking the Forecast Labor Needs button in the main window.

To forecast the labor needs of a data set, perform the following steps :

1. Create a data set which contains jobs over which you wish to forecast . See Creating a New Data Set for more information. In the main window, click on the Forecast Labor Needs button . The window in Figure 14 will appear.

3 . Select the data set over which to forecast man-hour usage . To select a data set, click

on its name in the list of available data sets . Specify the months you wish to include in the forecast of man-hour usage. You can change the month values by one of the two following ways: either you can click on the up and down arrows next to the month, or you can click in the box you wish to change and use the up and down arrow keys on the keyboard to change the values in

the box.

AHTD Man-Hour Forecaster User's Manual 1 9

Forecasting Labor Needs (continued)

AHT©Wif-Hour F'arecasti '

.

Figure 15 The window displayed by clicking the Forecast Labor Needs button in the Forecast Labor Needs window

5 . Click on the Forecast Labor Needs button to begin distributing the forecasted man-hours over the specified time period. The window in Figure 15 will appear .

1. When the AHTD Man-Hour Forecaster has completed distributing the forecasted man-hours, click on the Done button . This returns you to the Forecast Labor Needs window (Figure 14).

2. You can then click on the Done button to return to the main window, or you can continue to forecast the labor needs of other data sets using the above procedure .

Generating Reports

Figure 16 The window displayed by clicking the Generate Reports button in the main window .

After creating a data set and forecasting its labor needs, you can generate reports on its labor needs. The window shown in Figure 16 enables you to choose a reporting option and a data set on which to report.

.

To print a report, perform the following steps

1. Create a data set which contains jobs over which you wish to forecast . See Creating a New Data Set for more information.

2. Forecast the labor needs of this data set . See Forecasting Labor Needs for more information. In the main window, click on the Generate Reports button . The window in Figure 16 will appear.

AHTD Man-Hour Forecaster User's Manual

Generating Reports (Continued)

4. Select the report you wish to run by clicking on the circle next to the name of the report.

1. Select the data set on which you wish to generate a report by clicking on its name in the list.

2. Press the Print Report button. The window in Figure 17 will appear. This operation can take from a few seconds to several minutes both before and after

Figure 17 The window displayed by clicking the Print Report button in the window appears, the Generate Reports window. _{depending on the} complexity of the report and the number of records in the database.

1. Use the scroll bars and page selection buttons (the arrow buttons in the lower left corner of the window) to review the document to be certain you are satisfied with the results. If you are not, click on the close button and correct any problems by editing the data set and forecasting its labor needs again .

2. Click the Print button (the button with the printer icon) to send the document to the printer.

Other Problems

If you have a problem you are unable to resolve using the above table, you can contact the developers of the AHTD Man-Hour Forecaster, Dr . Ron Skeith and Jerry Bullard .

At the time of this writing, Dr. Skeith is Head of Computer Systems Engineering at the University of Arkansas at Fayetteville, and Mr . Bullard is a graduate student in that department. To reach Dr. Skeith or Mr. Bullard, contact the departmental office at 313 Engineering Hall, Fayetteville, Arkansas, 72701, (501) 575-6036 .

In order to forecast the labor needs of jobs, a set of man-hour prediction equations were developed and included in the AHTD Man-Hour Forecaster software. These equations relate the total cost of a job to its required inspection man-hours based on the functional classification and type of improvement of the job .

When you build a data set, the AHTD Man-Hour Forecaster forecasts the total man-hours for each job in the data set . First, it selects an appropriate man-hour prediction equation based on the job's type of improvement and functional classification . The total cost of the job is then entered into the equation to produce the forecasted man-hours .

When developing the man-hour prediction equations, it became necessary to categorize jobs by their functional classification . A simple categorization was chosen based on the

functional classification as shown in the following table :

The default equations were developed using the procedure shown in Figure 19 . To create new equations and use them in the AHTD Man-Hour Forecaster, perform the following steps:

I~NTO1.DATJ OFDATJ O DATJ MIAATJ CAL AT4 MACOI.DAT MIC01 DAT LOC H DATI

COL99.DAT MAC99.DAT MIC99.OAT LOC99.DAT ETC99.DAT~

I

Figure 18 The procedure to find the man-hour prediction equations.

1. Create a data file, referred to as a data set, which contains the total cost, total inspection man-hours, functional classification, type of improvement, and completion time for a large number of completed jobs . This data file can be of any format you choose, but it must be readable by your statistical software package .

2. Partition this large data set into separate data sets for each functional category, also keeping the original data set for further use . (Refer to Figure 18 for an illustration of this process.)

3. Further partition the data sets for each functional category into data sets for each combination of functional classification and type of improvement .

4. Compare the total cost of the jobs to their total inspection man-hour usage for each data set, including the original (large) data set and the various functional category data sets. Use linear regression to determine the mathematical relationship for each data set

AHTD Man-Hour Forecaster User's Manual

between the total cost and length (in months) of the jobs . This step requires use of a statistical analysis software package, which is not provided .

1. Accept or reject the equations produced by the linear regressions for each data set based on the strength of the correlation (r-squared value) between the total cost and length (in months) of jobs.

2. The new job length prediction equations are the accepted equations for the data sets, the accepted equations from the functional category data sets, and the overall default equation from the original (large) data set.

To update the AHTD Man-Hour Forecaster to use these equations, perform the following steps:

1. Using Microsoft Access or other program which reads and writes Access 2 .0 databases, open the forecast.mdb database, found in the installation directory of the AHTD Man-Hour Forecaster. The prediction equations in the database are separated into two tables, thefuncat equatstable and thefuncat typwrk equatstable. The

funcat typwrk equats table contains the equations from the data sets which were partitioned according to functional category and type of improvement. The

funcat equats table contains the equations from the data sets partitioned only by functional category and serve as defaults for each functional category when an appropriate equation can not be found in thefuncat_typwrk equatstable. The funcat equatstable also contains the default equation ncat=DEF) for the original (large) data set above.

2. Add, delete, or edit records in these tables to reflect the new equation coefficients . Note: You must have a default (DEF) equation in thefuncat_equatstable, which is the equation for the original (large) data set above .

Once the AHTD Man-Hour Forecaster has estimated the man-hours required to inspect a job, it must then distribute those man-hours over the months the job is active. However,

in most cases, the job records in the data files provided by the computer center of the

AHTD have a start date but not a completion date . That is why the ability to estimate the

length of a job in months was integrated into the AHTD Man-Hour Forecaster and is

automatic and transparent (see the Notes in Appendix G for more information) .

The method used to forecast the length of a job is similar to the method employed to

forecast labor needs (see Appendix B) . Based on the job's functional category, a

prediction equation is selected. The total cost of the job is then entered into the equation

to produce the forecasted length .

Note: Forecasting the job length is the weakest link in the AHTD Man-Hour Forecaster chain. The correlation between the cost of a job and the length of a job was not as strong as was found when producing the man-hour prediction equations, especially for very large jobs. Therefore, it is imperative the job lengths be checked carefully by an experienced engineer.

The default equations were developed using the procedure shown in Figure 19 . To create new equations and use them in the AHTD Man-Hour Forecaster, perform the following steps:

Figure 19 The procedure to find job length prediction equations .

1. Create a data file, referred to as a data set, which contains the total cost, total man-hours, functional classification, type of improvement, and completion time for a large number of completed jobs. (See Appendix B for more information on this data set.)

2. Partition this large data set into separate data sets for each functional category, also keeping the original data set for further use .

3. For each data set compare the total cost of jobs to their length (in months), including the original (large) data set. Use linear regression to determine the mathematical relationship for each data set between the total cost and length of the jobs .

4. Accept or reject the equations for the functional category data sets, produced by the linear regressions, based on the strength of the correlation (r-squared value) between the total cost and length of a job .

5. The accepted equations for the functional categories and the default equations from the original data set are the new job length prediction equations . To update the AHTD Man-Hour Forecaster to use these equations, open the length equats table in the forecast.mdb database, found in the installation directory of the AHTD Man-Hour Forecaster. Add, delete, or edit records to reflect the new equation coefficients . Note: You must have a default (DEF) equation, which is the equation for the original (large) data set above.

AHTD Man-Hour Forecaster User's Manual

The seasonal distribution is used by the AHTD Man-Hour Forecaster to spread the predicted man-hours throughout the year, taking into consideration the seasonal variances in the labor usage of the Arkansas Highway and Transportation Department. Analysis of historical man-hour usage data for the AHTD showed a correlation between the time of year and the total inspection man-hours which were performed across the state .

Figure 20 shows the historical man-hour distribution which was used to determine the default seasonal distribution . Since the distribution used was an average over several years, and since the relative distribution is not likely to change significantly, the default seasonal distribution should serve the AHTD well for the foreseeable future . However, if the seasonal variances in the labor distribution which are forecasted by the AHTD Man-Hour Forecaster are consistently incorrect, you need to examine the current seasonal

Figure 20 AHTD Historical man-hour distribution by month for the years 1988 -1992.

trends in labor usage and make the necessary changes to the seasonal distribution using the following procedure.

To update the seasonal distribution, perform the following steps :

1. In the main window, click on Setup Seasonal Distribution in the menu . The window shown in Figure 16 will appear . This window will allow you to edit the relative man-hour usage for each month .

2. Make any changes necessary to the relative monthly distribution values. Since these values are relative, there is no unit

_{AHTD Man-Hour Forecaster:} associated with these values . The only restriction on the values is that they must be positive real numbers between zero and 100.

3. Click on the Done button to save the changes to the database.

Note: Changing these values will not affect the data sets already in the database unless you forecast their labor needs again.

Figure 21 The window displayed by selecting Seasonal Distribution from the Setup menu of the main window.

AHTD Man-Hour Forecaster User's Manual 3 1

The underlying trend is used by the AHTD Man-Hour Forecaster to distribute labor usage during the construction time of a job. Analysis of the historical man-hour usage by the AHTD showed a distinct correlation between the fraction of total job completion time and the relative man-hour usage.

The normal distribution approximates this relationship, which can be seen in Figure 22 . The AHTD Man-Hour Forecaster requires only the following two values to fully and

Underlying Trend
AHTD Historical Man-hour Distribution

-10 0 10203040 Twenty-fourth of Job Completion Time

Figure 22 The AHTD's historical underlying trend in labor usage during construction of jobs .

uniquely specify the normal distribution to be used to distribute the forecasted man-hours : the mean, .t (mu), and the standard deviation, 6 (sigma). Mu is the mean or the center of the distribution, around which the man-hours will be symmetrically distributed . Sigma is the standard deviation of the distribution and specifies the "width" of the distribution, or how widely the man-hours will be distributed . The mu and sigma values are specified in

AHTD Man-Hour Forecaster User's Manual 32

twenty-fourths of the total completion time of a job (where the start date is zero, and the completion date is 24).

For example, the default values for mu and sigma are 12.0722 and 8.35458, respectively. This specifies a distribution centered at almost exactly 12/24 or 1/2 the completion time. The width of the normal distribution is actually infinite, but 67% of the man-hours will fall within one standard deviation (12.0722 ± 8.35458).

For more information on the normal distribution, see a textbook on statistics .

To update the underlying trend, perform the following steps .

1 . In the main window, select Setup Underlying Trend from the menu. The window in Figure 23 will appear.

2. Enter in their respective boxes the values for the mu and sigma of the normal distribution which represents the distribution of man-hours over the completion time of a job. The values

Figure 23 The window displayed by selecting are in twenty-fourths of totalUnderlying Trend from the Setup menu of the main _{completion time. See the above} window.

explanation for more details.

3. Click on the Done button to save your changes to the database.

Note: Changes to these values affect only those data sets over which you forecast labor needs. That is, changing these valueswill notaffect data sets whose hours have already been forecasted. You can re-run the forecast for existing data sets to redistribute their forecasted man-hours.

AHTD Man-Hour Forecaster User's Manual

Since forecasting the man-hour usage of a job depends heavily on the cost of the job, the default man-hour prediction equations could quickly become out of date in an era of inflation. The inflation factor is a feature of the AHTD Man-Hour Forecaster which attempts to counteract the inflationary forces, thereby reducing the frequency with which you will need to update the man-hour prediction equations .

The inflation factor attempts to compensate for the difference in the value of a dollar at the time the equations were last updated to its value at the time of forecasting . For example, if the inflation rate last year was five percent, and the equations were last updated before last year, then you could expect the man-hour predictions to be off by about five percent.

To use the inflation factor, perform the following steps :

1. In the main window, select Setup Inflation Factor from the menu . The window in Figure 24 will .appear.

2. Calculate the inflation factor . You can calculate the inflation factor by averaging the annual inflation rates over the years since the man-hour prediction equations were last updated (Appendix B). For example, if the equations were updated four years ago,

and the annual inflation rate has been . . . . . . . . . between three and four percent since^{AHT©ManHour_} _ _ _ ^{. Forecaster . .} then, the inflation factor should be

close to 3.5%.

3 . Enter the calculated inflation factor into the box labeled "Inflation Factor" . For example, for an inflation factor of 3 .5%, enter 0.035 .

1. Enter the year in which the equations were last updated into the box labeled "Since This Year."

2. Verify the date in the box labeled

"Current Date" and make any necessary changes to reflect today's date.

Figure 24 The window displayed by selecting 6. Click on the box labeled "Use inflation Inflation Factor from the Setup menu of the main factor" to place an x in the box. If an window. x is present, the inflation factor is in effect. If the box is empty, the inflation factor is not in effect .

7. Click on the Done button to save your changes.

Note: The inflation factor affects only those data sets which are created while the inflation factor is in effect . That is, selecting "Use inflation factor" will not affect data sets currently in the database.

AHTD Man-Hour Forecaster User's Manual 3 5

The following is a list of the fields which are required in the input file when creating a new data set (see Creating a New Data Set, above) . The column positions and widths shown are the default values and can be changed to suit your needs .

e job number uniquely identifies eac

1 Multiple instances of a jobnumber'are allowed, but should be avoided'. This field is used to groupjobs by their resident engineer (RE) for staffing the R offices, If this field is blank is given >tvalue "UNASSIGNED This field specifies the functiona classification of the job'and is used in the selection of the appropriate man-hour

rediction,equation . See Functional a.5siffcation section below.

ii$-field .specifie5 the ty omied, by the', job a ^, ion of the appro

t ion equation .

...... . ...... status identifies a job asber: oposed orunder construction : : : : :: --proposed nder construction

he work orderdate is used to estimate the which is the month work begins if this field contains- invalid' data, . .

will be used instead. See'Notes

These values can not be changed due to their data type .

AHTD Man-Hour Forecaster User's Manual

Functional Classification

When developing the man-hour prediction equations, it became necessary to categorize jobs by their functional classification . A simple categorization was chosen based on the functional classification as shown in the following table :

Notes on the input file format:

• If a job in a data set does not have a valid start date, the AHTD Man-Hour Forecaster cannot distribute its man-hours . The work order date is assumed to be the start date . If the work order date is invalid, the let date is used to estimate the start date . If both are invalid, the job will not be processed .

• The completion date is used to calculate the length (in months) of a job . If a job does not have a valid completion date (which is the case for most jobs in a typical data set), a completion date must be estimated by the AHTD Man-Hour forecaster (see Appendix C) .