I am trying to batch update the inventory sales price in excel by multiplying by the average inventory cost. However, while the average inventory cost is listed on the “inventory items” page in manager, the information isn’t translated to the excel spreadsheet during batch update. Is there a way to have this information in this format or do I need to figure out work around with v lookup in excel.
Average cost is not a variable stored in Manager. It is calculated in real time for the display in the Inventory Items tab from the total cost and quantity owned. Both of those are running totals that change with every purchase, sale, write-off, or journal entry referencing the item. That is why the figure is not clickable in the display.
When you do a Batch Update, you can only modify (because you are only presented) the fields in the table you are updating. Those fields are the ones present when looking at the Edit screen for an inventory item. You will notice average cost is not there. In other words, it is not part of the definition of an inventory item.
So, your guess was correct, whether or not you use the VLOOKUP function in Manager:
- Copy to clipboard from the Batch Update window.
- Paste into spreadsheet.
- Copy the tab listing to another spreadsheet or portion of the same spreadsheet, capturing the average costs. Use those to calculate new prices.
- Copy the values of new prices back into the sales price column of the spreadsheet from step 2.
- Paste the spreadsheet back into the Batch Update window.
Be aware as you do this that you cannot use formulas for Batch Update. You must paste actual values into modification spreadsheet.
Thanks so much. Manager is amazing software