Viewing month to date budget figure

howto

#1

Hi,

I have a query that is similar to another topic raised a while back, but I can’t get it to work for me. Hopefully I haven’t just missed something obvious.

Original topic is here: Advanced Calculations for MTD and Budgets

I have set up a database that compares sales stream to a budget stream. I’m hoping to view MTD sales vs MTD budget. MTD sales works well, but I can only see the entire month’s budget figure in comparison.

The budget figure I’ve uploaded is for the entire month.

Is there a way to view a MTD budget figure? If the total monthly budget was $20,000, then half way through the month, I’d like to see a budget figure of $10,000.

It would be perfect if I could do it based on business days for the month, but I won’t be too fussy.

Please let me know if you need any other details.

Thanks,
Scott


#2

There might be a better way to go about this, but here’s how I worked around this. My budget figures I was uploading were monthly as well. My budget figures are stored in a SQL DB. So before I sync over to Phocas, I use some SQL to break out that monthly number out into a daily figure, then I sync those daily budget figures over to Phocas, so my MTD and YTD figures work out OK.


#3

Thanks Aaron. I wondered if this might be the best option. I’m manually uploading the budget data from an Excel sheet. I can split up the monthly figure into daily amounts and Phocas seems to allow that option. It might be the best way to go.

Thanks for your response.

Scott


#4

I’ve done something very similar with turnover vs target and i have done exactly that.
Imported a daily target for each working day, (exclude national holidays or non work days or your target will always work out higher if you include weekends etc)
the have 1 stream as being your MTD sales and the variable stream for the budget.
What you want to be careful of is the period, if you use current month the budget will calculate the entire month because you’ve got an entire month of data in the stream. whereas your.
Set up a new period for start of month to yesterday (using day on the drop down) and it would give you the current rolling month only up to the previous days sales/budget


#5

Thanks @Havok and @aaron.roma . I’ve split out my budget to a daily amount (ignoring holidays and non work days) and it’s worked pretty well.

I couldn’t do it via SQL, but it’s worked well in the Excel version using the current rolling month period I’ve created also.

Thanks again,
Scott