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).
- P&L main file with VBA:
http://files.gove.net/shares/files/16w/cpmoffat/MoffatCole_VBAFinal_ProjectPL.xlsm - Workbook with New Data to be appended and processed:
http://files.gove.net/shares/files/16w/cpmoffat/MoffatCole_VBAFinal_RawDataWorkbook.xlsx - Write up:
http://files.gove.net/shares/files/16w/cpmoffat/MoffatCole_VBAFinal_WriteUp.pdf
No comments:
Post a Comment