Allocation of patient volumes creating a spread issue due to rounding

Hello all,

New to the board here and my work team has a unique issue with a budget spread that we are not able to solve.

File Explanation: To do our budget, we take the volumes (column B-G) of the current year by payer type (column A) to get the payer mix (column J) for the upcoming year. Using this payer mix, we spread the budgeted monthly volumes (row 52) which are provided by our budget team (Budget Teams Volumes tab) across each month (column R-AC) by payer.

Issue: when we round the volumes which are based on the projected volumes in column N multiplied the total volumes for the month, it will zero out the volumes where there should be volumes. See the yellow highlighted section. This creates a rounding variance in row 54 and we put the rounding plug in row 34. Unfortunately, we can’t spread a partial volume evenly across all 12 months since these are patients. So how do we allocate the projected volumes from column N across the 12 months as a whole number using a formula without handpicking for each volume? This has to be a scalable formula as this is for 22 service lines, 9 hospitals and multiple iterations of budget.

Your help is greatly appreciated!

Thanks,

James

Use the payor grouping with the highest contribution as the balancing figure:
L34=L52-L30-SUM(L35:L51)

Try this in R34:

=ROUND(SUMPRODUCT($J34*$R$52:R$52)-SUM($Q34:Q34),0)

Thanks for taking the time to look into. This is really close. I dropped the formula into the file (see attached) and it definitely works getting those projected volumes into the budget spread. In total, there is only a variance of 2 but still a rounding issue by month in row 54.

Because it's only off 2 for the entire year, I don't mind putting that rounding issue to row 34. Just need a way to get that rounding variance by month into that row. I tried on my own and because the formula is dependent on the prior month, I can't just link the formula to row 30 to remove the variance.Volume Spread - Test 3.26.24.xlsx

You could use more spill array formulas in your workbook since you use Excel 365, like the bellow.

Formula:

Please Login or Register  to view this content.