Is there a way to build an automatically updating working day function?
i.e. I’ve built a custom report that monitors sales vs plan but the plan is divided by number of working days in the month multiplied by number of days completed. Therefore shows sales vs a pro rata’d plan.
Jan = 22 working days
Friday 25th Jan was day 18
Jan plan total = 450,000 units
Jan sales at 25th = 400,000 units
Jan plan of 450,000 / 22 days * 18 days = 368,182 vs sales of 400,000. So we are 31,818 units above plan on day 18.
When we uploaded our budget, we only put in monthly amounts and the Phocas support team run a script to split it by working days for each month, I do however have to go in and build the working days calendars, I need to do 6 per year as we operate in 6 different states and they can have different working days each month. This then allows us to compare our daily sales amount against a daily budget amount as illustrated in this thread.
Shane, I never thought of this before - I import my budgets into NAV which is quite a long process - we only do monthly budgeting but deleting and replacing takes ages.
I’d love to do daily but the NAV interface wouldn’t cope (though the table can support it).
If there was a script to split it this would be great.
Thanks both for your replies.
I guess I’m of the same opinion as you @StuartH that’d I’d really like Phocas to add a simpler PERIOD_DAYS and PERIOD_COUNT function.
@ShaneAngel
I’ll speak to my Phocas support advisor, to see if they can run the same script for us as I load out budget by month also.
I would be interested in seeing {PERIOD_DAYS} being adjustable, too. I was working on this type of thing throughout January, trying to make a moving daily budget, based on the overall month budget, how much of the budget has been completed, and how much is left to make up until the end of the month.
From my understanding, {PERIOD_DAYS} pulls the number of days within the designated period, so if I have a MTD period defined as the 1st of the month to yesterday, it would pull Jan-01 - Jan-28. The issue is that it does not recognize the holidays or weekends that we excluded on our Working Days calendar; rather, it just counts the number of days elapsed in the month.
My workaround was to upload two measures broken out daily for every working day this year :
Fixed working days for the month (Jan-02 was 22, Jan-03 was 22, Jan-04 was 22…)
Moving billable days remaining in the month (Jan-02 was 22, Jan-03 was 21, Jan-04 was 20…)
That workaround is simple and works for the most part, since I did it by branch, but it does not work for company-wide numbers, as the measure sums between all the branches’ working days defined, so it becomes 100+ working days, rather than 22.
I think we would need some type of custom coding to have {PERIOD_DAYS} defined exactly as we want it to ignore our holidays and weekend days, which I have spoken with Phocas about previously.
I put mine in a Forecasts stream. It’s all through Custom Mode, so I was able to use multiple streams, as needed. Otherwise, you can add them as measures within an existing stream. Here are my docs:
Decreasing Days
Fixed Days
Then, in Phocas, I have new measures available within the database:
Finally, a bit of math fun:
Moving goal for one branch = (EntireMonthBudget - MTDActualSales) / BillableDaysEllapsed
For the daily budget per branch, we just use a widget showing the Budget$ goal for Yesterday, since I have the Excel with each day’s goal uploaded and mapped into the database.
For the moving budget for the entire company, we basically just add a piece to the end of the formula to divide the MOVING DAYS element by the number of branches, in order to account for one branch’s worth of days:
Nice, out of interest do the branches have different period lengths? Presume this would be true if one was US and one was UK as you’d have to account for different statutory holidays.
They do not for me. All of my branches are on the same working day schedule. Your example illustrates why it would be helpful to have a way to define {PERIOD_DAYS} through a calendar of some sort, similar to how summarizing by Working Days operates.
Absolutely, on point 1 there are multiple ways to do it as you can see. Tim has opted for a fixed and a reducing days basis (one minus the other would give the actual day number), my preference is the opposite as I’m more likely to want to see how many days have passed rather than are left - but it’s horses for courses really.
I think if you questioned a selection of users outside of PUG you’d see big support for this.
Point 2 is spot on, ideally you want the budget as granular as possible but this isn’t always practical in the users ERP system as can involve huge imports.
Spot on, @will.marriott ! Thanks for the reply. As @StuartH said, I think many Phocas users would be inclined to use this functionality, especially as more people learn how to upload and use Budget data through DB Designer.
Thanks @StuartH and @tim.janes for responding. These suggestions have been logged in our system. There’s currently a couple of roadmap items which relate however they haven’t been prioritised as yet. I can see the value that they would deliver which will help in our planning.
@will.marriott, has there been any development around this suggestion? I’m trying to do something similar. If not, I’d like to cast my vote in boosting its priority if possible.
I’m not sure if this is the same concept - but we had issues with presenting sales vs budget by days in the default way that the script worked (same as Stuart mentioned) but rectified that by importing a different calendar from our ERP. That ERP calendar does not include Saturdays, Sundays or Holidays.
This is on my list to implement soon - I might as you mention include declining days (or Days Remaining in Period)
It’s a bit of a faff to have to make this sort of table to handle it and then the ongoing maintenance of it but at least it fixes the ability to split budgets etc into days without having to import an enormous daily budget.
