Advanced Calculations for MTD and Budgets


#1

We rely upon the idea of budgets for our company to track against sales. We import a budget file that contains a daily budget for each of our locations. When running reporting, I can obtain my daily budget (easy) or my month budget whether in Custom or Period Mode.

However, I am looking for a way to obtain MTD budget. For example, assume the budget for January 2018 at location 1 is $3,200.00. I would like to see a Favorite that shows 3 column. Column A - location name (easy), Column B - MTD Sales (easy), Column C - MTD Budget. Therefore, hypothetically on January 5 when I ran this Favorite Column C would show a budget total of $516.12.

Along the same lines, building a favorite that would allow me to create a YOY comparison on month versus month performance. Simply put, being able to compare January 1-5 2017 versus January 1-5 2018. Currently, I only know how to show MTD Sales (ie. January 1-5 2018) versus all of January 2017 (1-31). NB! I do understand I can break this down daily by manually creating a report that would show January 1-5 2017 vs. January 1-5 2018, but that would require manipulation each month to update the favorite to the current month.

Any help would be greatly appreciated.


#2

Hi JDogg016,

Is the issue adding the extra column in custom mode for the MTD Budget? Or setting up a rolling MTD period that you won’t need to change each day?


#3

The latter, setting up a MTD budget that would not need to change each day.


#4

You will need to make sure that you are on the latest version of Phocas. Or one of the versions that have the functionality if you aren’t hosted.

Go to create a period > Name the period > Select the day option > Click the start date > Special tab > Select the first day of Month 0 > Go to the end date > Select the special tab (or offset tab and put 0) > Then match today or yesterday of Month 0 > Save the period.

See the below link for a more detailed description: https://help.phocassoftware.com/display/userdoc/Offset+dates

Let me know if you have any issues.


#5

Thanks Matt, that link is great, it has allowed me to automate 14 dashboards that I was manually updating each month to reflect current month by day and previous month by day, you legend! :sunglasses: