Creating a spreadsheet based on an amount allocated

[ZackTx]

I wonder if someone can help me.
I work for a manufacturing company, and we have to allocate materials according to the purchase orders we receive. We have been doing it by hand every day, but it would really help if we could do it in Excel, as it could catch any human errors.

Here is an example to see what I mean:
In cell A1 I would have the amount of raw material allocated. From each pound of material I can make 2 of x OR 10 of y OR 5 of z. If the amount in A1 (amount allocated) was 100, A2 (amount remaining) would be 100, A3 (product x) = 200, A4 (product y) = 1000 and A5 (product z) = 500. B1-B5 have the descriptions. In C3-C5 I would like to enter the amount of product that I am manufacturing, according to the purchase orders we receive.

When I enter an amount in the C column, I would need it to reduce A2 (material remaining) by the relevant amount and thereby reduce the amounts in A3-A5.

I can't seem to find a formula that can do this, and have very limited experience with macros. I haven't programmed since high school...

Any help would be greatly appreciated. Even if you could direct me to a link where something like this is discussed.

Thanks for your help,
Zack

 

[dannac]

If you input a value of 20 into C3,C4,C5 ......

Is this what you want as output

A2 - 80

A3 - 180

A4 - 980

A5 - 480

 

[ZackTx]

Thanks for taking the time to reply to my post.
After putting 20 in C3, it would look like this:

A1 (Amount allocated) - 100
A2 (Amount available) - 90 (because it only takes 10 units of raw material to make 20 of product x)
A3 (product x) - 180
A4 (product y) - 900
A5 (product z)- 450

 

[ZackTx]

I've added more explanation to my previous post. I tried to edit the original post but it wouldn't let me:

Thanks for taking the time to reply to my post.
After putting 20 in C3, it would look like this:

A1 (Amount allocated) - 100
A2 (Amount available) - 90 (because it only takes 10 units of raw material to make 20 of product x)
A3 (product x) - 180
A4 (product y) - 900
A5 (product z)- 450

If I would enter 20 in C4 instead, it would look like this:
A1 - 100
A2 - 98 (because it only takes 2 units of raw material to make 20 of product x)
A3 - 196
A4 - 980
A5 - 490

If I had 20 in C3 and 20 in C4, it would look like this:
A1 - 100
A2 - 88
A3 - 176
A4 - 880
A5 - 440

Basically, A1 determines the beginning value of A2, and then everything feeds off of A2. A3=2*A2, A4=10*A2, etc. The amount in the C column reduces A2 proportionately to how much raw material was needed to make the product (as determined in the A column).