Hopefully Simple set up, stumping me

[lance214]

Here is the data I have for the sheet;
COLUMN A INVENTORY ON HAND (static)
COLUMN B enter MATERIALS REQUIRED FOR SCHEDULE "X"

Here is what I am trying to accomplish and just cannot wrap my brain around it

Our goal is a perpetual inventory that is taken once a week. Want to show how many units are left after deducting the forecast for the current schedule. Basically have 50 on hand, need 8 for next schedule, enter same and show balance remaining.

Do I need to have two columns for each production week or is there a way to have a separate column that shows remaining balance after each entry weekly? Trying to enter amount for said week and then show number after removing it from on-hand total. was attempting to have a dedicated column for this part rather then one for each week.

Hopefully I have explained this better than it is sitting in my head, because I am just not coming up with anything logical. Thanks in advance for your help

 

[mackc557]

This is how I understand your sheet, please correct me. I'm guessing you want the expected remaining inventory to happen multiple times.

Book1
	A	B	C	D
1	Part #	Parts on Hand	Expected Use	Parts After Use
2	9183	47	12	35
3	7474	18	11	7
4	9744	34	3	31
5	6949	62	5	57
6	2908	27	4	23
7	7272	68	12	56
8	5575	41	13	28
9	6371	39	15	24
10	8177	18	8	10
11	2994	65	6	59
12	2978	47	15	32
13	1836	53	13	40
14	4269	19	2	17
15	7929	31	8	23
16	7188	32	13	19
17	7923	46	2	44
18	9629	16	15	1
19	7634	46	3	43
20	2028	68	7	61
21	6867	10	10	0
22	9003	56	2	54
23	7113	13	4	9
24	4119	15	6	9
Sheet1

Cell Formulas
Range	Formula
D2:D24	D2	=B2-C2

 

[lance214]

yes, but it will be performed on a weekly basis. I was trying to avoid cluttering the spreadsheet with too many columns considering the frequency we will be performing the task. Was hoping for a one-column total that would update with each week's entires.

 

[mackc557]

Do you plan on updating the Expected Use section to have actual values or are you planning on keeping them as expected values? Do you want to keep a record of previous weeks' On-Hand, Expected Use, and Parts After Use? Or are you going to overwrite it with the updated values?

 

[lance214]

The expected use column will be actual values gleaned from forecast and production schedules. Honestly had not decided if we wanted to monitor actual versus on hand after the schedule clears. believe it will be too tough due to urgent, RGA, and just general salespeople PITA orders. Overwriting previous values seems to be the simplest form of monitoring for Purchasing and fabrication purposes.