Spread a yearly total across chosen months using whole numbers only

[rkobeyer]

Hi there,
I've been working on the holy grail of excel formulas to solve my current labor intensive excel work.
A startup company entrusted me to figure out their monthly orders based on a yearly total that they give me.
They also wanted me to take into account if the orders are spread evenly or "ramped up" incrementally over the year, depending on what they choose.
I created an excel solution that solved this, for a while. For example 10 orders spread evenly over 12 months was 0.8, or alternatively, a "ramped up" spread is 0.2 in Jan, 0.3 in Feb to 1.5 in Dec.

But the engineers have come back to me saying they don't want to see decimal places when figuring out how many products they need to build. They want whole numbers.
Fair enough, but there are 4 products divided over 4 years for 30 customers, all with their own monthly order quantities; doing this manually is a nightmare and opens me up to error. And these quantities can change daily with new customers, or an engineer's change of mind.

On top of that, if a customer wants, say, 20 products in 2014, but the engineers can't start manufacturing until June, I will need to spread 20 from June to December.
So using whole numbers this, spread evenly, would be: JUN = 2, JUL to DEC = 3. Or if they can't start until December, all 20 products will be manufactured in December.

I've looked everywhere to a solution to this problem but haven't had much luck. After 3 attempts that have gotten close, but not close enough, I am starting to worry that a solution might not exist. Please help!

I have attached an excel file showing my current, inadequate solution and my 3 attempts to solve this problem. If you test out different numbers in the total columns, you will see why these attempts haven't solved the problem, errors pop up, negative numbers, cells not adding up to the original total etc.

Click Slow Download when your on the site.

http://speedy.sh/YF4vj/Attempts.xlsm

Below is how the new hopeful solution should work. The new formula should give the figures shown from CELL F2:Q5

If having "Ramp" and "Even" is too complicated, maybe you could just focus on the Even spread. That would still help me a lot. Thank you in advance!

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Spread[/TD]
[TD]2014[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[TD]APR[/TD]
[TD]MAY[/TD]
[TD]JUN[/TD]
[TD]JUL[/TD]
[TD]AUG[/TD]
[TD]SEP[/TD]
[TD]OCT[/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Customer 1[/TD]
[TD]JAN[/TD]
[TD]DEC[/TD]
[TD]Ramp[/TD]
[TD]50[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Customer 2[/TD]
[TD]MAY[/TD]
[TD]DEC[/TD]
[TD]Even[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Customer 3[/TD]
[TD]DEC[/TD]
[TD]DEC[/TD]
[TD]Ramp[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Customer 4[/TD]
[TD]MAR[/TD]
[TD]NOV[/TD]
[TD]Even[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Even %[/TD]
[TD]8.3%[/TD]
[TD]8.3%[/TD]
[TD]8.3%[/TD]
[TD]8.3%[/TD]
[TD]8.3%[/TD]
[TD]8.3%[/TD]
[TD]8.3%[/TD]
[TD]8.3%[/TD]
[TD]8.3%[/TD]
[TD]8.3%[/TD]
[TD]8.3%[/TD]
[TD]8.3%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Ramp %[/TD]
[TD]2%[/TD]
[TD]3%[/TD]
[TD]4%[/TD]
[TD]6%[/TD]
[TD]7%[/TD]
[TD]8%[/TD]
[TD]9%[/TD]
[TD]10%[/TD]
[TD]11%[/TD]
[TD]12%[/TD]
[TD]13%[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]