Spaceballs_the_fanboy
New Member
- Joined
- Feb 15, 2018
- Messages
- 18
Hi folks, I've cannot seem to figure out a way to make this spreadsheet do what I want. Perhaps they're just too many moving parts that formulas just won't work.
I am managing inventory and have organized it into weekly categories. 0-6 days, 7-13 days...up to 120+ days. The idea is that any inventory left over will move upward to the next category. The goal is that for each Month, Quarter, and Year 90% of inventory should be done before it reaches 120+ days.
Inventory is a basic formula in itself. Opening + intake - output = Ending, where Ending = next cycle Beginning. This is done over 12 periods, where a period = 1 month.
I am using historical averages for intakes and outputs, which are spread across the different categories.
My issue is the results don't make sense. There are periods where intakes are greater than outputs, so I would expect the next periods opening to be higher. But there are also periods where outputs are greater than intakes, yet the ending inventory is still greater. By the end of the 12th period the beginning inventory from P1 has increase by 5x. Which, in reality would not happen.
It's obvious that I am not doing this correctly, but I'm not able to visualize, or construct how this spreadsheet should be designed to do what I want it to do.
I am managing inventory and have organized it into weekly categories. 0-6 days, 7-13 days...up to 120+ days. The idea is that any inventory left over will move upward to the next category. The goal is that for each Month, Quarter, and Year 90% of inventory should be done before it reaches 120+ days.
Inventory is a basic formula in itself. Opening + intake - output = Ending, where Ending = next cycle Beginning. This is done over 12 periods, where a period = 1 month.
I am using historical averages for intakes and outputs, which are spread across the different categories.
My issue is the results don't make sense. There are periods where intakes are greater than outputs, so I would expect the next periods opening to be higher. But there are also periods where outputs are greater than intakes, yet the ending inventory is still greater. By the end of the 12th period the beginning inventory from P1 has increase by 5x. Which, in reality would not happen.
It's obvious that I am not doing this correctly, but I'm not able to visualize, or construct how this spreadsheet should be designed to do what I want it to do.
Excel Formula:
| MANDATE | | | | | | | | | | | | MANDATE | | | | | | | | | | | | MANDATE | | | | | | | | | | | | MANDATE | | | | | | | | | | | |
|---------|-------------------|----------------|-------------|---------|--------|--------|--------|---------|--------|--------|--------|---------|--------|--------|--------|---------|--------|--------|--------|---------|--------|--------|--------|---------|--------|--------|--------|---------|--------|--------|--------|---------|--------|--------|--------|---------|--------|--------|--------|---------|--------|--------|--------|---------|--------|--------|--------|
| | | | | 81.72% | | | | | | | | | | | | 80.44% | | | | | | | | | | | | 74.10% | | | | | | | | | | | | 78.10% | | | | | | | | | | | 78.31% |
| | | | | P1 | | | | P2 | | | | P3 | | | | P4 | | | | P5 | | | | P6 | | | | P7 | | | | P8 | | | | W9 | | | | W10 | | | | W11 | | | | W12 | | | |
| | Avg. over 5 weeks | From Table | AGE | Opening | intake | output | ending | Opening | intake | output | ending | Opening | intake | output | ending | Opening | intake | output | ending | Opening | intake | output | ending | Opening | intake | output | ending | Opening | intake | output | ending | Opening | intake | output | ending | Opening | intake | output | ending | Opening | intake | output | ending | Opening | intake | output | ending | Opening | intake | output | ending |
| 34% | 44 | 220 | 121+ | 526 | 0 | 176 | 350 | 387 | 0 | 176 | 211 | 270 | 0 | 250 | 20 | 20 | 0 | 240 | 0 | 0 | 0 | 230 | 0 | 0 | 0 | 244 | 0 | 0 | 0 | 393 | 0 | 0 | 0 | 376 | 0 | 0 | 0 | 356 | 0 | 0 | 0 | 288 | 0 | 0 | 0 | 240 | 0 | 0 | 0 | 176 | 0 |
| 22% | 16 | 80 | 120 - 113 | 101 | 0 | 64 | 37 | 123 | 0 | 64 | 59 | 19 | 0 | 93 | 0 | 0 | 0 | 64 | 0 | 10 | 0 | 64 | 0 | 10 | 0 | 78 | 0 | 0 | 0 | 217 | 0 | 0 | 0 | 200 | 0 | 0 | 0 | 180 | 0 | 0 | 0 | 112 | 0 | 0 | 0 | 64 | 0 | 773 | 0 | 64 | 709 |
| 15% | 12 | 60 | 112 - 105 | 171 | 0 | 48 | 123 | 67 | 0 | 48 | 19 | 19 | 0 | 48 | 0 | 58 | 0 | 48 | 10 | 58 | 0 | 48 | 10 | 34 | 0 | 48 | 0 | 13 | 0 | 166 | 0 | 0 | 0 | 136 | 0 | 0 | 0 | 116 | 0 | 0 | 0 | 48 | 0 | 821 | 0 | 48 | 773 | 611 | 0 | 48 | 563 |
| 20% | 17 | 85 | 104 - 97 | 135 | 0 | 68 | 67 | 87 | 0 | 68 | 19 | 126 | 0 | 68 | 58 | 126 | 0 | 68 | 58 | 102 | 0 | 68 | 34 | 164 | 0 | 150 | 13 | 0 | 0 | 118 | 0 | 0 | 0 | 88 | 0 | 0 | 0 | 68 | 0 | 889 | 0 | 68 | 821 | 679 | 0 | 68 | 611 | 849 | 0 | 68 | 781 |
| 9% | 5 | 25 | 96 - 89 | 107 | 0 | 20 | 87 | 146 | 0 | 20 | 126 | 146 | 0 | 20 | 126 | 122 | 0 | 20 | 102 | 269 | 0 | 105 | 164 | 0 | 0 | 82 | 0 | 0 | 0 | 50 | 0 | 0 | 0 | 20 | 0 | 909 | 0 | 20 | 889 | 699 | 0 | 20 | 679 | 869 | 0 | 20 | 849 | 1196 | 0 | 20 | 1176 |
| 4% | 8 | 38 | 88 - 81 | 176 | 0 | 30 | 146 | 176 | 0 | 30 | 146 | 153 | 0 | 30 | 122 | 369 | 0 | 101 | 269 | 0 | 0 | 85 | 0 | 0 | 0 | 62 | 0 | 0 | 0 | 30 | 0 | 940 | 0 | 30 | 909 | 730 | 0 | 30 | 699 | 900 | 0 | 30 | 869 | 1227 | 0 | 30 | 1196 | 1716 | 0 | 30 | 1685 |
| 2% | 8 | 40 | 80 - 73 | 208 | 0 | 32 | 176 | 185 | 0 | 32 | 153 | 449 | 0 | 79 | 369 | 0 | 0 | 70 | 0 | 0 | 0 | 54 | 0 | 0 | 0 | 32 | 0 | 972 | 0 | 32 | 940 | 762 | 0 | 32 | 730 | 932 | 0 | 32 | 900 | 1259 | 0 | 32 | 1227 | 1748 | 0 | 32 | 1716 | 1329 | 0 | 32 | 1297 |
| 2% | 6 | 28 | 72 - 65 | 207 | 0 | 22 | 185 | 514 | 0 | 65 | 449 | 0 | 0 | 47 | 0 | 0 | 0 | 38 | 0 | 0 | 0 | 22 | 0 | 994 | 0 | 22 | 972 | 784 | 0 | 22 | 762 | 954 | 0 | 22 | 932 | 1281 | 0 | 22 | 1259 | 1770 | 0 | 22 | 1748 | 1351 | 0 | 22 | 1329 | 1375 | 0 | 22 | 1353 |
| 1% | 4 | 20 | 64 - 57 | 530 | 0 | 16 | 514 | 5 | 0 | 48 | 0 | 0 | 0 | 25 | 0 | 0 | 0 | 16 | 0 | 1010 | 0 | 16 | 994 | 800 | 0 | 16 | 784 | 970 | 0 | 16 | 954 | 1297 | 0 | 16 | 1281 | 1786 | 0 | 16 | 1770 | 1367 | 0 | 16 | 1351 | 1391 | 0 | 16 | 1375 | 1617 | 0 | 16 | 1601 |
| 2% | 2 | 11 | 56 - 50 | 33 | 0 | 28 | 5 | 0 | 0 | 32 | 0 | 0 | 0 | 9 | 0 | 1019 | 0 | 9 | 1010 | 809 | 0 | 9 | 800 | 979 | 0 | 9 | 970 | 1306 | 0 | 9 | 1297 | 1795 | 0 | 9 | 1786 | 1376 | 0 | 9 | 1367 | 1400 | 0 | 9 | 1391 | 1626 | 0 | 9 | 1617 | 1650 | 0 | 9 | 1641 |
| 5% | 6 | 29 | 49 - 42 | 30 | 0 | 49 | 0 | 0 | 0 | 23 | 0 | 1042 | 0 | 23 | 1019 | 832 | 0 | 23 | 809 | 1002 | 0 | 23 | 979 | 1329 | 0 | 23 | 1306 | 1818 | 0 | 23 | 1795 | 1399 | 0 | 23 | 1376 | 1423 | 0 | 23 | 1400 | 1649 | 0 | 23 | 1626 | 1673 | 0 | 23 | 1650 | 1649 | 0 | 23 | 1626 |
| 8% | 9 | 45 | 41 - 35 | 10 | 0 | 36 | 0 | 1078 | 0 | 36 | 1042 | 868 | 0 | 36 | 832 | 1038 | 0 | 36 | 1002 | 1365 | 0 | 36 | 1329 | 1854 | 0 | 36 | 1818 | 1435 | 0 | 36 | 1399 | 1459 | 0 | 36 | 1423 | 1685 | 0 | 36 | 1649 | 1709 | 0 | 36 | 1673 | 1685 | 0 | 36 | 1649 | 1709 | 0 | 36 | 1673 |
| 17% | 18 | 90 | 34 - 28 | 600 | 550 | 72 | 1078 | 390 | 550 | 72 | 868 | 560 | 550 | 72 | 1038 | 887 | 550 | 72 | 1365 | 1376 | 550 | 72 | 1854 | 957 | 550 | 72 | 1435 | 981 | 550 | 72 | 1459 | 1207 | 550 | 72 | 1685 | 1231 | 550 | 72 | 1709 | 1207 | 550 | 72 | 1685 | 1231 | 550 | 72 | 1709 | 1207 | 550 | 72 | 1685 |
| 26% | 35 | 175 | 27 - 21 | 80 | 450 | 140 | 390 | 250 | 450 | 140 | 560 | 577 | 450 | 140 | 887 | 1066 | 450 | 140 | 1376 | 647 | 450 | 140 | 957 | 671 | 450 | 140 | 981 | 897 | 450 | 140 | 1207 | 921 | 450 | 140 | 1231 | 897 | 450 | 140 | 1207 | 921 | 450 | 140 | 1231 | 897 | 450 | 140 | 1207 | 921 | 450 | 140 | 1231 |
| 21% | 25 | 125 | 20 - 14 | 30 | 320 | 100 | 250 | 357 | 320 | 100 | 577 | 846 | 320 | 100 | 1066 | 427 | 320 | 100 | 647 | 451 | 320 | 100 | 671 | 677 | 320 | 100 | 897 | 701 | 320 | 100 | 921 | 677 | 320 | 100 | 897 | 701 | 320 | 100 | 921 | 677 | 320 | 100 | 897 | 701 | 320 | 100 | 921 | 677 | 320 | 100 | 897 |
| 6% | 7 | 35 | 13 - 7 | 60 | 325 | 28 | 357 | 549 | 325 | 28 | 846 | 130 | 325 | 28 | 427 | 154 | 325 | 28 | 451 | 380 | 325 | 28 | 677 | 404 | 325 | 28 | 701 | 380 | 325 | 28 | 677 | 404 | 325 | 28 | 701 | 380 | 325 | 28 | 677 | 404 | 325 | 28 | 701 | 380 | 325 | 28 | 677 | 404 | 325 | 28 | 701 |
| | 24 | 120 | 6 - 0 | 395 | 250 | 96 | 549 | | 250 | 120 | 130 | | 250 | 96 | 154 | 250 | 250 | 120 | 380 | 250 | 250 | 96 | 404 | 250 | 250 | 120 | 380 | 250 | 250 | 96 | 404 | 250 | 250 | 120 | 380 | 250 | 250 | 96 | 404 | 250 | 250 | 120 | 380 | 250 | 250 | 96 | 404 | 250 | 250 | 120 | 380 |
| | 1226 | 1226 | TOTAL | 3399 | 1895 | 1026 | 4313 | 4313 | 1895 | 1103 | 5203 | 5203 | 1895 | 1165 | 6118 | 6368 | 1895 | 1194 | 7478 | 7728 | 1895 | 1197 | 8872 | 9122 | 1895 | 1264 | 10257 | 10507 | 1895 | 1550 | 11814 | 12064 | 1895 | 1449 | 13330 | 13580 | 1895 | 1345 | 14850 | 15100 | 1895 | 1165 | 16279 | 16529 | 1895 | 1045 | 17683 | 17933 | 1895 | 1005 | 18999 |
| | | | | | | | | | | 2129 | | | | 3294 | | | | 4487 | | | | 5684 | | | | 6948 | | | | 8498 | | | | 9946 | | | | 11291 | | | | 12456 | | | | 13501 | | | | 14506 | |
| | AGE | Last FY ending | Files rec'd | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 4494 | |
| | 121+ | 526 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 120 - 113 | 101 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 112 - 105 | 171 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 104 - 97 | 135 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 96 - 89 | 107 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 88 - 81 | 176 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 80 - 73 | 208 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 72 - 65 | 207 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 64 - 57 | 182 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 56 - 50 | 184 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 49 - 42 | 348 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 41 - 35 | 75 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 34 - 28 | 180 | 550 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 27 - 21 | 190 | 450 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 20 - 14 | 242 | 320 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 13 - 7 | 302 | 325 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | 6 - 0 | 324 | 250 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | TOTAL | 3658 | 1895 |
