If the item in column A is not on this list, then the item is a location. Each time the program finds something in a cell in column A, it checks it against the list on the InvGroups worksheet. The VBA macro runs through each cell in column A on the Inv-Val-Import worksheet. InvGroups: This page just referenced the Location Group names. Once selected, the macro will run down through the Inventory Valuation Summary report on the Inv-Val-Import worksheet and convert it row by row. We wrote a macro that would be run when the user selected the Run Transform button at the right. Inv-By-PN: This is where our flat file result will be saved. Inv-Val-Import: The entire Inventory Valuation Summary report is pasted onto this worksheet, beginning at cell A1. But in this instance, since we had the report we decided to just create a VBA macro that would parse the Inventory Valuation Report and transform the data into the desired flat file format. We use this approach to summarize shipment data, which cannot be gleaned easily from a standard report. Getting More Value From Standard Reports Using Visual BasicĪs mentioned earlier, Fishbowl does provide us with the ability to run SQL queries on the live database and generate a very precise report. So you can see the challenge with converting this to a flat file. The report itself was more than 1,900 rows in Excel. But in this the example, the Fishbowl client had four Location Groups, 485 active Locations and 577 Part Numbers, totaling more than $2.5M of inventory value. If we only had one Location Group with a small number of Locations, we could easily parse this report just cutting and pasting by hand and get the desired result. Next, you cannot easily distinguish Location Groups from Locations given that they are in the same column. For one, the Location is not on the same row as each Part Number. If you look at the example above as compared to how we want the information summarized, you can begin to see the problem. What we want is to have the data in the following flat file format to import into our data warehouse, by column: The Torrance Location Group has hundreds of Locations in this example. Within the Gardena Location Group we have three locations-Packaging, Receiving and Stock. In the snippet above you can see that we have two Location Groups-Gardena and Torrance. Here is an example of a CSV export to Excel to better illustrate the challenge: But if you look at the report, it does not lend itself to being easily transformed into a flat file format with all the Location Group and Location information. The Challenge With Using A Standard ReportĪt month end, we capture the values in this report to help with analyzing inventory efficiencies. When variances do arise, you can look at the daily inventory asset balance in QuickBooks and compare that to the daily Fishbowl Inventory Valuation Summary and root cause what transaction(s) are creating the variance. Frequently, this report is used to post an adjusting entry, when needed, to bring the QuickBooks inventory asset balance in alignment with Fishbowl’s representation. One the primary uses of this report is to reconcile total inventory asset value as detailed in a daily Fishbowl Inventory Valuation Summary report vs. Generally, we set this report to run just before midnight on a daily basis. This report can be set up as a Scheduled Task to be generated and distributed on the frequency of your choice (options are Minute, Daily, Weekly, Monthly, Annually) or generated on demand. Then for each part you get the on-hand quantity, unit of measure, unit cost and extended cost (or Asset Value as the column is titled). a warehouse, customer consignment location, etc.), by Location (Location is a subset of a Location Group), by Part Number. This very useful report includes a listing if inventory by Location Group (Location Group is the top level inventory location identifier, e.g. One example of this technique is with the Inventory Valuation Summary report, which you can find in the Reports module as shown below: Transforming the Inventory Valuation Summary Report This approach is especially useful because you can distribute simple macro-enabled Excel workbooks to inventory analysts and others within your company allowing them to generate Fishbowl CSV data on the fly.
#Fishbowl inventory cost how to
Learn how to create CSV data from Fishbowl standard reports using Excel Visual Basic to get more value from your inventory, purchasing and production reports.
0 kommentar(er)
