Epicor ERP Software

 Content Editor

Upgrading Your Inside Sales & Customer Service Teams Part 4: MS Excel Tips

Continuing our discussion on how to improve upon our employee skillset, let’s review Microsoft Excel.  This is quite possibly the most important tool in the Microsoft line of products, because it allows anyone with a basic understanding of some Excel tools to analyze, manipulate and comprehend data. Because knowledge is power, Excel can empower your workforce to be more analytical, and thus more capable of making decisions on actionable data.

The following example data will be used throughout this blog.


You’ll notice that all the names are in lower case. To change this without hand keying everything over again is simple―by using the Excel function called PROPER.

Step 1: Add a new column called, ‘First Name Proper’ or anything that makes sense to you
Step 2: Place your cursor in the cell where you want your corrected name to go; in this case, C2. Then, using the function field in Excel, enter the function as follows: =PROPER(A2) 

This tells Excel to look at cell A2 and to capitalize the first letter of each word and use lower case for the rest of each word.  Had the cell contained multiple words, each word would have the first letter capitalized.
Repeat the steps above to add a column for the Last Name Field.

Now that you’ve created the formula for one cell, you can apply the same formula to other cells using the crosshairs feature in Excel.  To do this, hover your cursor in the lower left corner of the cell that contains the formula you want to extend to other cells.  You will note that the white cross becomes a black crosshairs. When you see the crosshairs, double click and the formula will be applied to every cell below it until it comes to a blank field in the column on the left of it.
Pasting Values
Now that you’ve made the data look better, you may be bothered by the fact that you have two columns of first names and two columns of last names. This too can be easily fixed, though not as simply as an inexperienced Excel user may think.

Because the fields you’ve used the PROPER function on are tied directly to the cells they’re referenced to (i.e., Column C cells are referencing data in Column A cells), any change you make to column A will immediately affect the data in column C.  So if you delete the column in lower case, your PROPER column will “error out,” because it will no longer have anything to reference.

You can fix this by pasting values.  By highlighting Column C and copying (using Ctrl + C or using the contextual right click menu), you can then highlight Column A and right click.  From there, you’ll click on the clipboard with the numbers on it.  This is the PASTE VALUES function in Excel.  This literally instructs Excel, regardless of the formula of the copied cell, to paste the value itself and not the formula that created it. 
You can repeat this for the Last Name column, leaving you with duplicate First Name and Last Name columns.
From there, you can now delete Columns C & D, leaving only two columns.
In our next blog, we’ll show how to easily turn these two columns into one, and how to use the VLOOKUP function to drill into data.

Posted by Brad Vance, Epicor Senior Business Process Consultant

Analyzing Your Risks for Business Success

We manage risk in our personal lives from issuing car insurance to dental cleaning, in hopes to reduce the consequences of potential unwelcome events. No one wants car accidents or dental problems, right?

Yet what we often fail to do is identify and manage risk in a business environment, similarly as we recognize risk in our private lives. More often than not, companies do not investigate everything that could impact their business, but rather, look only at the obvious areas of risk. They need to engage in proper risk management.

Risk management is the process of identifying, assessing, and controlling risks that arise from operational factors, and making decisions that balance the risk costs with the benefits.

You might be asking, why manage risk at all? What would happen if we choose to cliff dive in an area that no other swimmers have dove into before, and all it took was a minute of our time to prevent a catastrophe? All businesses encounter circumstances that can be identified and mitigated appropriately to gain positive risk benefits and reduce the impact of negative risks.

Typically, risk is considered to have a negative connotation, but in reality, risk is what provides a business the ability to succeed and we often fail to differentiate between good risks versus a bad risk. For example, if a company decides to increase its product lines to service a different customer demographic, this risk could potentially increase revenue while growing the overall business. If this risk is not taken, the company may remain at the same growth level, or have negative growth due to lack of innovation and customer retention.

That said, there are two types of risk:

  • Good risks are called opportunities
  • Bad risks are called threats

The goal of risk management is to really minimize potential negative risks, while maximizing potential positive risks.  While some companies go without a defined strategy, hoping everything will “work out for the best,” the reality is that businesses must be proactive in assessing risk versus the “hopeful” alternative.

Risk management helps establish the framework in which the project team will identify and develop strategies to mitigate or avoid the risks associated with activities such as a new project, ERP implementation, or upgrade/migration venture.

The risk management process should include the following tasks:

  • Risk management planning
    • Define the risk management plan
  • Risk identification
    • Risk register
    • Risk statement
    • Categorizing notations of risk
  • Risk assessment
    • Rating scale
  • Risk responses planning
  • Risk monitoring & control

We can take control by consistently evaluating and adjusting our processes to achieve the greatest impact on the success of the business. The process of implementing effective risk management will result in a more predictable and profitable business. To learn about analyzing risks for your businesses, visit our consulting services page to download the whitepaper:


Posted by Epicor Social Media Team

ERP Part Numbering Standards for Manufacturing (Part 2)

In the previous blog post, we went over types of part numbering and general recommendations from Epicor. In this post, we will be giving a summary of part numbering options.

Standards for your Customers, Suppliers, and Manufacturers
This section refers to the base part numbering. It may not be within your control to change the part numbering methodologies of your suppliers, manufacturers or customers. It is, however, possible to have your own part numbers for all of the above.

Why Have Your Own Numbering System?
Why not use your supplier or customer part numbers for numbering your own parts?  There are a few reasons:

  1. Consistency: Just as you have multiple customers, your customers will have multiple part numbering standards.
  2. Elimination of Duplicates: What if two of your suppliers, or two of your customers, happen to both use the same part number to call out two different items? Now you have to come up with some method for eliminating these accidental collisions of data.

Summary of Options
There are several numbering options outlined below. Epicor recommends the third, “semi-meaningful” numbering system for most companies.

Meaningful Part Numbering
This is where the digits of the part number each have a specific meaning, resulting in the ability for a knowledgeable user to know what a part number translates into. However, having a completely meaningful system makes for a very complicated structure, and necessitates a strong engineering organization to control the part number assignment. Some companies choose to only use a fully meaningful part numbering system on their finished goods.

Non-Meaningful Part Numbering
In this case, the part number itself does not mean anything significant. This could be a sequentially assigned number, or can appear to be random. Also, sorting by part number is totally meaningless. When searching by part number, you must know what you want.

Semi-Meaningful Part Numbering (Recommended)
This is a system where there is some meaning in the part number—defining the type of part, but not down to every final detail. Not all attributes of the part are defined. At some point, there becomes too many attributes to track in the part, so a sequential number is assigned to differentiate from other similar parts. The actual description and differences are stored in the part description, as well as the part’s drawing.

To learn more about part numbering rules, best practices and recommended formatting, download the white paper ERP Part Numbering Standards for Manufacturing here.

Posted by Tim Shoemaker, Senior Principal Consultant, Epicor Professional Services

ERP Part Numbering Standards for Manufacturing (Part 1)
There are many opinions on the subject of “proper” part numbering systems within the manufacturing world. Many companies will fall into what can be called an “accidental” part numbering scheme, and still others end up over-thinking their designs. This blog series will present you with guidelines for developing and using good part numbering techniques within a manufacturing company.
The first thing to note is that a part number is generally not a description of the part; there are separate fields for holding the actual description, as well as part class and product group. Having part numbers defined by whether a part is purchased or manufactured is not recommended, as this can change over time. Anything that can change should not be embedded into the part number.
Every part has a “Part Number,” which is also the primary index for the part table; this may also be called an “Item Number” or a “Stock Keeping Unit” (SKU). There should be only one part number set up for each item that is kept in stock. We assume that each part number represents a specific item, and there are no duplicate items.
Here are 4 different types of part numbers you should be aware of:
  • Manufacturer Part Number: Since you may have multiple manufacturers for any one part number, it is possible to have multiple manufacturers and multiple manufacturer part numbers assigned to any one of your part numbers. An enterprise resource planning (ERP) system is able to store cross-references to multiple manufacturers’ part numbers.
  • Supplier Part Number: A supplier part number is the number that your supplier/vendor labels your part. Your supplier may be a manufacturer, or they may be a distributor for another manufacturer. Since they may reference their part number differently than the manufacturer, you can create a three-way tie between your part, the manufacturer part, and the supplier part.
  • Customer Part Number: The ERP system can also have a customer part number cross-reference. Since multiple customers can purchase the same part, there can be multiple customer part numbers that reference your part. Customer part numbers also have their own descriptions.
  • Internal Part Cross-reference: This final part number option is to create an internal, secondary method of calling out a part number. The internal number can be used as a shortcut to a longer part number, or it can modify any serial numbering methodologies for the base part. It is also possible to have multiple internal cross-references.
In the next post of the Part Numbering Standards for Manufacturing blog series, we will be giving a summary of part numbering options. For more information on Epicor consulting services on this and other aspects of ERP, visit here.
Posted by Tim Shoemaker, Senior Principal Consultant, Epicor Professional Services
Implementing Consignment Inventory (Part 3 of 3)

In previous blog posts, we covered the definition, initial setup and advantages of managing consignment inventory using Epicor ERP v9 or v10.

Several other questions remain to be answered, i.e.: “How do we verify the consignment inventory quantities?” and “How do we finish the contract without ending up with extra inventory?”

Cycle Counting/Physical Inventory
Consignment inventory is still an asset of the company that currently owns it. It is therefore the distributor’s fiscal responsibility to periodically count that inventory to make sure it is still in its proper place. If you have used the procedures defined in the previous blog posts, then you can use the standard cycle counting or physical inventory module that is built into Epicor ERP v9 or v10.

Since Epicor supports counting by warehouse ID, you can simply initiate a physical inventory for your consignment warehouse. If you have multiple customers, you would have multiple warehouses, with each warehouse to be counted separately.

Of course, in order to do this inventory check, you still need access to the inventory itself, or you need a trustworthy customer that will return the physical inventory results.  It is up to you to determine what you are going to do with any adjustments that are found.

  • If during the count, you find more parts that were supposed to be there, that may mean:
    • You have overbilled your customer for product they did not actually use, or
    • You shipped more parts than you acknowledged in the electronic transfer order.
  • If during the count, you find fewer parts than you were supposed to:
    • Your customer has used more parts than they have told you about.

In either of the above two cases, you need to confirm that there is a clear declaration of who is responsible for any variances found during the physical inventory. If you followed all of the procedures, there should be no unverified variances in the shipment. This would then leave only one option for the variance: issue a credit or an invoice for the difference in inventory quantity.

Closing the Contract
As the contract is coming to an end, you want to ensure that you do not end up with large quantities of inventory sitting in your customer’s consignment warehouse. At some point, they will not want that inventory anymore, and you will need to take it back (unless you have a clause in your contract to protect you).

Our recommendation is to closely monitor those parts as the contract is winding down, to make sure you have reduced or eliminated the minimum on hand/safety stock levels that you have defined throughout the system. Also, verify that any forecasts that the customer has delivered represent real, sellable product.

Posted by Tim Shoemaker, Senior Principal Consultant, Epicor Professional Services

Implementing Consignment Inventory (Part 2 of 3)

In the previous blog post, we covered the definition, initial setup and advantages of managing consignment inventory using Epicor ERP v9 or v10.

Below are the steps that must be done to implement a new consignment customer with a new consignment part, assuming that you already have a consignment plant (in v9) or site (in v10) set up in the system.

  1. New consignment agreement is placed by the customer
    • Create a “Consignment Warehouse” for this new customer (one time for each customer).
    • Define minimum stocking levels in the consignment plant/site; i.e., if your contract says you are to always have 100 on hand, then set 100 as the minimum on hand for the consignment plant/site.
    • Define minimum stocking level at the manufacturer’s local plant. This would be any inventory that you want to keep on hand to fulfill any rush requirements. Typically, this is less than or equal to the minimum consignment level above.
    • In the consignment plant/site, specify that this item is a “Transfer” item, and that the “Supply Plant/Site” is your main manufacturing plant. Also specify the transfer lead time (the number of days that the item will be in transit).
    • Create the MRP forecast for this customer part in the consignment plant (i.e., you are forecasting that the inventory will be consumed from consignment, not from your local stores).
    • Note: at this point, you do not have to have any firm Sales order entered… MRP Forecasting + minimum on-hand will drive the first fulfillment of consignment inventory.
  2. Run MRP
    • MRP will see that you are currently below the minimum in the consignment plant, and will generate suggested transfer order requirements from the manufacturer’s plant to consignment.
    • MRP will then add these requirements to the minimum on hand specified in the manufacturer’s plant, and will generate “unfirm jobs” to fill these demands (even without a firm sales order).
  3. Firm Job, and Make Product
    • This is a normal job in your local plant.
    • When complete, the product is received to stock.
    • Once in stock, it will show on the suggested transfer order shipments.
  4. Ship Transfer Order from the manufacturer’s plant
    • Transfer orders generate Transfer Order Packslips. These packslips show the address of the demand (the consignment plant). It is your paperwork that goes to the customer.
    • Note that this is not a customer shipment…it is a transfer, because you are not transferring ownership, only moving inventory locations.
  5. Receive Transfer Order into Consignment
    • This is done once you receive confirmation from the customer that they have received the shipment.
    • This function actually takes the inventory out of “In transit” and puts it into the consignment plant.
  6. Consumption of Consignment Inventory
    • In most cases, your customers give a report showing what items were consumed. When this happens, an order needs to be entered, shipped and invoiced.
    • There are several ways that this can be done, but easiest is to create a new “counter sale” sales order. Counter sales allow for an order to be entered, “shipped” and immediately invoiced without all the extra processing.
    • When you create the counter sales order, you tell the system that you are selling it from the “consignment warehouse” location. This then automatically reduces the quantity on hand in consignment.
    • If there are a large number of parts consumed each day, then this could be automated with a Service Connect process to create and ship the orders. Alternately, an Excel spreadsheet could be copied and pasted into the counter sale section on the sales order.
  7. Replenishing consignment locations (basically, Go to Step 2 above)
    • If the customer’s consumption above did not reduce inventory below the “minimum” on hand in the consignment plant, then nothing will happen.
    • But, if it does reduce the on hand below minimum, then when MRP has its nightly run (see step 2 above), it will create another transfer order suggestion to move more inventory from your main stock to consignment.

Consignment Summary
While there are “urban legends” that Epicor ERP v9 and v10 cannot do consignment inventory because there is not a consignment module, this is not true, and as shown above, the actual steps are not difficult. In fact, once set up, the system will self-fulfill as the customer consumes the inventory. The manufacturer may need some help from an Epicor consultant in setting this up the first time, but once the model is complete, it can be easily replicated.

In the last part of this series, we will discuss how to verify the quantities and finish the contract for consignment inventory.

Posted by Tim Shoemaker, Senior Principal Consultant, Epicor Professional Services

Introduction to Consignment Inventory (Part 1 of 3)
Consignment inventory is inventory that is:
  1. Owned by the manufacturer
  2. Shipped to the customer, but not invoiced until—
  3. Consumption of the inventory is advised by the customer—at which time, it is invoiced.

Consignment and Epicor ERP v9 & v10
Epicor ERP (v9 & v10) does not have a “consignment module” per se, but it does support consignment very well, with well-defined procedures. For example, the method described below has been used by multiple companies in the aerospace industry.

There are several deviations from this model that can cause it to malfunction.  We conclude this post by highlighting those pitfalls so they are not pursued.

Consignment with MRP-Multi-Site Advantages
By setting up consignment in the manner described here, there are many advantages and processes that can be managed within Epicor ERP v9 or v10. These include:

  1. Forecasting of consignment usage by location
  2. Management of minimum stocking levels by consignment location
  3. Management of minimum stocking levels at manufacturer’s location to fill consignment emergencies
  4. Automatic replenishment of minimum levels at the consignment location
  5. Ability to cycle count/physically inventory a specific customer’s inventory
  6. Easy shipment of “Transfer Orders” to move inventory to consignment location
  7. Material requirements for future consignment deliveries are still calculated based on the forecast that is entered into consignment

The Required Initial Setup
To process consignment inventory, there are several modules required, as well as some specific setups.

  1. Must have Material Requirements Planning (MRP)
  2. Must have Multi-site
  3. Must create a new “Plant” (in v9) or “Site” (in v10) to hold “Consignment Inventory”

Optional Setup Items
There are some decisions that are optional, depending on the customers, and the products that are shipping to those customers:

  1. You can set up either one consignment plant/site for the entire company, or one consignment plant/site for each Customer Ship-To. The reason is:
    • If you ship common assemblies to multiple consignment sites, then it is easier to track requirements if there is a separate plant for each location.
    • But if there are no common parts between customers, then creating one plant (or site), with one warehouse for each customer, is sufficient.
  2. You can alternatively create separate cost tables for each plant. This allows the plant to have its own average cost. However, many companies do not want this to happen, and tie the costs of the consignment plant to the main plant.

Pitfalls of Skipping Steps or Incorrect Setup
As stated above, there are several pitfalls that are potential causes for failure and should be avoided:

  1. Some think that these consignment locations are supposed to be “non-net inventory”… this is not true. They must be considered “nettable” inventory in order for this to work.
  2. Ignoring forecasts, or putting forecasts in the wrong location.
    • Forecasts should always be entered, and they should be entered into the consignment plant.
    • Forecasts are what drives the future purchases (and even manufactured job orders, if the lead time on purchasing/manufacturing is longer).
  3. Forgetting transfer lead time.
  4. Entering sales orders against the wrong plant. All consignment usage must be “shipped” (consumed) from the consignment plant.

In the next post, we will discuss the consignment process in action.

Posted by Tim Shoemaker, Senior Principal Consultant, Epicor Professional Services
RAPID Onboarding of Acquired Companies to your ERP

Many companies do not realize successful economies of scale and resource synergies from a merger or acquisition because they are unable to rapidly consolidate disparate organizations and business systems. Organizations currently on or considering a Merger and Acquisition (M&A) path will be tasked with bringing those companies together. One of the most critical projects in this endeavor will be implementing the selected go-forward ERP (Enterprise Resource Planning) system. A properly executed onboarding strategy will enable you to recognize ROI by leveraging the scale of the new organization, and capitalize on the synergies of the merger or acquisition.

“RAPID” in the title of this article represents an acronym and a framework for executing a fast-tracked ERP implementation. RAPID actually includes an acronym within an acronym.

  • Reports: Reports include any business intelligence output, such as: dashboards, printed reports, transactional information, external data sources, etc.
    • Interfaces: Interfaces are any systems that may push or pull information from the ERP.
    • Conversions: All activities related to data conversion or data transfer.
    • Enhancements: Any functional gaps between your current ERP and the acquired company’s ERP should be fully documented, focusing on the impacts to your value stream.
  • Actions: The near-term action items resulting from each meeting should be tracked to completion.
  • Policies: All policies—especially customer-facing ones—of the acquired company should be documented, and policy changes should be communicated to the appropriate stakeholders.
  • Issues/Decisions: As operational, personnel, or technical issues are uncovered, it is important to track those issues to completion with ownership and due dates. The resolved issues should have the decisions or solutions documented and filtered off your list.

Decreasing the amount of time to assimilate all entities onto one system will improve your timeline to return on investment of the merger or acquisition. Another tactic to rapidly implement acquired companies includes an effective project kickoff session, i.e., a Change Acceleration Process (CAP) workshop, to minimize employee resistance, attrition, and general dissatisfaction.

Communication is a key part of change management, and having an expert onsite that is skilled in this area is helpful. After all, if you fail to clearly describe the reasons for the acquisition and its expected impacts to your customers, your competitors will certainly do it for you.

Along with the technical migration, equally important projects to pursue during the
M&A process should include:

  • Human Resources Plans, Systems and Policies, such as: payroll, benefits, etc.
  • Marketing: A co-branding program is typical, with the acquired company’s logo being phased out over time
  • Sales territory alignment
  • E-commerce Web site shopping permissions

Approaching each aspect systematically, via the Actions, Policies, and Issues/Decisions execution steps of the framework above, will allow you to properly scope the project, manage the strategic deliverables, and work towards a RAPID implementation.

Posted by Jon Snow, Director, Business Consulting Services at Epicor

© Epicor Software Corporation Home |  Investors |  Partners |  Privacy
Terms of Use |  Privacy Policy Industries |  Solutions |  Products |  Company |  Customers |  Services |  Careers