Need help with excel formulas
- Thread starter Jevin
- Start date
Jevin
Chameleon Enthusiast
Well, I'm managing to slowly figure it out through trial and error. Having to use more complex coding than I have used before in excel so it's a bit of a learning curve. Gotten it to grab the first upc now in the array formula, just need to get it to grab more than just that one now. I think an if statement with vlookup might solve the issue. All of this is to make ordering easier at my work. Currently we have a ordering system at work that was called auto order when first introduced but has since been renamed computer assisted ordering and this is at a safeway liquor store. I came from 7-11 and have five years of ordering experience and 1 year of experience with the auto ordering system they implemented which worked almost flawlessly. My hope is that with this spreadsheet I'm building is that it will not only utilize the forecast info from the computer assisted ordering system, but also inventory of items set to auto order.
I took this upon myself as the computer assisted ordering does not forecast sales for every product that is set to auto order, as well as basing it's order on what the last order was, which means that it will regularly cause out of stocks as an item won't necessarily be included on the previous order and it won't add it to the new one. And since it does not take into account inventory on hand, it will regularly overstock some items as well. It is honestly a pitiful excuse of an automated system, even in the loosest definition of the term. We still have to manually go through the orders and change them to appropriate levels. Add on top of this, the upc or description, sometimes both, for a product sometimes varies from the inventory report and the forecast report.
The premise of what I want to do is to have a spreadsheet that you copy and paste the inventory and forecast report from the computer assisted ordering system and my spreadsheet will pull all the unique upc and product descriptions from both and eliminate any duplicates. It will follow the premise of 'forecasted sales for seven days' + 'minimum' - 'quantity on hand' = 'order'. For minimum, it will set it to the amount we will sell from the day the order needs to be submitted to the day we receive the order, unless the item is going on sale, in hich. case it will set the minimum to the shelf capacity for the product. It will also use the forecasted sales report values as the forecasted sales for 7 days in said formula and if the system has not forecasted for a product it will assume that we will sell at minimum 1 unit for the 7 day period. The final step is for it to them use the inventory report to fill in the quantity on hand for the products and from there use F+M-QOH=O. And lastly on a separate sheet, compile the order quantities for the products with the upc barcode as a list than can be printed off and scanned to make the order itself more accurate and efficient.
I took this upon myself as the computer assisted ordering does not forecast sales for every product that is set to auto order, as well as basing it's order on what the last order was, which means that it will regularly cause out of stocks as an item won't necessarily be included on the previous order and it won't add it to the new one. And since it does not take into account inventory on hand, it will regularly overstock some items as well. It is honestly a pitiful excuse of an automated system, even in the loosest definition of the term. We still have to manually go through the orders and change them to appropriate levels. Add on top of this, the upc or description, sometimes both, for a product sometimes varies from the inventory report and the forecast report.
The premise of what I want to do is to have a spreadsheet that you copy and paste the inventory and forecast report from the computer assisted ordering system and my spreadsheet will pull all the unique upc and product descriptions from both and eliminate any duplicates. It will follow the premise of 'forecasted sales for seven days' + 'minimum' - 'quantity on hand' = 'order'. For minimum, it will set it to the amount we will sell from the day the order needs to be submitted to the day we receive the order, unless the item is going on sale, in hich. case it will set the minimum to the shelf capacity for the product. It will also use the forecasted sales report values as the forecasted sales for 7 days in said formula and if the system has not forecasted for a product it will assume that we will sell at minimum 1 unit for the 7 day period. The final step is for it to them use the inventory report to fill in the quantity on hand for the products and from there use F+M-QOH=O. And lastly on a separate sheet, compile the order quantities for the products with the upc barcode as a list than can be printed off and scanned to make the order itself more accurate and efficient.
