Plan by working days vs sales


#1

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.

Thanks
Graham


#2

Hi Laney,

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.

Feel free to ask any questions, happy to help. :slight_smile:


#3

If your budget is monthly then Shane’s will work, it’s a point we’ve not looked at as we use an Average daily value instead.

For example (not real numbers):

Budget of £1m over 20 working days = £50k per day
Sales Yesterday = £49k vs Budget of £50k
Avg Sales MTD = £51k vs Budget of £50k

At my last place I had a daily report out of SQL Reporting Services that did similar to what you’re talking about so I know what you’re looking for.

I’d like them to explore developing the [PERIOD_DAYS} and {PERIOD_COUNT} functions to be able to be specific to a measure.

Essentially, if you could in the calculation for a and b parts:

a - Budget Sales - 1st Jan to 31st Jan = £100k where {PERIOD_DAYS} = 22
b - Invoiced Sales - 1st Jan to 28th Jan = £90k where {PERIOD_DAYS] = 19

Therefore the calc would look something like:

a / (a{PERIOD_DAYS} = £100K / 22 = £4,545 per day * b{PERIOD_DAYS} = £4,545 * 19 = £86,363 to Budget To Date

The trick is dictating to the function which period to use whether it’s the full month or the month to date.

Of course the work around is to add budgets daily but that’s one hell of an import!


#4

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.


#5

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.


#6

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 :

  1. Fixed working days for the month (Jan-02 was 22, Jan-03 was 22, Jan-04 was 22…)
  2. 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.


#7

Ah that’s logical, would that be in a separate stream?

WorkingDays

MonthBudget / PeriodDays = DailyBudget
DailyBudget x WorkingDay = MTDBudget

You could have additional columns for WorkingDayYear and YearDays

Even if no one implements it this way, it might help the developers to write a function that mirrors it.


#8

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:
image

Finally, a bit of math fun:

Moving goal for one branch = (EntireMonthBudget - MTDActualSales) / BillableDaysEllapsed
image

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:
image


#9

@tim.janes great way to do it, thanks for sharing!!


#10

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.


#11

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.


#12

Wow, some good stuff coming out in this post.

I can see a couple of suggestions here.

  1. Ability to use a “count of completed working days” and a “total count of working days” value in custom mode calculations.
  2. Option on budget import to split monthly values into daily values based on a working day calendar.

This is so you can easily get a sales versus pro-rata budget for the current month.

Does that sum it up @LaneyPhocasLubes @StuartH @tim.janes ?


#13

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.


#14

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.


#15

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.