Monthly Budget Variance Report

Jeff Geddes


12/1/2015

BUSM 614
VBA Final Project Memo

 

 

EXECUTIVE SUMMARY:
Martin Anderson, PC is a probate, estate planning, and tax law firm located in Provo, UT. The firm consists of six employees: two lawyers, two paralegals, a receptionist, and an accountant. The firm has been in existence since 2001 when Martin separated from the Munster and Anderson Partnership. After an efficiency analysis performed in 2013, the firm has emphasized its probate practice.

Martin creates an annual budget in January that takes into consideration the prior year budget, prior year data, and current year data. In 2014, Martin wanted to get better control of his business operations and asked me to create a monthly P&L compared to Budgeted P&L, a report that could not be run in Quickbooks. After finding this initial report useful, Martin wanted to see more data including: Prior Year Month, Current YTD, Prior YTD, and Budgeted YTD. The report also included a wage summary for each employee under the same categories. With all the moving parts in this report, I found formula mistakes popping up almost every month. Completing the report took me at least an hour each month (usually longer), and the process was not enjoyable. I needed an automated process that was accurate, consistent, and efficient – so I started working on this VBA project.

 

 

THE PROJECT DESCRIPTION:
The report has three basic elements to its creation: (1) preparing the workbook, (2) creating the working budget, and (3) creating the monthly report. I wrote a separate macro for each of these steps.

(1) Preparing the Workbook

• Add a sheet before prior month sheet by referencing the sheet name: Sheets.Add Before:=Worksheets("P&L " & priorMonth & " 15")
• Use an input box to enter the first three letters of the current month: currentMonth = InputBox("Enter the first three letters of the current month"), name the activesheet
• Use ErrorHandler: On Error GoTo Handler
• Name the worksheet: ActiveSheet.Name = "P&L " & currentMonth & " " & CY
• Repeat for the six spreadsheets (different naming convention for each sheet prevented use of loop)
• Handler: use a message box if there is an error, then delete the worksheet if error.
• After this Macro runs, I manually copy and paste the six reports from Quickbooks Online into their respective tabs.

(2) Creating the Budget Comparison

• Reference the Current and Prior Year Dates by looking into the last and second to last worksheet names in the workbook: CY = Right(Worksheets(Worksheets.Count - 1).Name, 2), and the same for PY but without subtracting 1 from worksheets.count.
• Create a prior month reference by looking into the prior month tab name: priorMonth = Left(ActiveSheet.Name, 3)
• Copy the prior month working budget, and paste into a new tab created before the prior month: Sheets.Add Before:=Worksheets(priorMonth & " Comparison P&L"), and worksheets(activeindex+1).select…cells.select…election.copy…asteSpecial, etc.
• Name the active sheet using an input box: ActiveSheet.Name = InputBox("Enter the first three letters of the current month") & " Comparison P&L"
• Create a reference for currentMonth: currentMonth = Left(ActiveSheet.Name, 3)
• Increase the prior month budget column reference by 1 to reflect an increase of 1 month on the current sheet: Range("S1").Value = Range("S1").Value + 1
• Change the date in cells B5 and C5 using the DateAdd formula: Range("B5").Value = DateAdd("m", 1, Range("B5").Value)
• Insert the current month into vlookup formulas throughout the spreadsheet by changing the formula at the top, then copying the formula down to the bottom of the P&L: Range("B7").FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-1],'P&L " & currentMonth & " 15'!R6C1:R100C3,2,FALSE)),0,(VLOOKUP(RC[-1],'P&L " & currentMonth & " 15'!R6C1:R100C3,2,FALSE)))"
• Copy and paste range formatting of unchanged columns to changed columns: Range("B7").COPY Destination:=Range("B8:B10,B13:B39,B41:B45,B47:B55,B57:B60,B62:B64,B66")
• Set x = 69, then use this reference to insert current month into vlookup formulas calculated in columns B, C, F, and G for Employee A (note that employee A refers to different spreadsheets than other employees): Range("B" & X).FormulaR1C1 = "=Numbervalue(IF(ISNA(VLOOKUP(RC[-1],'MA Payroll " & currentMonth & " 15'!R6C1:R100C26,10,FALSE))," & "0.00" & ",(VLOOKUP(RC[-1],'MA Payroll " & currentMonth & " 15'!R6C1:R100C26,10,FALSE))))"
• Set x = x+1 and Loop until endXlDown -1 to insert current month into vlookup formulas calculated in columns B, C, F, and G for all other employees: Range("C" & X).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-2],'Payroll " & currentMonth & " 14'!R6C1:R100C26,10,FALSE))," & "000" & ",(VLOOKUP(RC[-2],'Payroll " & currentMonth & " 14'!R6C1:R100C26,10,FALSE)))"
• Where necessary, check each cell in the payroll range for "#N/A" and replace with "":Selection.Replace What:="#N/A", replacement:="", Lookat:=xlPart,SearchOrder:=xlByRows, MatchCase:=False
• Autofit column width: Columns("A:AZ").AutoFit
• I also used the ribbon creator to create a new tab and three buttons, one for each Macro.

(3) Creating the Monthly Report

• Create a reference to the budget worksheet: budget = ActiveWorkbook.Name
• Open the "template" worksheet: Workbooks.Open (ThisWorkbook.Path & "\template.xlsx")
• Input the first three letters of the current month using an input box, and name the activesheet: ActiveSheet.Name = currentMonth & " Comparison P&L"
• Save the activeworkbook as an xlsm file with a new name in the same folder: ActiveWorkbook.SaveAs Filename:= ThisWorkbook.Path & "\2015 " & currentMonth & " Comparison P&L.xlsm", FileFormat:= xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
• Set reference to currentMonth workbook: monthlyReport = ActiveWorkbook.Name
• Copy and paste specified columns from the Workbook(budget) and Worksheets(currentMonth & " Comparison P&L") into the monthly report workbook sheets 1, sheets 2, and sheets 3. Change names of the tabs. Auto adjust columns. Also, on the first tab, adjust column width of the memo column:
• Workbooks(budget).Activate
• Worksheets(currentMonth & " Comparison P&L").Activate
• Range("B5:D80").Select
• Selection.COPY
• Workbooks(monthlyReport).Activate
• Range("B5:D80").PasteSpecial xlPasteValues
• Workbooks(budget).Activate
• Range("F5:H80").Select
• Selection.COPY
• Workbooks(monthlyReport).Activate
• Range("G5:I80").PasteSpecial xlPasteValues
• Columns("A:AZ").AutoFit
• Columns("E").ColumnWidth = 45
• Columns("J").ColumnWidth = 45. Also, adjust the width of the Memo column in tab 1 to 45

CHALLENGES AND THINGS I LEARNED

 

I learned a lot during this project from encountering challenges that we had not yet covered in class and finding solutions through recording macros and google searches. These challenges included: error handling, adding sheets in a specific location, referencing sheets by order, adding a month to a date formula, using FormulaR1C1 referencing, working with a template worksheet, saving a workbook as, the replace function, and adjusting column width.

 

Error handler – As I practiced running each of the macros, I ran into errors when I had not first deleted the prior run worksheets. The error was consistent, so I looked up how to deal with errors on Google. I created an "On Error Goto…Handler: Exit Sub" argument. I created a subsequent msgbox to display the error I kept seeing. One of the problems I ran into, however, was that the Handler ran each time whether or not the project had an error. I used a google search to realize that I needed an "exit sub" before my error handler code. This solved my problem.

 

Add a sheet in a specific place – I knew how to add a new sheet to the workbook, but how to add a sheet in a specific place of my workbook was a challenge for me. I wanted the most recent profit and loss reports to show up to the left of the prior month reports. I used the "sheets.add Before:=… argument to refer to the prior month sheets. I found this on Google.

 

Referencing a worksheet by order – I needed to refer to the prior month budget worksheet by selecting the sheet to the right of the activeworkbook. I used the Worksheets(ActiveSheet.Index + 1) to solve this problem. I also used the embedded worksheets.count function to pull references to prior and current years: Right(Worksheets(Worksheets.Count - 1).Name, 2). I learned how to do this in class, and then checked syntax on Google.

 

Referencing part of the name in a worksheet – I wanted to eliminate the use of another input box for the prior month, so I used "priorMonth = Left(ActiveSheet.Name, 3)" after I had selected the prior month worksheet. I knew how reference tab names, but had never tried using the Left() function in the same process.

 

Increasing the Month of a Date – I used the Date Add function to increase the Current Month in a date formula. I found this formula on Google, and tried it until I got it to work: DateAdd("m", 1, Range("B5").Value).

 

FormulaR1C1 – I discovered this kind of referencing when I used the Macro recorder. My goal was to have the spreadsheet display the formula, rather than just a value. One of the problems I had was learning that absolute references do not work with FormulaR1C1. Also, learning how the referencing worked was a bit of a challenge at first, but I figured it out through trial and error.

 

Creating a template worksheet – The monthly report is a 3-tab worksheet. I thought I would try creating template worksheet in the same folder as my budget working document, then open this worksheet when creating the monthly report, and save it as its own 2015 Monthly Report. This worked out very well. I used the thisworkbook.path function to open the workbook.

 

Replace function – Since some employees change through the years, the vlookup argument used in the spreadsheet sometimes returned an N/A#. This caused problems for my sum calculations. I used the macro recorder to learn about the "selection.replace What:… function. This worked wonderfully.
 

Column width adjustment – I used the columns.auto fit to adjust each spreadsheet. Then, I used the columnwidth = 45 to specially adjust my monthly report column where I make comments.

 

 

ASSISTANCE RECEIVED
I did not receive assistance from anyone, nor did I copy substantial parts of code from another person’s project or Google. However, I did research a lot on Google and find syntax. For example, I found the following online: "worksheets(worksheets.Count).Select", then change it to: right(worksheets(worksheets.Count).name,2).




FILES - note that the last file is the final report that is created with the 2nd and 3rd files through the Macro

1. http://files.gove.net/shares/files/15f/jjgeddes/Geddes_Jeffrey_-_VBA_Final_Project_Memo.pdf
2. http://files.gove.net/shares/files/15f/jjgeddes/MAA_P.C._Budget_-_2015_repaired_1.xlsm
3. http://files.gove.net/shares/files/15f/jjgeddes/template.xlsx
4. http://files.gove.net/shares/files/15f/jjgeddes/2015_Oct_Comparison_PL.xlsm