Spreadsheet: 763280d1642091583-google-sheets-formula-needed-to-work-out-when-to-pay-for-inventory-cashflow-forecasting-forecasting-example
Sheet: Sheet1

Inventory
 | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | Week 6 | Week 7 | Week 8 | Week 9 | Week 10 | Week 11 | Week 12 | Week 13 | Week 14 | Week 15 | Week 16 | Week 17 | Week 18 | Week 19 | Week 20 | Week 21 | Week 22 | Week 23 | Week 24 | Week 25 | Week 26 | Week 27 | Week 28 | Week 29 | Week 30 | Week 31 | Week 32 | Week 33 | Week 34 | Week 35 | Week 36 | Week 37 | Week 38 | Week 39 | Week 40 | Week 41 | Week 42 | Week 43 | Week 44 | Week 45 | Week 46 | Week 47 | Week 48 | Week 49 | Week 50 | Week 51 | Week 52 | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | Week 6 | Week 7 | Week 8 | Week 9 | Week 10 | Week 11 | Week 12 | Week 13 | Week 14 | Week 15 | Week 16 | Week 17 | Week 18 | Week 19 | Week 20 | Week 21 | Week 22 | Week 23 | Week 24 | Week 25 | Week 26 | Week 27 | Week 28 | Week 29 | Week 30 | Week 31 | Week 32 | Week 33 | Week 34 | Week 35 | Week 36 | Week 37 | Week 38 | Week 39 | Week 40 | Week 41 | Week 42 | Week 43 | Week 44 | Week 45 | Week 46 | Week 47 | Week 48 | Week 49 | Week 50 | Week 51 | Week 52 | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | Week 6 | Week 7 | Week 8 | Week 9 | Week 10 | Week 11 | Week 12 | Week 13 | Week 14 | Week 15 | Week 16 | Week 17 | Week 18 | Week 19 | Week 20 | Week 21 | Week 22 | Week 23 | Week 24 | Week 25 | Week 26 | Week 27 | Week 28 | Week 29 | Week 30 | Week 31 | Week 32 | Week 33 | Week 34 | Week 35 | Week 36 | Week 37 | Week 38 | Week 39 | Week 40 | Week 41 | Week 42 | Week 43 | Week 44 | Week 45 | Week 46 | Week 47 | Week 48 | Week 49 | Week 50 | Week 51 | Week 52 | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | Week 6 | Week 7 | Week 8 | Week 9 | Week 10 | Week 11 | Week 12 | Week 13 | Week 14 | Week 15 | Week 16 | Week 17 | Week 18 | Week 19 | Week 20 | Week 21 | Week 22 | Week 23 | Week 24 | Week 25 | Week 26 | Week 27 | Week 28 | Week 29 | Week 30 | Week 31 | Week 32 | Week 33 | Week 34 | Week 35 | Week 36 | Week 37 | Week 38 | Week 39 | Week 40 | Week 41 | Week 42 | Week 43 | Week 44 | Week 45 | Week 46 | Week 47 | Week 48 | Week 49 | Week 50 | Week 51 | Week 52 | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | Week 6 | Week 7 | Week 8 | Week 9 | Week 10 | Week 11 | Week 12 | Week 13 | Week 14 | Week 15 | Week 16 | Week 17 | Week 18 | Week 19 | Week 20 | Week 21 | Week 22 | Week 23 | Week 24 | Week 25 | Week 26 | Week 27 | Week 28 | Week 29 | Week 30 | Week 31 | Week 32 | Week 33 | Week 34 | Week 35 | Week 36 | Week 37 | Week 38 | Week 39 | Week 40 | Week 41 | Week 42 | Week 43 | Week 44 | Week 45 | Week 46 | Week 47 | Week 48 | Week 49 | Week 50 | Week 51 | Week 52
 | 2022-01-01 00:00:00 | 2022-01-08 00:00:00 | 2022-01-15 00:00:00 | 2022-01-22 00:00:00 | 2022-01-29 00:00:00 | 2022-02-05 00:00:00 | 2022-02-12 00:00:00 | 2022-02-19 00:00:00 | 2022-02-26 00:00:00 | 2022-03-05 00:00:00 | 2022-03-12 00:00:00 | 2022-03-19 00:00:00 | 2022-03-26 00:00:00 | 2022-04-02 00:00:00 | 2022-04-09 00:00:00 | 2022-04-16 00:00:00 | 2022-04-23 00:00:00 | 2022-04-30 00:00:00 | 2022-05-07 00:00:00 | 2022-05-14 00:00:00 | 2022-05-21 00:00:00 | 2022-05-28 00:00:00 | 2022-06-04 00:00:00 | 2022-06-11 00:00:00 | 2022-06-18 00:00:00 | 2022-06-25 00:00:00 | 2022-07-02 00:00:00 | 2022-07-09 00:00:00 | 2022-07-16 00:00:00 | 2022-07-23 00:00:00 | 2022-07-30 00:00:00 | 2022-08-06 00:00:00 | 2022-08-13 00:00:00 | 2022-08-20 00:00:00 | 2022-08-27 00:00:00 | 2022-09-03 00:00:00 | 2022-09-10 00:00:00 | 2022-09-17 00:00:00 | 2022-09-24 00:00:00 | 2022-10-01 00:00:00 | 2022-10-08 00:00:00 | 2022-10-15 00:00:00 | 2022-10-22 00:00:00 | 2022-10-29 00:00:00 | 2022-11-05 00:00:00 | 2022-11-12 00:00:00 | 2022-11-19 00:00:00 | 2022-11-26 00:00:00 | 2022-12-03 00:00:00 | 2022-12-10 00:00:00 | 2022-12-17 00:00:00 | 2022-12-24 00:00:00 | 2023-01-01 00:00:00 | 2023-01-08 00:00:00 | 2023-01-15 00:00:00 | 2023-01-22 00:00:00 | 2023-01-29 00:00:00 | 2023-02-05 00:00:00 | 2023-02-12 00:00:00 | 2023-02-19 00:00:00 | 2023-02-26 00:00:00 | 2023-03-05 00:00:00 | 2023-03-12 00:00:00 | 2023-03-19 00:00:00 | 2023-03-26 00:00:00 | 2023-04-02 00:00:00 | 2023-04-09 00:00:00 | 2023-04-16 00:00:00 | 2023-04-23 00:00:00 | 2023-04-30 00:00:00 | 2023-05-07 00:00:00 | 2023-05-14 00:00:00 | 2023-05-21 00:00:00 | 2023-05-28 00:00:00 | 2023-06-04 00:00:00 | 2023-06-11 00:00:00 | 2023-06-18 00:00:00 | 2023-06-25 00:00:00 | 2023-07-02 00:00:00 | 2023-07-09 00:00:00 | 2023-07-16 00:00:00 | 2023-07-23 00:00:00 | 2023-07-30 00:00:00 | 2023-08-06 00:00:00 | 2023-08-13 00:00:00 | 2023-08-20 00:00:00 | 2023-08-27 00:00:00 | 2023-09-03 00:00:00 | 2023-09-10 00:00:00 | 2023-09-17 00:00:00 | 2023-09-24 00:00:00 | 2023-10-01 00:00:00 | 2023-10-08 00:00:00 | 2023-10-15 00:00:00 | 2023-10-22 00:00:00 | 2023-10-29 00:00:00 | 2023-11-05 00:00:00 | 2023-11-12 00:00:00 | 2023-11-19 00:00:00 | 2023-11-26 00:00:00 | 2023-12-03 00:00:00 | 2023-12-10 00:00:00 | 2023-12-17 00:00:00 | 2023-12-24 00:00:00 | 2024-01-01 00:00:00 | 2024-01-08 00:00:00 | 2024-01-15 00:00:00 | 2024-01-22 00:00:00 | 2024-01-29 00:00:00 | 2024-02-05 00:00:00 | 2024-02-12 00:00:00 | 2024-02-19 00:00:00 | 2024-02-26 00:00:00 | 2024-03-04 00:00:00 | 2024-03-11 00:00:00 | 2024-03-18 00:00:00 | 2024-03-25 00:00:00 | 2024-04-01 00:00:00 | 2024-04-08 00:00:00 | 2024-04-15 00:00:00 | 2024-04-22 00:00:00 | 2024-04-29 00:00:00 | 2024-05-06 00:00:00 | 2024-05-13 00:00:00 | 2024-05-20 00:00:00 | 2024-05-27 00:00:00 | 2024-06-03 00:00:00 | 2024-06-10 00:00:00 | 2024-06-17 00:00:00 | 2024-06-24 00:00:00 | 2024-07-01 00:00:00 | 2024-07-08 00:00:00 | 2024-07-15 00:00:00 | 2024-07-22 00:00:00 | 2024-07-29 00:00:00 | 2024-08-05 00:00:00 | 2024-08-12 00:00:00 | 2024-08-19 00:00:00 | 2024-08-26 00:00:00 | 2024-09-02 00:00:00 | 2024-09-09 00:00:00 | 2024-09-16 00:00:00 | 2024-09-23 00:00:00 | 2024-09-30 00:00:00 | 2024-10-07 00:00:00 | 2024-10-14 00:00:00 | 2024-10-21 00:00:00 | 2024-10-28 00:00:00 | 2024-11-04 00:00:00 | 2024-11-11 00:00:00 | 2024-11-18 00:00:00 | 2024-11-25 00:00:00 | 2024-12-02 00:00:00 | 2024-12-09 00:00:00 | 2024-12-16 00:00:00 | 2024-12-23 00:00:00 | 2025-01-01 00:00:00 | 2025-01-08 00:00:00 | 2025-01-15 00:00:00 | 2025-01-22 00:00:00 | 2025-01-29 00:00:00 | 2025-02-05 00:00:00 | 2025-02-12 00:00:00 | 2025-02-19 00:00:00 | 2025-02-26 00:00:00 | 2025-03-05 00:00:00 | 2025-03-12 00:00:00 | 2025-03-19 00:00:00 | 2025-03-26 00:00:00 | 2025-04-02 00:00:00 | 2025-04-09 00:00:00 | 2025-04-16 00:00:00 | 2025-04-23 00:00:00 | 2025-04-30 00:00:00 | 2025-05-07 00:00:00 | 2025-05-14 00:00:00 | 2025-05-21 00:00:00 | 2025-05-28 00:00:00 | 2025-06-04 00:00:00 | 2025-06-11 00:00:00 | 2025-06-18 00:00:00 | 2025-06-25 00:00:00 | 2025-07-02 00:00:00 | 2025-07-09 00:00:00 | 2025-07-16 00:00:00 | 2025-07-23 00:00:00 | 2025-07-30 00:00:00 | 2025-08-06 00:00:00 | 2025-08-13 00:00:00 | 2025-08-20 00:00:00 | 2025-08-27 00:00:00 | 2025-09-03 00:00:00 | 2025-09-10 00:00:00 | 2025-09-17 00:00:00 | 2025-09-24 00:00:00 | 2025-10-01 00:00:00 | 2025-10-08 00:00:00 | 2025-10-15 00:00:00 | 2025-10-22 00:00:00 | 2025-10-29 00:00:00 | 2025-11-05 00:00:00 | 2025-11-12 00:00:00 | 2025-11-19 00:00:00 | 2025-11-26 00:00:00 | 2025-12-03 00:00:00 | 2025-12-10 00:00:00 | 2025-12-17 00:00:00 | 2025-12-24 00:00:00 | 2026-01-01 00:00:00 | 2026-01-08 00:00:00 | 2026-01-15 00:00:00 | 2026-01-22 00:00:00 | 2026-01-29 00:00:00 | 2026-02-05 00:00:00 | 2026-02-12 00:00:00 | 2026-02-19 00:00:00 | 2026-02-26 00:00:00 | 2026-03-05 00:00:00 | 2026-03-12 00:00:00 | 2026-03-19 00:00:00 | 2026-03-26 00:00:00 | 2026-04-02 00:00:00 | 2026-04-09 00:00:00 | 2026-04-16 00:00:00 | 2026-04-23 00:00:00 | 2026-04-30 00:00:00 | 2026-05-07 00:00:00 | 2026-05-14 00:00:00 | 2026-05-21 00:00:00 | 2026-05-28 00:00:00 | 2026-06-04 00:00:00 | 2026-06-11 00:00:00 | 2026-06-18 00:00:00 | 2026-06-25 00:00:00 | 2026-07-02 00:00:00 | 2026-07-09 00:00:00 | 2026-07-16 00:00:00 | 2026-07-23 00:00:00 | 2026-07-30 00:00:00 | 2026-08-06 00:00:00 | 2026-08-13 00:00:00 | 2026-08-20 00:00:00 | 2026-08-27 00:00:00 | 2026-09-03 00:00:00 | 2026-09-10 00:00:00 | 2026-09-17 00:00:00 | 2026-09-24 00:00:00 | 2026-10-01 00:00:00 | 2026-10-08 00:00:00 | 2026-10-15 00:00:00 | 2026-10-22 00:00:00 | 2026-10-29 00:00:00 | 2026-11-05 00:00:00 | 2026-11-12 00:00:00 | 2026-11-19 00:00:00 | 2026-11-26 00:00:00 | 2026-12-03 00:00:00 | 2026-12-10 00:00:00 | 2026-12-17 00:00:00 | 2026-12-24 00:00:00 | Yr 5
[NAME] cost | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -270 | -283 | -296 | -310 | -323 | -337 | -350 | -364 | -377 | -391 | -404 | -418 | -431 | -445 | -458 | -472 | -485 | -1213 | -1324 | -1436 | -1548 | -1659 | -1771 | -1883 | -1994 | -2452 | -2633 | -2814 | -2995 | -3176 | -3357 | -3538 | -3719 | -3900 | -4081 | -4262 | -4443 | -4624 | -4805 | -4986 | -5167 | -5348 | -5529 | -5710 | -5891 | -6071 | -6252 | -6433 | -6614 | -6795 | -6976 | -7157 | -7338 | -7519 | -7700 | -7881 | -8062 | -8243 | -8424 | -8605 | -8786 | -8967 | -9148 | -9329 | -9510 | -9690 | -9871 | -10052 | -10233 | -19483 | -20059 | -20634 | -21210 | -21785 | -22361 | -22936 | -23512 | -24088 | -24663 | -25239 | -25814 | -26390 | -26965 | -27541 | -28117 | -28692 | -29268 | -29843 | -30419 | -30994 | -31570 | -32146 | -32721 | -33297 | -33872 | -34448 | -35023 | -35599 | -36175 | -36750 | -37326 | -37901 | -38477 | -39052 | -39628 | -40204 | -40779 | -41355 | -41930 | -42506 | -43082 | -43657 | -44233 | -44808 | -45384 | -45959 | -46535 | -47111 | -47686 | -48262 | -48837 | -53700 | -54325 | -54951 | -55577 | -56202 | -56828 | -57454 | -58079 | -58705 | -59330 | -59956 | -60582 | -61207 | -61833 | -62459 | -63084 | -63710 | -64335 | -64961 | -65587 | -66212 | -66838 | -67464 | -68089 | -68715 | -69340 | -69966 | -70592 | -71217 | -71843 | -72469 | -73094 | -73720 | -74345 | -74971 | -75597 | -76222 | -76848 | -77474 | -78099 | -78725 | -79350 | -79976 | -80602 | -81227 | -81853 | -82479 | -83104 | -83730 | -84355 | -84981 | -85607 | -93197 | -93873 | -94548 | -95224 | -95900 | -96575 | -97251 | -97927 | -98602 | -99278 | -99954 | -100629 | -101305 | -101981 | -102656 | -103332 | -104008 | -104683 | -105359 | -106035 | -106710 | -107386 | -108062 | -108737 | -109413 | -110089 | -110765 | -111440 | -112116 | -112792 | -113467 | -114143 | -114819 | -115494 | -116170 | -116846 | -117521 | -118197 | -118873 | -119548 | -120224 | -120900 | -121575 | -122251 | -122927 | -123602 | -124278 | -124954 | -125629 | -126305 | -126981 | -127656
Inventory Purchase date
 |  | Yr 1 | Yr 2 | Yr 3 | Yr 4 | Yr 5
Materials Purchase Lead Time (weeks) |  | 4 | 4 | 2 | 2 | 2
Credit Terms (weeks) |  | 1 | 4 | 6 | 7 | 8
Weeks worth of stock to purchase (weeks) |  | 16 | 8 | 6 | 4 | 4
 | [NAME] needed to reflect when purchase will take place and the value of this.....
 | So without a formula, I would figure out that the inventory purchase date would be Week 33 initially (Week 36 is when the products are sold, we need to order the materials to manufacture 4 weeks in advance, but we get 1 weeks credit, so we would need to pay 3 weeks before the product is sold....)