AGS Completed Special Projects
Growers are challenged today more than ever to manage labor, inventory, process data, and control costs.
Our AGS team has years of experience completing a plethora of custom projects for clients enabling growers to gain a competitive advantage.
Here are a few of the variety of larger projects and implementations we have accomplished for clients.
Sample custom wishlist
Copyright Advanced Grower Solutions
Shipping Rack Scanning and Rack Manifest Generation
Description
A large greenhouse grower needed a way to provide their customers with printed manifests of what was on each shipped rack of multiple rack loads for the greenhouse plants delivered to them. The growers’ customers needed to speed order receipt processing and finding plants on the delivered racks. The grower needed a way to ‘prove’ items on the order were indeed delivered in order to reduce chargeback or credit requests from customers in cases where they just couldn’t find the items.
Project
We built a scanning and printing system leveraging their existing legacy database and fulfillment system whereby they would use Windows Embedded Based scanners to scan UPC bar codes on the items on the racks and generate a rack specific barcoded manifest that was recorded then printed on portable printers to include on each rack with the shipments.
Technology
Windows Embedded, SQL Server, .Net
Results
Grower was able to significantly reduce credit requests in the first year, more than paying for the equipment and development of the system.
Store Replenishment Order Generation
Description
A large supplier of annuals and perennials to big box retailers used a back-end ERP system that made large (many items per) order writing very manually intensive, slow, and cumbersome. There was no juxtaposition of sales metrics or availability of items with order plans for a given store need. Replenishment agents spent hours manually building spreadsheets to gather necessary data together and many more hours manually creating orders to be loaded and shipped. The grower needed a way to improve data visibility and assembly for the replenishment team and a way to speed up the creation of the orders.
Project
We built a replenishment order data system that used their existing ERP data, coupled with integrating retail statistics and sales data from their customers and finally, we folded in what items the grower had ready to ship.
This combined data set was output in an automatically generated Excel file where the replenishment agents could work by store, or SKU by SKU seeing co-located relevant statistics, inventory levels, ready to sell, total amount of racks booked for a given store so far and remaining inventory among many other metrics. The automatically generated Excel file included a suggested order quantity based on the grower’s own methodology for replenishment calculation. The replenishment agents could override that suggested order quantity or allow it to flow through. Once the file adjustments in the Excel file were complete and saved, the replenishment agent would run a process to generate the order file to be uploaded to the ERP system from that Excel file.
Technology
Python and Python Data Stack, SQLite and SQL Server Databases, Excel
Results
The replenishment team was able to fulfill order quantity the next busy season with an estimated labor savings of between 2 and 3 FTE. On given days the system saved replenishment agents between 3 and 8 hours of manual tedious labor allowing them to focus on the store / SKU situation and the most effective movement of product.
Replenishment Warehouse Mapping File Generation
Description
A multi-facility grower supplied to several big box retailers used a third party retail sales analytics platform for their replenishment statistics. The 3rd party platform required what was referred to as a warehouse map file which mapped which growing facility sent given SKU to given stores. This particular grower used several growing facilities and served several hundred retail stores. The number of facilities, SKU and stores yielded a configuration file that was over 250,000 rows. As the grower made changes to SKU or optimizing growing location or store delivery plans this file had to be manually updated. It took the staff many hours to rebuild this file as changes in the business occurred and as you can imagine it was error prone.
Project
We built a tool that would read the growers SKU list and take in a higher level configuration file which mapped category level to store/facility. The sales and operations teams could easily update this file. In this manner they only had to edit a few lines for changes. Then with our automation tool a new full warehouse map file was generated and reloaded into their third party system.
Technology
Python and Python Data Stack, SQLite, Excel
Results
We were able to generate in a few seconds what was taking some of the staff many hours to rebuild.
Automated Merchandiser Store Order Delivery Notifications
Description
A merchandising company specializing in merchandising live plants for big box retailers needed a way to get regular updates on supplier delivery notices to their individual employees for the stores they were assigned. They needed regular, timely updates to plan their work and travel better – thereby helping them improve their weekly budget results.
Project
We worked with the merchandising company and one of the key suppliers in their markets to build a system that surveyed pending orders in the grower ERP system, built individualized HTML emails with the rolled up order volume. The emails showed a rolling 3 day view of the by-day order total for each store the individual was assigned to. The system sent updates via email twice per day, once at 5:30 AM local time and one at 6:00 PM local time (if orders were scheduled) to each employee. Thus, as pending orders were changed or canceled the merchandisers were notified and could update their daily plans. This allowed the merchandising team to stay in sync with the order change flow much more efficiently and improve their daily store and route planning.
Technology
Python and Python Data Stack, SQLite and SQL Server Databases, Email
Results
The merchandising team experienced more efficient store hours planning and better visibility into demand based on order volume vs planned budget and all on their phone email so they had access to the updated data wherever they were. They were able to coordinate changes much faster and the overall efficiency of the organization improved.
Accounting Journal Data Import from External Sources
Description
The accounting team for a large US grower spent a large amount of time manually creating journal entries for certain externally originated transactions surrounding payroll, benefits and other allocated transaction types which needed to be loaded into the accounting system for creation of financial reports.
Project
We worked with the accounting team to define the data needed and built Excel spreadsheet templates with the correct data definition so that the accounting personnel could easy import the necessary data in its native format. We added Excel macros that would build the resultant journal entry imports formats which then easily went into their back end ERP system.
Technology
Excel and VBA macros
Results
Each month the accounting team saved multiple 10’s of staff hours and improved accuracy with these data transformation tools and imports.
Automated Purchase Order Generation from Production Plan
Description
The procurement team for an annuals and perennials grower needed a way to speed the creation of purchase orders for raw materials from large numbers of production plan entries. The team members were spending hours and hours each week manually copying and entering data into a different system for PO’s.
Project
We worked with their legacy planning tool and wrote a system that would read the given lines of the production plan that were marked ready. The procurement member would identify which vendor to use. The program would scan the database records, correlate the needed raw material in the quantity need for the plan item and build the necessary data set for the purchase order import into their back-end ERP software.
Technology
Python and the Python Data Stack, SQL Server, Excel
Results
The procurement team members saved between 5 and 20 hours per week in the busy season on manually entering purchase order data also improving accuracy of the entered orders.
Integration of Payroll Data For Key Metrics And Dashboards
Description
A US based grower needed a way to integrate data from their payroll system to feed key metric calculations for efficiency and labor spend compared to budgets and keep their dashboards updates. They had a large number of employees and some employees regularly worked multiple departments making the data sizes large and thus almost impossible to do manually.
Project
We built an ETL (extract, transform, and load) system that would allow a defined accounting rep to download the key payroll datafile and process it into an anonymized data set with additional key fields added and calculated. This anonymization process masked the individual identity but kept the key financial, department, and other key dimensional data needed. This data was then pushed into their internal data warehouse database system automatically. From there it was read by their dashboard reporting system. We built several dashboards to show the key labor metrics, efficiency measures, and trends.
Technology
SQL Server, Python and Python Data Stack Tools, PowerBI
Results
The system could do in just 1-2 minutes what previously took hours of manual work by staff. Every payroll could now be processed, and the dashboards be updated showing current data and allow faster decision making by the department managers.
Disaggregate Annual Sales Forecast – Breakout To Store SKU Level By Week
Description
A grower with a large sales plan for big box retailers worked with their customers and ended with a SKU volume plan for the coming year. They needed a way to break this out (disaggregate) to store / week level to build a production plan from this aggregated plan.
Project
We built a system that would read and calculate the sales ratios for stores and SKU from previous years. Then these weights were used to disaggregate the new forecast back to store/SKU/week level to aid in production planning. We provided output in excel to be reviewed. And once review and adjustments were made, we imported data into their planning system.
Technology
Excel, Python and Python Data Stack, SQL Server
Results
In working with the sales team, we were able to refine the process and create the detailed forecast level in less than 10 minutes. This is a process that would have taken experienced sales reps 10s of hours to accomplish doing manual calculations to disaggregate the plan. Additionally we saved them hundreds of hours of manual plan data entry by the import.
Automated Manufacturing ERP Software Routing Record Generation
Description
A US grower using a big name back-end ERP system realized that the creation of the manufacturing routing records for thousands of items across many manufacturing sites would be excessively time prohibitive. They needed these manufacturing routing records to accurately reflect certain costing factors applied to the manufactured item as well as to properly handle certain aspects of duration of manufacturing including the growing time of the plant.
Project
We worked with their ERP provider and their operations team. We built a system that used a template engine for the types of database records necessary for manufacturing routing record generation. We built it to read information about the item and then calculate the necessary data value to fill in the record template. After the items were processed the program built an output format compatible with their back end ERP system to import the generated routing records.
Technology
SQL Server, Python and Python Data Stack tools
Results
This client has had over 1.1 million routing records generated for them, saving thousands of manual hours. We subsequently used this for the same client to regularly scan for any item changes and regenerate the routing cost factors if needed.
Automated Multi-period Cost Master Sheet Based On Prior Years Sales
Description
A large northern grower needed a way to generate a multi-period cost / profit analysis sheet more quickly. They had done so manually but it took many hours of high-level personnel and was difficult to format and formulate quickly.
Project
We took their costing period data and built a system that would automatically generate their costing / profit analysis against the prior year’s sales read from their analytics database and build an output file in the exact format they were used to in Excel.
Technology
Excel, SQL Server, Python and Python Data Stack
Results
Grower was able to free up time spent manually gathering, formatting, and calculating data and instead could focus on plan changes to improve execution and profitability.
Pay by Scan / Pay From Scan Pricing Model
Description
A grower of annuals and perennials who began to participate in a ‘pay by scan’ or ‘pay from scan’ or also known as ‘vendor managed inventory’ programs with some of their customers needed a model to build out new pricing for their products under those programs. They wanted to be able to see past key sales results by SKU and see other key retail statistics such as margin and sell through. Additionally, they wanted a way to estimate what future pricing should look like based on key cost assumptions across the various costing elements of their particular business model.
Project
We worked with key executives, sales, and ownership, to develop an Excel model with their requisite SKU data and statistics embedded in it. Additionally, we helped them identify a model to account for all the cost areas involved in a pay by scan or pay from scan program and allow them to build some scenarios so that using assumptions about various raw material changes, labor cost changes, shipping and sell through assumptions they could round out a program pricing plan that would meet their goals and objectives.
Technology
Excel
Results
They were able to access a broader range of scenarios and assumptions as they re-priced their programs. The resulting plan and output improved their probability of better profitability. Additionally the model provided better visibility to the program (weighted) profitability potential.
Cash Flow Projection Dashboards
Description
The owners and accounting team of a sizable southern grower needed a way to automate their cash flow forecasts. Previous accounting teams had built very large, complex Excel spreadsheets that proved very difficult to keep up to date. The cash flow forecast would need to include looking at normal vendor payments, and other fixed and varying costs such as utilities or equipment payments. It also needed to factor in the estimated hourly payroll as well as the salary-based payroll based on their sales plan. The cashflow forecast also meant building a projection of revenue from the sales model and integrating the outstanding invoices yet to be paid by customers, essentially the accounts receivable schedule. Additionally, there were a couple of types of lines of credit the business used that had different terms, and payment requirements that had to be built in.
Project
We built a set of SQL queries and materialized database views from their ERP accounting system as well as other external systems holding key data points. This included doing calculations from the production/sales plan as well as using standard payment schedules for some of the simpler varying and fixed payments. These disparate data sets were brought together with PowerBI and views were created that represented a 13 week projection of the week to week starting balance, income, expenses and then a final estimated ending cash balance. We also integrated the credit line repayment schedules and terms into the calculations so that the owners could see how the credit line repayments changed as the inventory and other asset levels changed with the seasons.
Technology
Excel, SQL Server and SQL Materialized Views, PowerBI, SharePoint.
Results
The accounting team and the ownership were able to use this, make key updates and get better visibility to near term cash situation estimates. This automation freed the accounting team from hours and hours of tedious, complex spreadsheet creation and validations.
Raw Material Inventory Walk Forward Projection
Description
A large wholesale grower needed a better data view for raw material inventory walk forward projections. Even though they were using an expensive commercial ERP system the views that were provided were confusing, difficult, and inadequate to clearly see the key information juxtaposed and in a nice calendar type flow so they could identify potential issues long before they actually occurred. They needed to be able to see the impact of planned (or missed) production, sales (or lack thereof), scrap week by week and to have those projections updated automatically as changes were made in their back-end ERP system.
Project
We built a set of SQL queries and materialized database views for their ERP system as well as other external systems used by them for production and sales plan to bring together needed data view for inventory, projection plan and sales plan. We then created a set of PowerBI dashboards which using the DAX language did the calculations for building the roll-forward values from those external data sets to show key raw material inventory metrics as well as a week-by-week view of all their raw material inventory items.
Technology
SQL Server, SQL Views, PowerBI
Results
The procurement team used this daily as they anticipated raw material surplus to be re-allocated or material shortage which needed to be handled. This tool saves hundreds of person hours each year and gives much better overall raw material situation visibility.
Item Grow Time Profile Migration Go Live Conversion
Description
A mid cap US wholesale grower was doing a back-end system migration from an internally developed legacy production system to a commercial ERP system. The legacy system used a very granular ‘weeks to make’ format where the estimated grow time of a given plant could be stored to account for every week of the year at every geographical location they used. For the number of finished goods and propagation items, they manufactured this amounted to almost a quarter of a million records of data for grow time that would need to be verified, filtered, transformed, and migrated. Additionally, the legacy system was a forward-schedule system by week whereas the new ERP system was a week-range value system and used backward scheduling.
Project
We build some ETL and analysis tools as well as the algorithms to clean up records, filter, and collapse (using average smoothing) the grow time data into the new necessary ‘week range-value’ format required. We built the program to output formats that were easily reviewable by the team for human expert validation of key use cases to validate the operation of the model and its output. We then used the tool to build the necessary import file format for the new ERP system imports and imported the grow time data into the new system.
Technology
Python and Python Data Stack, SQL Server
Results
The system worked and converted all the records to the new format. It is estimated that it saved several hundred person hours of manual work validating and hand keying all the data.
Automated Item Data Validation and Error Flagging Notice
Description
A grower of a variety of plant types needed a way to automate error checking and item limits validation. Their commercial ERP system had many database fields and tables that impacted item planning, raw materials estimates, costing, space, labor and shipping calculations and other key item units of measure for sales, purchase and shipping. They discovered that errors in these areas could cost large amounts of money if not caught and addressed prior to production, procurement or shipping.
Project
We built a system to scan the designated records and tables in all the areas impacting item setup including the item details, any unit of measure of pack units, bill of materials, item routing, and item costing records. We built SQL queries to extract necessary details and then built template validations for each item to validate the item had all required setup and that the values were directionally correct. The program generated an Excel worksheet automatically that contained tabs with each problem type and the items affected. This made it much faster and easier to see where the issues were and correct them. It also allows the new team members to have yet another source of error checking as they interacted with the system.
Technology
SQL Server, Python and Python Data Stack, Excel
Results
A weekly report was generated in which the growing and procurement team used to make adjustments then re-run any models that depended on those (such as raw material need or budget etc.). We believe that this saved the grower thousands of dollars in incorrect calculations regarding item setup as well as saving hundreds of person hours in analysis and ‘hunting’ for issues.
Weighted Item Breakout for SKU orders
Description
A large grower of annuals needed a way to take orders at a SKU level and automatically break those orders out to the item level. The Store orders were at the SKU level but the grower sometimes has many individual items that could be shipped for that SKU available and ready to be sold. The back end ERP system the grower used required orders at the item level but the retail customers of the grower have all order/sales/delivery values at the SKU level.
Project
A program was written which would take the SKU based orders, query the back-end ERP system to get the SKU to item mappings, and the current unallocated availability of items and quantities that were ready to sell. It would then do a weighting based on qty per SKU considering minimal shelf quantities etc., it would also weight the store order need so as to be able to scale across stores as required. The program would then re-write the orders to the item level with the weighted quantities of the individual items under the SKU. These could then be imported into the system. In this manner the grower could then do the order picks from the items they had. Once the order picks were processed the delivery and invoices to the customer were rolled up to the SKU level quantities by the ERP system.
Technology
SQL Server, Python and the Python Data Stack, Excel
Results
The system was used, and the order imports generated saved tens of hours of manual item breakout each week as orders were processed.
Automated Contract Grower Pricing Model
Description
A grower of annuals and perennials contracts a portion of their production plan each year to a number of other growers. Each contract grower had a different part of the plan, and some growers grew the same item due to volume needs. Each grower had different shipping costs and other constraints. All of this had to be considered when building a model to allow the main grower to create price quotes for the individual contract grower to consider for their individual plan of items.
Project
We built a system that would read the portion of the internal production plan from their internal system that was desired for a given grower and using the internal costing from their back end ERP system, as well as stated cost assumptions for this plan, and key item metric values it would automatically generate an Excel model with the cost quote for the grower based on their individual plan and transportation parameters. This cost quote was then reviewed and could be adjusted or overridden before sending to the contract grower for approval or additional negotiations. Once the plan and pricing was set with the grower, the output of the quote sheet could be transformed into a pricing import template for their back end ERP system so that when the orders for the grower were written they had the agreed to pricing.
Technology
SQL Server, Excel, Python and the Python Data Stack
Results
The grower has used the mechanism for multiple seasons for all the contract growers they interact with, saving hundreds of manual effort hours and improving accuracy of the pricing quotes and downstream transactions.
Facility Efficiency Metrics Study and Analysis
Description
A multi-facility US grower of multiple plant categories needed detailed analytics done regarding facility costs, overhead, efficiency, and revenue as part of an effort to improve profitability and rationalize facility costs and plan.
Project
We build the necessary SQL queries to extract data from their back-end ERP and accounting system for key data points such as facility payroll, facility production, costs of operation and overhead and other efficiency data they had collected. We pulled all the data together into a comparative metric calculations model. These model data sets and resulting key metrics were shared with the operational leadership of the facilities in a presentation.
Technology
SQL Server and SQL materialized views, Excel
Results
After the review of the data, the teams created plans to improve operation, streamline overhead and abandon unproductive or too expensive facilities. Additionally, we refreshed the analysis each year for the grower as they strove for continuous improvement.
Accounting System Historical Imports for System Migration
Description
A large-scale annual grower was doing a system migration to a new ERP system. They desired to import 5 years of historical data from multiple operating companies into the new system. This included journal transactions as well as open sales orders, purchase orders and invoices.
Project
We built an extraction and transformation utility (ETL) that would read their old data from the legacy accounting system and compile/summarize and transform it to a format compatible with the journal imports for the new system they had chosen.
Technology
SQL, Python, Excel
Results
We were able to migrate the 5-year data requirement into their new system for all of the selected affiliate companies. We saved the accounting team doing the migration many person hours and got the company the historical data they needed.
H2A Employment Costing Impact Model
Description
A large annual grower needed better understanding of how utilizing the US H2A labor program would impact their business.
Project
We worked with ownership and key management team to build an excel model that would allow them to assess cost impacts using a variety of assumptions and scenarios.
Technology
Excel
Results
The grower did end up going to H2A for a portion of their labor force and saw improvements in output and productivity.
Item Cost Master Reporting Automation
Description
A multi facility grower needed a way to generate a report that detailed constituent costs at the item level including configured raw materials, allocated labor and overhead and other aspects of item cost.
Project
We built an extraction and analysis tool which would extract known costing configurations from their back-end ERP system and update all the per unit costs and automatically build an Excel spreadsheet to put all those impacts together at the item level with the per unit values.
Technology
Python, SQL server, Excel
Results
The team used this continuously in analysis, planning and pricing considerations, saving them countless hours of manual work to bring all the data together.
What project can we do for you?
Find Out More
The AGS team has years of experience helping growers of all sizes optimize their businesses. We understand the unique needs of greenhouse and nursery growers and have the expertise to create custom solutions to improve your grower businesses. Learn more about our Consulting solutions or Contact us to learn more about how our consultants can help you grow your business.