Xing Duong
New Member
- Joined
- Feb 2, 2021
- Messages
- 3
- Office Version
- 2019
- Platform
- Windows
Hello everyone,
I am facing the challenging and would like to ask your professional help to overcome this one. Hereunder is the detail from my attached file.
Sheet "On-Hand": The Data in column B & D are what I want to auto update in sheet "Order Form" column A & B
The first time update, I have to manual categorize & record unit per case for each item in
Anytime, when I copy & paste new data in sheet "On-Hand" (which may have more items (new) and/or less items (removed) in compared to last data list) then sheet "Order Form" can be able to detect them and auto update, new items will be in the new rows while removed item rows will be deleted without touching the category & unit per case of the existed items I recorded.
Really appreciate your help
Sheet "On Hand":
Sheet "Order Form"
I am facing the challenging and would like to ask your professional help to overcome this one. Hereunder is the detail from my attached file.
Sheet "On-Hand": The Data in column B & D are what I want to auto update in sheet "Order Form" column A & B
The first time update, I have to manual categorize & record unit per case for each item in
Anytime, when I copy & paste new data in sheet "On-Hand" (which may have more items (new) and/or less items (removed) in compared to last data list) then sheet "Order Form" can be able to detect them and auto update, new items will be in the new rows while removed item rows will be deleted without touching the category & unit per case of the existed items I recorded.
Really appreciate your help
Sheet "On Hand":
| Form.xlsx | |||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
| 1 | Store Name | Code | GroupName | Description | UOM | Last Stock Take | Price | Opening Stock | Purchase Qty | Transfer In | Transfer Out | Raw Waste | POS Usage | Expected Stock | Closing Stock | Actual Usage | Difference Qty | Stock Variance Cost | Target Yield | Actual Yield | Yield Variance | ||
| 2 | Da Kao | 4 | Food | FRENCH FRIES | KG | Nov 1 2021 3:59AM | 37,788 | 530.76 | 0 | 0 | 0 | 5 | 218.34 | 307.42 | 173 | 352.76 | -134.42 | -5,079,527 | 9.1 | 5.63 | -3.47 | ||
| 3 | Da Kao | 5 | Food | 10:1 BEEF | EA | Nov 1 2021 3:59AM | 8,019 | 5,059.00 | 0 | 0 | 0 | -140 | 1,103.00 | 4,096.00 | 3,170.00 | 2,029.00 | -926 | -7,425,467 | 1 | 0.54 | -0.46 | ||
| 4 | Da Kao | 6 | Food | 4:1 BEEF | EA | Nov 1 2021 3:59AM | 20,641 | 485 | 0 | 0 | 0 | 2 | 180 | 303 | 236 | 247 | -67 | -1,382,975 | 1 | 0.73 | -0.27 | ||
| 5 | Da Kao | 7 | Food | MCCHICKEN PATTY | EA | Nov 1 2021 3:59AM | 8,812 | 1,079.00 | 0 | 0 | 0 | 1 | 250 | 828 | 540 | 538 | -288 | -2,537,942 | 1 | 0.46 | -0.54 | ||
| 6 | Da Kao | 8 | Food | FILET O FISH | EA | Nov 1 2021 3:59AM | 8,531 | 418 | 0 | 0 | 0 | 1 | 105 | 312 | 315 | 102 | 3 | 25,592 | 1 | 1.03 | 0.03 | ||
| 7 | Da Kao | 9 | Food | TARTAR SAUCE | BAG | Nov 1 2021 3:59AM | 54,264 | 9.29 | 0 | 0 | 0 | 0 | 2.66 | 6.63 | 4 | 5.29 | -2.63 | -142,713 | 35 | 17.61 | -17.39 | ||
| 8 | Da Kao | 10 | Food | MCCHICKEN SAUCE | BAG | Nov 1 2021 3:59AM | 36,991 | 20.34 | 0 | 0 | 0 | 0 | 13.59 | 6.76 | 16.25 | 4.09 | 9.49 | 351,044 | 70 | 232.23 | 162.23 | ||
| 9 | Da Kao | 12 | Food | SUNDAE MIX | LTR | Nov 1 2021 3:59AM | 41,598 | 52.13 | 0 | 0 | 0 | 2 | 77.96 | -27.83 | 66 | -15.87 | 93.83 | 3,903,172 | 11.73 | -57.65 | -69.38 | ||
| 10 | Da Kao | 13 | Food | CHEESE SLICES | EA | Nov 1 2021 3:59AM | 1,650 | 11,784.10 | 0 | 0 | 0 | 5 | 1,431.50 | 10,347.60 | 9,280.00 | 2,499.10 | -1,067.60 | -1,761,987 | 2 | 1.15 | -0.85 | ||
| 11 | Da Kao | 14 | Food | ENGLISH MUFFINS | EA | Nov 1 2021 3:59AM | 7,122 | 1,443.00 | 0 | 0 | 0 | 1 | 220 | 1,222.00 | 547 | 895 | -675 | -4,807,062 | 1 | 0.25 | -0.75 | ||
| 12 | Da Kao | 15 | Food | CANADIAN BACON | EA | Nov 1 2021 3:59AM | 5,179 | 1,773.00 | 0 | 0 | 0 | 2 | 108 | 1,663.00 | 1,640.00 | 131 | -23 | -119,117 | 1 | 0.82 | -0.18 | ||
| 13 | Da Kao | 16 | Food | EGGS FRESH | EA | Nov 1 2021 3:59AM | 5,422 | 113 | 0 | 150 | 0 | 24 | 133 | 106 | 41 | 198 | -65 | -352,408 | 1 | 0.67 | -0.33 | ||
| 14 | Da Kao | 18 | Food | LETTUCE | KG | Nov 1 2021 3:59AM | 89,667 | -77.81 | 240 | 3 | 3.46 | 0 | 17.57 | 144.16 | 0 | 161.73 | -144.16 | ######### | 66 | 7.17 | -58.83 | ||
| 15 | Da Kao | 23 | Food | CONE | EA | Nov 1 2021 3:59AM | 1,133 | 1,739.00 | 0 | 0 | 0 | 0 | 369 | 1,370.00 | 1,256.00 | 483 | -114 | -129,154 | 1 | 0.76 | -0.24 | ||
| 16 | Da Kao | 26 | Food | MUSTARD POUCH | KG | Nov 1 2021 3:59AM | 51,638 | 7.1 | 0 | 0 | 0 | 0 | 0.65 | 6.45 | 6.75 | 0.35 | 0.3 | 15,491 | 990 | 1,856.61 | 866.61 | ||
| 17 | Da Kao | 29 | Food | FRY SALT | KG | Nov 1 2021 3:59AM | 31,088 | 24.13 | 0 | 0 | 0 | 0 | 0 | 24.13 | 14.93 | 9.2 | -9.2 | -286,007 | |||||
| 18 | Da Kao | 38520 | Ops Supplies | Breading Gloves G10 Blue M | EA | Nov 1 2021 3:59AM | 3,258 | 4,700.00 | 0 | 0 | 0 | 0 | 0 | 4,700.00 | 500 | 4,200.00 | -4,200.00 | ######### | |||||
| 19 | Da Kao | 43 | Food | SALT PACKET | EA | Nov 1 2021 3:59AM | 88 | 7,112.00 | 0 | 0 | 0 | 0 | 0 | 7,112.00 | 7,000.00 | 112 | -112 | -9,891 | |||||
| 20 | Da Kao | 45 | Food | KETCHUP PACKETS | EA | Nov 1 2021 3:59AM | 488 | 33,650.00 | 0 | 0 | 0 | 0 | 0 | 33,650.00 | 6,300.00 | 27,350.00 | -27,350.00 | ######### | |||||
| 21 | Da Kao | 55 | Food | BIG MAC SAUCE | BAG | Nov 1 2021 3:59AM | 54,536 | 26.75 | 0 | 0 | 0 | 0 | 5.38 | 21.38 | 22.5 | 4.25 | 1.13 | 61,626 | 35 | 44.26 | 9.26 | ||
| 22 | Da Kao | 56 | Food | DEHYDRATED ONIONS | BAG | Nov 1 2021 3:59AM | 46,441 | 1.65 | 0 | 0 | 0 | 0 | 2.31 | -0.66 | 7.75 | -6.1 | 8.41 | 390,569 | 385 | -145.62 | -530.62 | ||
| 23 | Da Kao | 63 | Food | DILL PICKLE SLICES | EA | Nov 1 2021 3:59AM | 266 | 10,897.00 | 0 | 0 | 0 | 0 | 1,277.00 | 9,620.00 | 5,500.00 | 5,397.00 | -4,120.00 | -1,095,243 | 1 | 0.24 | -0.76 | ||
| 24 | Da Kao | 65 | Food | HOTFUDGE TOPPING | BAG | Nov 1 2021 3:59AM | 73,312 | 15.32 | 0 | 0 | 1 | 0 | 2.6 | 11.72 | 9 | 5.32 | -2.72 | -199,410 | 60 | 29.3 | -30.7 | ||
| 25 | Da Kao | 67 | Food | STRAWBERRY TOPPING | BAG | Nov 1 2021 3:59AM | 74,354 | 9.29 | 0 | 0 | 0 | 0 | 2.15 | 7.14 | 5 | 4.29 | -2.14 | -159,118 | 54 | 27.03 | -26.97 | ||
| 26 | Da Kao | 70 | Food | HASH BROWN | EA | Nov 1 2021 3:59AM | 3,221 | 432 | 0 | 0 | 0 | 2 | 140 | 290 | 308 | 122 | 18 | 57,985 | 1 | 1.15 | 0.15 | ||
| 27 | Da Kao | 93 | Food | ROBUSTA ROAST COFFEE/GROUND | BAG | Nov 1 2021 3:59AM | 21,942 | 112.89 | 0 | 0 | 0 | 14 | 14.8 | 84.08 | 89 | 9.89 | 4.92 | 107,956 | 30 | 44.92 | 14.92 | ||
| 28 | Da Kao | 109 | Paper | LID/HOT CUP | EA | Nov 1 2021 3:59AM | 762 | 3,403.00 | 0 | 0 | 0 | 0 | 51 | 3,352.00 | 3,419.00 | -16 | 67 | 51,058 | 1 | -3.19 | -4.19 | ||
| 29 | Da Kao | 116 | Paper | LID/REG | EA | Nov 1 2021 3:59AM | 254 | 5,282.75 | 0 | 0 | 0 | 0 | 286.75 | 4,996.00 | 3,850.00 | 1,432.75 | -1,146.00 | -291,084 | 1.33 | 0.27 | -1.07 | ||
| 30 | Da Kao | 117 | Paper | LID/MEDIUM | EA | Nov 1 2021 3:59AM | 294 | 2,640.00 | 0 | 0 | 0 | 0 | 1,134.00 | 1,506.00 | 1,525.00 | 1,115.00 | 19 | 5,586 | 1 | 1.02 | 0.02 | ||
| 31 | Da Kao | 127 | Paper | FRY BAG REGULAR | EA | Nov 1 2021 3:59AM | 110 | 4,124.00 | 0 | 0 | 0 | 0 | 282 | 3,842.00 | 3,750.00 | 374 | -92 | -10,141 | 1 | 0.75 | -0.25 | ||
| 32 | Da Kao | 128 | Paper | 4 BAG | EA | Nov 1 2021 3:59AM | 324 | 9,732.00 | 0 | 0 | 0 | 0 | 0 | 9,732.00 | 7,500.00 | 2,232.00 | -2,232.00 | -723,168 | |||||
| 33 | Da Kao | 141 | Paper | PLASTIC BAG D SIZE | EA | Nov 1 2021 3:59AM | 228 | 18,200.00 | 0 | 0 | 0 | 0 | 0 | 18,200.00 | 9,695.00 | 8,505.00 | -8,505.00 | -1,940,314 | |||||
On-Hand | |||||||||||||||||||||||
Sheet "Order Form"
| Form.xlsx | ||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | AE | AF | AG | AH | |||||||||||||||||||
| 1 | ||||||||||||||||||||||||||||||||||||
| 2 | Item Code | Item Name | Category | UOM | Unit per case | Unit Usage | Case Usage | Historical Sales | UPT | Order Cycle | Projected Sales | Build-To | System On-Hand | Physical Count | Expected Order Quantity | Revised Order Quantity | Remark on revision | Final Order Quantity | ||||||||||||||||||
| 3 | ||||||||||||||||||||||||||||||||||||
| 4 | ||||||||||||||||||||||||||||||||||||
| 5 | ||||||||||||||||||||||||||||||||||||
| 6 | ||||||||||||||||||||||||||||||||||||
| 7 | ||||||||||||||||||||||||||||||||||||
| 8 | ||||||||||||||||||||||||||||||||||||
| 9 | ||||||||||||||||||||||||||||||||||||
| 10 | ||||||||||||||||||||||||||||||||||||
| 11 | ||||||||||||||||||||||||||||||||||||
| 12 | ||||||||||||||||||||||||||||||||||||
| 13 | ||||||||||||||||||||||||||||||||||||
| 14 | ||||||||||||||||||||||||||||||||||||
| 15 | ||||||||||||||||||||||||||||||||||||
| 16 | ||||||||||||||||||||||||||||||||||||
| 17 | ||||||||||||||||||||||||||||||||||||
| 18 | ||||||||||||||||||||||||||||||||||||
| 19 | ||||||||||||||||||||||||||||||||||||
| 20 | ||||||||||||||||||||||||||||||||||||
| 21 | ||||||||||||||||||||||||||||||||||||
| 22 | ||||||||||||||||||||||||||||||||||||
| 23 | ||||||||||||||||||||||||||||||||||||
| 24 | ||||||||||||||||||||||||||||||||||||
| 25 | ||||||||||||||||||||||||||||||||||||
| 26 | ||||||||||||||||||||||||||||||||||||
| 27 | ||||||||||||||||||||||||||||||||||||
| 28 | ||||||||||||||||||||||||||||||||||||
| 29 | ||||||||||||||||||||||||||||||||||||
| 30 | ||||||||||||||||||||||||||||||||||||
Order Form | ||||||||||||||||||||||||||||||||||||
