2018 Excellence Awards Entry:
Calculating Private Financial Budget for Next Month
Name: Oliver Wahner
Title: Solutions Engineer CEE
Company: Alteryx
Overview of Use Case:
Since I like to plan ahead, I plan my budget a month in advance. Each month I am looking at how much of my budget is available to spend on anything extra outside of reoccurring general costs like apartment rent, insurances, car loan, etc. There is data coming from different sources that needs prepping and calculations, like the approx. commission paid to me by Alteryx, credit card receipts and so on.
Describe the business challenge or problem you needed to solve:
The main problem I am trying to solve is that I want to be financially secure every month and don’t want to have to go into my savings, so I would rather plan ahead to see what I will have available for extra spending and treats.
Describe your working solution:
I have created a couple of workflows; the first workflow actually accesses our Alteryx Salesforce portal to retrieve the sales that were done in my region at the beginning of a month. It then prepares this regional sales data so that I can see how much commission I will be paid for the sales from last month. Since commission is usually paid based on the next month (for example for sales that were done in all of September, commission will be paid with beginning of October) The number that I get out of the first workflow that calculates my commission is then used in a different workflow where I calculate my final financial budget for the next month.
Additionally, what also goes into this report is of course credit card expenses. I have a workflow that I can just copy/paste my live credit card expenses in from the Amex website. The workflow prepares the data and tells me what my current live number is for credit card expenses. Here, I use Alteryx Designer to prepare the data, as when you copy and paste the data from the website into Excel, it looks really messy; it typically dumps everything (dates, expense items, values) into just 1 column with empty lines in between—making analysis nearly impossible
Lastly, I bring all the data together, using a template that includes some "standard", reoccurring expenses like phone contract charges, apartment rent, insurances, etc. as well as the earnings I am getting (salary, commission, etc). Combining this template with the other workflows results in an output that shows me how much money I will have left over for the next month to either spend on nice things or put it into my savings account.
Of course, there are future plans to enhance this. I would for example like to run a time series analysis of the Commissions earned to forecast for a 6 or 12 months period how much commission I would get, to give me even longer running forecasts of my budget. Also I need to start saving the original monthly sheet (as I am currently deleting the lines usually in the current month for things that have been paid already) so that I can run comparisons between what I expected the month to be at the beginning of a month and how the month actually turned out at the end of it.
Describe the benefits you have achieved:
So, my main achievement for me itself is time savings. Cleaning up the credit card expenses when copying / pasting from the Amex website could take me (according to how much there is) anywhere from 15 to 30 minutes not using Alteryx Deisgner. Running this every 3-4 days to see the status of the credit card expenses would be nearly impossible cause with a kid and a job, and I just would not have the time to do this. Accessing Salesforce manually and getting the sales numbers from last month for the commission calculation would again be very time consuming.
Of course, the other main benefit I am having from the whole idea is that I can plan my expenses better. I can look at my calculation for October,for example, taking into consideration my expenses that will be in that credit card bill for that month as well as my earnings. When myself or my family are making purchasing decisions, Alteryx has made it much easier to decide whether it fits in next months budget or if it needs to go down on a list for one of the next months to come.
