\fC E H K M G J L A B D F Item: Concrete Block Walls Units W N Factor 1: Supervisor: Mary, Shilpa, Tom, Mohammad, Krishna Factor 2: Weather: Frigid, Cold, Mild, Warm, Hot Factor 3: Crew Number: 1, 2, 3, 4 Factor 4: Factor 5: 00 Factor 6: 9 Productivity: Units/craft labor Factor 1: Factor 2: Factor 3: Factor 4 Factor 5 Factor 6 10 Date Completed Quantity Craft Labor Hrs hours Craft hrs/Unit Supervisor Weather Crew # (Identify) (identify) (Identify) Job Name -# Mary Mild 2 11 Job 101A 12 13 14 15 16 17 18 19 20 N 22 23 24 25 26 27 28 29CEM 59700 Construction Accounting and Finance: Construction Industry Spring 2024 Exercise 5 Using Accounting Past Data (big data) and Al to Predict Labor Productivity This assignment relates to the importance of keeping historical cost and accounting data for estimating and managing construction. Predicting labor productivity and costs for constructing a project is essential to reduce risk and generate profits. In the past, many construction firms have not maintained valuable historical data regarding past projects; therefore, losing the advantage of documented historical data learned from having completed projects. It can be argued that the only advantage of an experienced firm competing against a new firm is that the experienced firm has more knowledge or data than the new firm. However, given that this \"past history\" is in the head of the company workers and workers leave or forget, this historical data is lost. The current use of computer software and Al is bringing attention to the benefits of \"big data\" and the benefits of using past data and results to improve performance and reduce risk. One of the best uses of historical data is using past data to predict labor craft productivity for a future project. My past research and studies indicate that actual labor productivity and costs of on-site labor average more than 10 percent from what was predicted or estimated. This is troublesome given that the net potential profit may be less than five percent. Construction worker productivity is a function of many \"factors\" to include weather conditions, difficulty of the work, supervisor overlooking work, years of worker experience, and factors that relate specifically to the work performed (e.g. height of wall, number of penetrations/holes in formed walls, corner versus straight walls, etc.) This assignment is an attempt at demonstrating the use of Al and \"big data\" from past projects to predict labor craft productivity for upcoming projects and reduce estimating risk. Assignment 1. Select a work item that is of interest to you, for example, erect concrete walls forms, place steel beam. You can select a \"work item\" from the attached list; one student per work item. However, you can select any work task you like to use (it does not have to be one on the list and it does not have to be a construction work task). If you select your own work item or task, enter it on the selection template. . Determine an average productivity in units per person hour or person hours per unit (the reciprocal of productivity). Use the average or guideline productivity shown for your selected work item from the list attached list of work items; or identify one if you select work item not on list. This \"average productivity\" becomes a guideline when you make your individual past project entries. Each entry you make will have different results. The average shown is given such that your entered past project entries are reasonable. . Using Microsoft ACCESS or Microsoft EXCEL, format and establish a database that will be used to keep past project productivity results from completed projects. In addition to keeping the labor hours, quantities, and productivity (quantity of work per craft hour) for completed projects, and six factors that strongly affect the craft labor productivity that you believe dictate productivity. Three of the factors will apply regardless of the work task selected. Factor 1: Supervisor of work: Mary, Shilpa, Tom, Mohammad, Krishna Factor 2: Weather condition: Cold, Mild, Warm, Hot, Weather Independent Factor 3: Crew Size and Makeup: Crew 1, Crew 2, Crew 3, Crew 4 You are to name the other three (3) factors you will keep track of that you believe affect the labor productivity or your selected type of work; two of the factors should be factors unique to your work task that affect the productivity achieved; for example the height of the ceiling if installing ceiling tile, the number of penetrations (windows) if erecting concrete wall forms, etc. Your fields (an ACCESS term) or columns using EXCEL should include the following: - Project, - Date completed, - Quantity: - Labor hours expended, - Productivity, - Reciprocal of productivity, - and the six (6) factors. It is preferred to use ACCESS to construct the database (10 possible points). However, if you do not know how to use ACCESS, you can use EXCEL (9 possible points). You are \"making up\" the past data. The actual data would be entered when the firm completed the work for a project. When you make up your thirty past project entry, you will input quantities and labor hours that convert to productivity (units per craft hour) and the reciprocal of productivity (labor hours per unit) that are reasonable but not exactly the average. The format of your ACCESS fields or EXCEL columns is shown in Attachment 1. Example entered data for a work item is shown in Attachment 2 (not all the columns or fields are shown for the masonry example data base in Attachment 2) The quantity, hours, and resulting productivity should somewhat reflect the state of the six factors you are using. For example, if one of your factors is weather, and it is cold, you might expect the productivity to be somewhat lower than average. Similarly, if you believe one of the supervisors is a more effective supervisor, some, if not all the productivities on that supervisors' project are is cold, mild warm, hot. . Enter 30 past project data from completed projects for your work item to include the \"'state\" of the six factors you have identified for the completed projects. . For the 30 past samples, calculate the following. - Average productivity - Median productivity - Variance - Standard deviation - Range . Do queries for the following using Microsoft ACCESS or EXCEL. What you are doing is identifying known factors for an upcoming project and searching or querying the database to best determine the productivity likely to be achieved. 6a. Two factors: Print samples Calculate average productivity and standard deviation. 6b. Three factors: Print samples Calculate average productivity and standard deviation. 6c. Four factors: Print samples Calculate average productivity and standard deviation. 6d. Five factors: Print samples Calculate average productivity and standard deviation. NOTE: Microsoft ACCESS is a database management program, and it would be beneficial if you learned how to use it if you have not already used the program. However, given student time constraints, it is permittable to use EXCEL to do the assignment. Also, because EXCEL is easier to use than ACCESS to do math calculations such as average and standard deviation, you can create your 30 using past records using ACCESS, transfer the ACCESS database to EXCEL, and perform math calculations and graphs. Label and submit each of the six requirements