Question: One of my tasks as the Sales Manager is to calculate the percentage increase or decrease in sales at the end of the financial year in comparison to prior year sales figures. The formula is quite complex and usually the data captures make mistakes when called upon to enter the formula. Is it possible to create a custom function in Excel to do this calculation?
Answer: Yes, with VBA for Excel. VBA stands for Visual Basic for Applications. It is the friendly programming language used within Excel to develop macros and complex programs.
Why: To create a custom function so that the task of calculating the percentage increase or decrease in sales between current vs. prior year will be simplified.
Process (Excel 2003, 2007 and 2010):
1. Create the spreadsheet as in the example below
2. Press ALT + F11 to open VBA Excel
3. Select Insert from the menu bar, select module
4. Select Insert from the menu bar, select procedure
5. Enter Sales as the name, select “Function” under Type and “Public” under Scope. Refer to the screen shot below.
6. Select OK
7. Edit the statements to what is given below
Public Function Sales (S2009, S2010)
Sales = (S2010 – S2009) / S2009 * 100
End Function
8. Save the workbook. When saving; for Excel 2007 & 2010 select the ‘Save As’ type Excel Macro – Enabled Workbook with the extension xlsm. When saving for Excel 2003 save the workbook normally
9. In the Excel Workbook Select cell F4 and type; =Sales(D4,E4)
10. Copy the formula down and reduce the number of decimal digits where necessary
11. The result will be as below
As can be seen it is now easy to calculate the % Increase/decrease in Sales. The complex formula works in the background. In this way no mistakes will be made.
