These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Wednesday, April 13, 2016

Special Project P&L update for Business Firm - Cole Moffat

A Consulting Firm has implemented a new Special Project and hired interns to work on it. This new project does not earn any revenues, but when there is a surge of billable work, the Firm takes some of the interns to fill in the gaps. This way, they can bill clients at the standard rate, pay the interns the same as they always do, keep the difference, and not have to constantly hire and fire their workforce to meet the demand. Interns submit weekly timecards, detailing the things they work on. Paychecks to interns is cash out from the Project, and amounts billed to clients from interns’ work is cash in from the project. The Business Firm wants to know how much this Special Project is making them or costing them, updated weekly.

The VBA program, complete with custom buttons on a new ribbon tab, prompts the user to select a raw data file with the updated timesheet information; imports the data, configures it to fit the template, and appends it to the existing timesheet data; updates/ refreshes pivot tables; uses that updated information to recalculate a Project P&L; displays the last 4 weeks’ data and the grand total since project inception; and saves the updated data as a new file with the data processing date. The Business Firm will know the profitability of their new Special Project in near- real time.

*This is based on a real business with a real project. Due to the sensitive nature of the data, all dollar amounts, hours, employee names, company names, or other personally identifying information has been changed. The Consulting Firm is referred to as the Business Firm (BIZ) and the new project is referred to as Special Project (SP).

No comments:

Post a Comment

Blog Archive