cancel
Showing results for 
Search instead for 
Did you mean: 
PoolHero1
Level 3

Surplus Inventory Site Transfer

We have two inventory sites.  Both sites have items that need to be reordered.  There is a surplus of some of those items at the other site.  What is the easiest way to identify which items they are and how many to transfer?

 

The stock status by site report identifies which items need to be reordered and their quantities.  It does not have a surplus column.  I can easily export both site reports to excel and create a surplus column.   Unfortunately the items listed in one site report differ from the other.  It's very difficult to match up the reorder qty and surplus qty of each item across both sites.

Solved
Best answer January 08, 2021

Best Answers
PoolHero1
Level 3

Surplus Inventory Site Transfer

Here are the directions for how to create a list of overstocked items that can be transferred to a site that needs them:

 

  1. Open the Inventory Stock Status by Site report.  Select one site from the inventory site drop down box.  We’ll use Texas as an example of a site name.  Change the date range to all.  Export the report to a Excel file and name the sheet “Texas original”. 
  2. Change the site (we’ll use Utah for this example) on the Inventory by Site report and export the report to a new sheet in the previously created Excel file.  Name the sheet “Utah original”.
  3. Create a copy of both sheets and name one Texas and other Utah.
  4. On the Texas sheet, select all of the columns that contain an item name or description by clicking and dragging across them.  Select Merge & Center and then Merge Across.  You may get an error.  Click ok each time it pops up.  Delete the empty columns.  Delete the top rows except for the header row and add a header for the item column.  Delete the column to the right of On Hand.  Repeat this step for Utah. 
  5. Create a new sheet and name it Master List.  Select cell A1.  Click the data tab and click consolidate.  Check the boxes Top row and Left Column.  Click the reference box and then the up arrow.  Open the Texas worksheet and select columns A through D which should be all of the columns with data in them.  Click the down arrow again and then the add radio button.  Click the down arrow and repeat for the Utah sheet.  After clicking add, click ok.  The consolidated list will appear in the Master List Sheet.  Delete columns B, C, and D.
  6. Create a new sheet and name it Texas Merged.  Select cell A1.  Click consolidate and follow the steps above this time selecting and adding the columns A-D on the Texas worksheet and then selecting and adding column A in the Master List worksheet.  Then click ok.  Create a new sheet, name it Utah Merged, and repeat this step.         
  7. In the empty column E on the Texas worksheet, write Order in cell E1, a formula for max minus on hand in E2, and fill the formula down to the last row.  With the column highlighted, format the cells to number and one decimal place.  Repeat this step for the Utah sheet. 
  8. Create a copy of both the Texas Merged and Utah Merged sheets.  Label them Texas Transfer and Utah Transfer respectively.  Copy the Utah Order column and paste the values only to the Texas worksheet.  Copy the Texas Order column and paste the values only to the Utah worksheet.  Write Utah Overstock in the top cell of the Order column in the Texas worksheet.  Write Texas Overstock in the top cell of the Order column in the Utah worksheet. 
  9. Sort by Utah Overstock from smallest to largest.  Delete rows with an Overstock quantity of =>0.  Sort again by Order from largest to smallest.  Delete all of the rows that contain an order quantity of <=0.  Repeat this step for the Utah sheet.     
  10. In the empty column G on the Texas Transfer worksheet, write Transfer from Utah to Texas in the empty cell G1.  Underneath of that in cell G2, create the formula =IF(Order<=Utah Overstock*-1,Order,Utah Overstock*-1) and fill down.  It should look like this: =IF(E2<=F2*-1,E2,F2*-1).  Repeat this for the Utah Transfer sheet.
  11. If you want to separate the item number from the description, add a few columns to the right of column A, select column A, click text to columns, click delimited, next, check other, and add the ( parenthesis symbol in the field to the right.  Click finish.         

 

 

View solution in original post

17 Comments 17
BettyJaneB
QuickBooks Team

Surplus Inventory Site Transfer

I have a report that you can pull, which shows the quantity on hand available per site, @PoolHero1.

 

To start with, when it comes to the Stock Status by Site report that you've pulled up, make sure that the date range is correct. Know that any future dated transactions may affect the quantity of the items on hand. This can be the reason why the report may differ from the other. 

 

Moreover, I'd recommend pulling up the Inventory Valuation Summary by Site so you can see the quantities of the items available per site. This way, you can export it to Excel then compare and match the surplus.

 

To do that:

  1. Click on Report at the top.
  2. Press on Inventory.
  3. Choose Inventory Valuation Summary by Site
  4. Select the Date range.
  5. Tap on the drop-down arrow beside Excel to export the report and choose Create New Worksheet

From there, you can gather the data indicating the surplus of the items per site. You can manually create a report for them.

 

I'm also including this reference to help you in managing your stock valuation in the system, in case you need it in the future: Balance Sheet and Inventory/Stock Valuation reports.

 

Please keep me posted on how this work for you, @ PoolHero1. I'll make sure you're all set. 

PoolHero1
Level 3

Surplus Inventory Site Transfer

Thanks @BettyJaneB 

 

I am unable to tell what the surplus is or what needs to be ordered on that report.  It doesn't display the reorder points or max.  

ReyJohn_D
Moderator

Surplus Inventory Site Transfer

You're welcome, @PoolHero1.

 

Yes, you're right. The Inventory Valuation Summary by Site report won't show the reorder points or surplus of your items. You're on the right track in exporting the Inventory Stock Status by Site report to Excel instead. From there, you can add the surplus column manually.

 

I'd agree that having the said filter or option will make your inventory comparison easy. For now, you can follow Step 5 from my peer's answer above to export the Inventory Stock Status report to Excel. This time, ensure to select the said report and choose the correct date filter to get accurate data.

d774.PNG

By the way, I've got another idea on how to make sure you'll know or gets notified when to reorder your inventories. You can perform some tweaks on the Preferences page to achieve this.

 

Here's how:

 

  1. Click Edit, and then choose Preferences.
  2. Select Reminders, and then go to Company Preferences.
  3. On the Inventory to Reorder option, select the Show List radio-button.
  4. Click OK when you're ready.
    d773.PNG

After turning on this setting, QuickBooks will remind you when to reorder your items moving forward.

 

Also, you can personalize the said reports to get other details that you need. Then, you can memorize them to save a copy for later use.

 

Lastly, I'd recommend checking out our Firm of the Future site to get you in the loop about the latest happenings and product enhancements.

 

Keep me posted if you have additional inventory-related concerns. I'll keep an eye on your reply. Take care!

PoolHero1
Level 3

Surplus Inventory Site Transfer

Thank you @ReyJohn_D 

 

I choose all for the date range when I create the inventory stock status by site.  The items on the report change when I change sites.  In some cases, the item name will be the same but the descriptions will be different from one report to another.  I think the report is pulling the items and their descriptions to populate from previous sales instead of from the item list.

PoolHero1
Level 3

Surplus Inventory Site Transfer

Since the replies have stopped, I'll assume there isn't a solution to this issue.

JasroV
QuickBooks Team

Surplus Inventory Site Transfer

Good day to you, @PoolHero1

 

Allow me to chime in and add some clarification.

 

Yes, you got it right, since you’ve chosen All dates as the date range of the report, it’ll include all the previous sales. Also, it could be that your item before has a different description. Thus, it’s the reason it shows different information.

 

If you don’t want to include the previous sale, I recommend selecting an appropriate date. This way, it’ll only show your current sales and the data you need.

 

To be guided on how to generate reports to accurately show the information you need, you can check out this link for reference: Customize reports in QuickBooks Desktop.

 

In case you have any other concerns or follow-up questions about running your reports in QuickBooks, you’re always welcome to share them with me. I’ll be more than happy to assist you. Have a great day!

PoolHero1
Level 3

Surplus Inventory Site Transfer

Thanks @JasroV 

 

I am unable to customize the report in QB in such a way that I can get the on hand, reorder, and max quantities for both sites to display at the same time.  When I run the report separately, some items are not included on both report making it very difficult to match up the surplus from one site with the reorder from the other.

AlcaeusF
Moderator

Surplus Inventory Site Transfer

Hi there, @PoolHero1.

 

I appreciate you for getting back to us here in the Community. I'm here to help you customize the report in QuickBooks Desktop.


Before you start the customization, please review your items and ensure you enter the on hand, reorder, and max quantities for each site. This way, we can ensure the information will display for both sites in the report.

 

Here's how:

 

  1. Click the Lists tab at the top menu bar.
  2. Select Item List.
  3. Double-click the item.
  4. Choose Inventory Site Info.
  5. Review and make the necessary changes.
  6. Hit OK, then click OK again. 

Once done, please proceed with running the Inventory Stock Status by Site report. Please follow these steps:

 

  1. Click the Reports tab at the top menu bar.
  2. Hover your cursor to Inventory.
  3. Select Inventory Stock Status by Site.
  4. Choose the date range.
  5. Press the Inventory Site drop-down, then click Multiple Inventory Sites.
  6. Add the sites. 
  7. Click OK.
  8. Review the report details. 

Additionally, I recommend visiting the following article to export reports to Excel: Export reports as Excel workbooks in QuickBooks Desktop.

 

Feel free to hit that Reply button if you have additional questions about the report. Have a great day ahead.

PoolHero1
Level 3

Surplus Inventory Site Transfer

Good Morning @AlcaeusF 

 

Do you know how to compare the reorder and max quantities for both sites and all the items at the same time?  We want the those quantities to be the same for each site.  It would be very time consuming to view and then edit each individual item on the item list.

 

I did however check one of the items that is showing up on the report for one site but not the other.  That item has a zero reorder and max quantity for both sites. 

 

After running the report for multiple sites as you've described, some items are listed in one site but not the other.  Without the lists being identical, I'm unable to export the two sites and combine the lists to calculate the transfer quantities.

Tori B
QuickBooks Team

Surplus Inventory Site Transfer

Hey there, @PoolHero1.

 

Thanks for following up with us. 

 

At this time, the best report option would be to follow the steps provided by my colleague above. However, there are some third-party apps you could look into that will offer a wider variety of report management. I've included the link to our supported third-party apps below. 

 

On the other hand, I can see how having a better report for your concerns would be beneficial for you and your business. I've submitted a feedback request to our product development team, so this could be considered in a future update. 

 

Please let me know if you have additional questions or concerns. I'll be here every step of the way. You can reach out to the Community or me at any time. Take care and have a Merry Christmas! 

PoolHero1
Level 3

Surplus Inventory Site Transfer

Thank you @Tori B 

 

Can you recommend a third party app as a solution?

PoolHero1
Level 3

Surplus Inventory Site Transfer

Is anyone else transferring extra inventory from one site to another?  If so, how are you getting a list together of what to transfer?

sberti
Moderator

Surplus Inventory Site Transfer

Hello @PoolHero1. Happy New Year!

 

While we cannot recommend a specific 3rd Party App, the link that @Tori B provided for the list of 3rd Party Apps will help you choose one that best fits your needs!

 

If you have any other inquiries please feel free to reach back out to us here in the Community.

 

Other Community users are encouraged to share their solutions and suggestions for a 3rd party app that has helped them accomplish transferring data and/or ways to put together a list of what to transfer!

PoolHero1
Level 3

Surplus Inventory Site Transfer

Thanks @sberti 

 

The list of 3rd party apps is extensive and not easily searchable unfortunately. 

PoolHero1
Level 3

Surplus Inventory Site Transfer

Hi @sberti 

 

The list of 3rd party apps is extensive and not easily searchable unfortunately. 

PoolHero1
Level 3

Surplus Inventory Site Transfer

It seems as though there isn't a solution for this issue within QB or the third party apps although I haven't checked them all.  I'm going to look for a solution within excel. 

PoolHero1
Level 3

Surplus Inventory Site Transfer

Here are the directions for how to create a list of overstocked items that can be transferred to a site that needs them:

 

  1. Open the Inventory Stock Status by Site report.  Select one site from the inventory site drop down box.  We’ll use Texas as an example of a site name.  Change the date range to all.  Export the report to a Excel file and name the sheet “Texas original”. 
  2. Change the site (we’ll use Utah for this example) on the Inventory by Site report and export the report to a new sheet in the previously created Excel file.  Name the sheet “Utah original”.
  3. Create a copy of both sheets and name one Texas and other Utah.
  4. On the Texas sheet, select all of the columns that contain an item name or description by clicking and dragging across them.  Select Merge & Center and then Merge Across.  You may get an error.  Click ok each time it pops up.  Delete the empty columns.  Delete the top rows except for the header row and add a header for the item column.  Delete the column to the right of On Hand.  Repeat this step for Utah. 
  5. Create a new sheet and name it Master List.  Select cell A1.  Click the data tab and click consolidate.  Check the boxes Top row and Left Column.  Click the reference box and then the up arrow.  Open the Texas worksheet and select columns A through D which should be all of the columns with data in them.  Click the down arrow again and then the add radio button.  Click the down arrow and repeat for the Utah sheet.  After clicking add, click ok.  The consolidated list will appear in the Master List Sheet.  Delete columns B, C, and D.
  6. Create a new sheet and name it Texas Merged.  Select cell A1.  Click consolidate and follow the steps above this time selecting and adding the columns A-D on the Texas worksheet and then selecting and adding column A in the Master List worksheet.  Then click ok.  Create a new sheet, name it Utah Merged, and repeat this step.         
  7. In the empty column E on the Texas worksheet, write Order in cell E1, a formula for max minus on hand in E2, and fill the formula down to the last row.  With the column highlighted, format the cells to number and one decimal place.  Repeat this step for the Utah sheet. 
  8. Create a copy of both the Texas Merged and Utah Merged sheets.  Label them Texas Transfer and Utah Transfer respectively.  Copy the Utah Order column and paste the values only to the Texas worksheet.  Copy the Texas Order column and paste the values only to the Utah worksheet.  Write Utah Overstock in the top cell of the Order column in the Texas worksheet.  Write Texas Overstock in the top cell of the Order column in the Utah worksheet. 
  9. Sort by Utah Overstock from smallest to largest.  Delete rows with an Overstock quantity of =>0.  Sort again by Order from largest to smallest.  Delete all of the rows that contain an order quantity of <=0.  Repeat this step for the Utah sheet.     
  10. In the empty column G on the Texas Transfer worksheet, write Transfer from Utah to Texas in the empty cell G1.  Underneath of that in cell G2, create the formula =IF(Order<=Utah Overstock*-1,Order,Utah Overstock*-1) and fill down.  It should look like this: =IF(E2<=F2*-1,E2,F2*-1).  Repeat this for the Utah Transfer sheet.
  11. If you want to separate the item number from the description, add a few columns to the right of column A, select column A, click text to columns, click delimited, next, check other, and add the ( parenthesis symbol in the field to the right.  Click finish.         

 

 

Need to get in touch?

Contact us