Comparing Actual to Budget

#1
Hello All

I have a list of actual sales figures for the previous 12 months and the associated budget values. What would be the appropriate way to determine if the actual sales are significantly different from the budget. I was thinking of using a paired t test.

If there is a significant difference is there any way to determine which months are sig higher or lower compared to the budget.

If possible I would prefer to use the tools available in Excel although I have access to Minitab v16


Thanks
Confused
 

hlsmith

Not a robit
#2
Can you define your variables and how they were generated. In particular, what is the budget for and how was it generated? Also, how is it related to sales?

Thanks.
 
#3
The budget sales values are the estimated sales values for each month in the period from Jan 2017 to Dec 2017. They were generated at the start of Jan 2017 and were calculated by averaging actual sales values for each month over the previous 3 years. The actual sales values are based on actual till receipts for each month from January 2017 to Dec 2017.
 

noetsi

Fortran must die
#4
You could use a MAPE as well I would think although that is used normally in time series or finance. MAPE can be done in excel, I can send it to you if you like. It basically is looking at forecasting error, but I think that is what you are doing really. It will treat each period separately than combine the error. That is calculate error for the first month, the second etc and average them.

There are a whole series of these types of calculations, MSE etc. I like MAPE because its intuitively easy to grasp.