Internet Piping
Final Project
By Scott Gonzalez
Executive Summary
My father works for a company that installs the underground piping for internet businesses such as Comcast, Utopia, and various other corporate and government entities. He is tasked to produce a weekly production report that takes him about an hour and a half to complete which he does on a calculator first and puts the calculated numbers into Excel second (not a sophisticated user of Excel by any means). The report consists of various employees that make up four crews. The information that needs to be reported by crew is the daily amount of piping feet installed, the daily amount of revenue brought in (calculated by price times the piping feet installed), the daily amount of labor dollars spent, the job number(s) worked on that day, and labor as a percent of revenue. Besides these daily totals, the report should also calculate weekly totals by crew and the weekly totals overall for all crews combined. Finally, there is one crew that installs four pipes at a time with a drill.
A macro is the best tool for my father’s situation. My father does not keep a master copy and copies the prior week’s report to make the next report. Coupling his method of copying worksheets with his propensity to override labor and revenue numbers for various reasons indicates that simply making an articulating spreadsheet would be copied wrong from one week to the next if a formula was overridden. A macro will solve this problem by writing over any one-time changes he made from the prior week. Again, my father takes about an hour and a half to complete this report each week; my plan is to create a macro that will take him at most five minutes to complete the same task.
Contents of Internet Piping - Final Written Project
Detailed Discussion of Project
Step 1 – Create a Master Worksheet and Worksheet Functionality
Step 2 – Create an Employee Name and Wage Worksheet
Step 3 – Create the Terrain Worksheet
Step 4 – Create the Macro for Building a New Weekly Production Report
Step 5 – Create a User Form
Step 6 – Prepopulate the User Form
Step 7 – Use the Inputs to Calculate Labor and Revenue per Crew
Step 8 – Populate the Spreadsheet
Step 9 – Enable Code to Run the Macro for the Corresponding Crew Depending on the Location of the Button
Discussion of Learning
See the full written report and macro-enabled workbook in the links below:
No comments:
Post a Comment