I am trying use a fixed amount to allocate compensation based on percentage, $1M. the issue I am having is I am over-allocating because I am using a base and inflation %. so, I inflate by 1.05% and I am allocating that fixed amount also, which i don't necessarily want to do. The purpose of the model is to take assumptions about enrollment, staffing and compensation, and then allocate comp across dept. The ideal situation would look at all these variables and produce a result. The issue I am having is that I am using a pre-existing base, which I am then trying to allocate compensation across. So, the model becomes driven by the existing percentages, rather than recalculating them based on this new information. I attached what I am working with. But, it ain't working. Let me know if you can guide me. The numbers aren't the issue the methodology is where I am getting hung up.
Can anyone think of another approach. My programming skills are not up for this.
Bookmarks