Formula to subtract cells, multiply by a percent, and distribute by average

[BigPaulMc]

This spreadsheet is to be able to quickly see how many assemblies have been
built to date (Estimated Running Total) and have the Start Quantity
calculated based on what is left to build - divided by remaining project
weeks (each row) - while taking into consideration yield percentage.

For example:

Say I am at line 11.* It is telling me that 74 assemblies have been made
(out of 660).* We have scheduled to start another 40 assemblies expecting to
get 20 good assemblies based on the 50% yield.

Let's say that we had a good week, and instead of only 20 good assemblies
(50%), we got 30 good units (75%).

I want to be able to enter in 75% in B11 - have the Calculated Finish
Quantity (C11) update with the correct yield quantity (30) - and have the
Estimated Running Total update.

I then want (and this is the part I am having the most issue with) - line
A12 through line A40 update with new (equally divided based on remaining
build weeks left) Start Quantities based on a finished quantity of 660 and
taking into consideration the Yield Assumptions.

[quekbc]

The way it's been structured is circular. You calculate the Start Qty based on Yield but you want the Yield to fix Finished Qty to a number which in turn, based on the formula in Start Qty must be fixed to a number (i.e. 20)

So you'll need to break the circularity. The formula you have there seems to work if you just replace the formula with a hard input in A11 with the number 40 in there. Everything else updates automatically.

I.e. for future weeks, manually type in Start Qty and Yield Assumption. The StartQty for all subsequent weeks will auto update.

Would that work for your purpose?

[BoredWorker]

I try to do based on quekbc.
and it is normal to compare between the budget and the actual result.

but for the way you calculate the start Qty, the beginning will have bigger portion, hope you understand...

for my try, you need to key in the for the B2 and the Yield Assumption, Actual
Start Qty, and Calculated Finish Qty. the remain will auto change.

B3:B40 formula,
=ROUNDUP(((660-H2)/C3)/(39-A2),0)

C2:C40
=B2*C2

F2:F40
=G2/E2

H2
=IF(G2="",D2,G2)

H3:H40
=IF(G3="",D3+H2,G3+H2)