Where’s My Damn Widget? – 5 Steps to Improved Inventory Accuracy Using Cycle Counting


Do you have unexpected part shortages?  Do struggle with inventory count accuracy?  Are you performing full physical inventory counts during the year only to find things are inaccurate again the following week?  Are parts frequently not in the location specified by your inventory management system?  Let us provide you with 5 steps to improved inventory accuracy using cycle counting.  Cycle counting is a fairly standard business practice used by distributors and manufacturers alike, to improve inventory accuracy and customer service levels, while also reducing total inventory costs which is typically a company’s biggest asset.

By implementing a robust cycle counting program, you can be assured of the following:

  1. Having a basis for establishing a inventory-related continuous improvement initiatives;
  2. Improved accountability within the warehouse team with respect to inventory accuracy;
  3. Improved confidence in the perpetual inventory balances, enabling the elimination of physical inventory counts;
  4. Enhanced insight into issues that are causing inventory inaccuracies and a reduction in lead time required to address those issues; and
  5. Most importantly, better customer service levels given the reduced probably of stock out situations caused by unanticipated part shortages.

The following 5 steps will get your cycle count program from a desire to fully implemented.  Read on…

Step #1 – Determine the count frequency and proportion of inventory to be assigned to each count frequency code (aka ABC code)

Each one of your active inventory items will need to be assigned to either an A, B or C frequency code.  The codes indicate the frequency with which each item will be counted yearly, as well as the importance each item represents within your inventory part number population (“A” highest importance, followed by “B” and then “C”).  To determine the frequency and proportions for each code, you need to assemble a list of your active inventory items, along with the quantity shipped/consumed for each part of the past year.  [Note: In this example, we are using the quantity shipped/consumed by part to determine each part’s relative importance to the organization.  Use whatever method makes the most sense for your organization, such as a combination of quantities and extended inventory costs is another alternative.]

To illustrate how this should work, assume we can allocate 1 full-time person to the cycle count program, providing for 2,080 hours per year (40 hours week * 52 weeks = 2,080 hours–sorry no PTO for our poor cycle count analysis).  After conducting a simple engineering time study, we observed that the average cycle count required 10 minutes, which provides for 12,480 counts per year (sorry, but no breaks either).  This includes both the initial count and any post reconciliation/investigation required.

Now that the number of theoretical part counts have been estimated, we can return to our list of active inventory items, with the quantities shipped/consumed over the last 12 months.  From our list, we see that we have 5,000 active inventory part numbers.  We have also retrieved the quantities shipped/consumed and sorted our list in ascending order by volume.

In our example, we determined that about 10% of our inventory items are extremely critical because of extended lead times and interchangeably between various products; consequently we have decided that we want those parts counted 8 times per year.  Next, the second tier products are the next 25% of inventory items, with the remainder 60% being of lesser priority because there are multiple sources within close proximity to the plant.  Given these parameters, we are very close to the maximum number of cycle counts our inventory analysis can perform:

Cycle plan table with ABC codes for fictitious business/entity

From the above, we can see that the current assumptions are within our maximum number of cycle counts.  If we were higher or lower than plan, we could adjust the parameters (mostly Proportion and Frequency to fit better fit our unique circumstances).  Step #1 is complete, we have the key assumptions needed to add more details to our plan.   

Step #2 – Assign Our Inventory Parts to Our Planned Working Days Throughout the Year

Next, you need to slot the 12,250 counts into the calendar.  With our full calendar, that has no assumed days off (not even Christmas??), we can perform 47 counts per day (12,250 / (52 weeks * 5 days/week)).  At 10 minutes per count, 47 counts would require a little under 8 hours (47 counts * 10 minutes/ 60 minutes = 7.8 hours).  This plan is a little unrealistic for nearly all businesses, but you will have to adjust the assumptions to fit your unique circumstances.

There are many ways to slot the counts into the calendar, most of them involve a bit of brute force machinations, but here is one rather mechanical method that works with some tweaking at the end:

The process that we illustrated above on our YouTube channel requires a bit of brute force to work through, but it is efficient and takes a significant amount of the tedium out of the process, assuming your ERP system does not automate the assignment process.  If you would like a copy of the worksheet example, feel free to reach out.

Step #3 – Execute on the Plan and Root Cause Variances That Exceed Established Thresholds

Once you have established a plan, then you need to determine how you will execute on your plan.  There is some common vernacular around the cycle counting processing that includes the following:

  1. Perpetual Count: This is the quantity that is referenced in your ERP system and is being updated automatically through receipts, work order consumption, shipments, scrapping, etc. type transactions;
  2. Physical Count: This is the actual count determined by physically counting or measuring the quantity of inventory on hand at a specific location;
  3. Book-to-Physical Adjustment: This is an inventory transaction whereby the current perpetual inventory balance is adjusted to the physical count, with the variance being posted to the P&L as either a debit (for missing inventory) or credit (finding more inventory);
  4. Unreconciled Count: This is the initial count made by the warehouse team and returned to whomever analyzes the cycle count results.  Generally, step 1 is give a part number and location to the warehouse person responsible for counting the inventory, without the expected result (or perpetual balance in your ERP system) and then compare the result to the perpetual system;
  5. Variance Threshold: If the threshold is exceeded, this is used to determine when your warehouse personnel need to return to the part and location to repeat the physical count;
  6. Reconciled Count: If the part count exceeded the threshold, and the second count confirmed the physical quantity was different than the perpetual balance, then the reconciled count is posted to the perpetual system to update the count using the book-to-physical adjustment process; and
  7. Root Cause Analysis: When parts exceed the variance threshold after repeated efforts to reconcile the difference between the perpetual and physical counts, then the inventory analysis team needs to determine what caused the variance and associate the event with one of your predetermined root cause types (see below for example).

You will need to establish a process for cycle counting that includes repeat counts for parts that exceed your threshold variance amount.  Here is a flow chart for the typical cycle count process that you can modify for your own process workflow:

Cycle count flow chart including steps and tests required to update and root cause cycle count variance and maintain inventory perpetual balance accuracy.

When counts exceed threshold variance, you will want to root cause the variance to provide a feedback mechanism that the organization can use to monitor performance and take correction action, when required.  Here is a typical list of root causes that fit the typical distribution company:

  1. Untimely reporting of invoices and/or receipts: Use this when
  2. Incorrect inventory location;
  3. Issue with inventory labeling
  4. Issue with Unit of Measure;
  5. Inventory Shrinkage; and
  6. Other (neither 1, 2, 3, 4, nor 5) Requires comment

Revise these 6 categories to suit your business requirements.  But even before you do that you need to determine what you variance threshold will be.  This is the quantity % variance that when exceeded, mandates that the Warehouse team go back and perform the inventory account a second time.  When variances are below the threshold, the Warehouse team should just adjust the perpetual count to align with the new physical count and move on.

Step #4 – Track Results and Take Action on Unfavorable Trends

Clearly, the most important aspect of having a robust cycle count program is increasing the accuracy of your inventory, such that it enhances customer service levels while allowing the business to reduce safety stock levels and other inventory write off expenses pertaining to excess and expiring inventory.  In addition to having a more reliable inventory processes, your cycle count program can provide a number of valuable insights into what is working and what needs improvement in your inventory handling processes; consequently you are going to want to capture your cycle count results and look for potential trends that can be proactively remediated before becoming larger problems.

If you inventory management/ERP system does not support cycle count analytics, then you may want to start your own system for tracking results.  Below you can see how we modified our original cycle count plan to include columns for recording and tracking cycle count performance.  Here’s an example:

Detailed Cycle Count results illustrating how to parse the variances, using Unreconciled, Reconciled and Root Cause variance codes to analyzed cycle count performance.

Subsequently, you can use either Excel Power Pivot or Power BI to generate trend analysis, such as in the example below, which only includes a couple of days worth of results, but still illustrates the concept.

Illustrative result of Cycle Count results for first 4 work days in January 2023, using Excel Power Pivot.

Step #5 – Periodically Audit the Cycle Count Program Results

You will want to periodically have the Accounting team randomly select a few inventory part numbers and check the cycle count program by performing their own independent counts.  Audit results need to be reported to both the CFO and Operations VP as an independent verification of the efficacy of your cycle count program.

Step #6 – What to do Next Once You Have Improved Inventory Accuracy Using Cycle Counting: Focus on Optimizing Inventory Levels

Do you know what your industry inventory turnover ratios are?  Do you know how rapidly you are turning over your own inventory?  Do you know how to compute Days Inventory On Hand for your total inventory as well as individual parts?  If you answer no to one or more of the above questions, you need to get a better grasp on your company’s inventory efficiency and how it relates to your industry.  Bench marking your inventory against your peers is a good start, but just that is just the beginning if you want to get to best in class level efficiencies. Read our post on how to improve inventory turnover ratio using DIOH to being your optimization efforts.

If you would like a copy of a draft cycle count procedure, reach out to and we will send along a Word document that you can modify as needed.

If you need any other assistance, we are available for consulting at reasonable rates.  Contact us with any questions. Or…

About the Author
About the Author
Chase Morrison provides CFO services, utilizing Profitwyse’s 3D Growth Platform™, enabling his business owner clients to more readily achieve their goals for wealth creation and family legacy.  Contact him today to learn how your business can hit the accelerator using Profitwyse’s proven platform.

 

 

9 Steps to Generate an Insightful Gross Margin Bridge Using Excel


 

A critical part of analyzing profitability is being able to bridge your planned vs. actual gross profit variance when reviewing monthly and year-to-date results.  Analyzing sales variances is fairly easy, because it just falls to both price and volume.  But gross margin analysis is more complex given that it includes both cost and mix variances in addition to price and volume components.  But don’t fret because we are going to take you through a detailed analysis.  First, we will need to generate a gross profit analysis, where we segment the plan vs. actual variances and then generate our gross margin bridge using those variances

Knowing how to generate effective gross profit analysis is an imperative in today's competitive environment. See how to parse gross profit variances and take action. Click to Tweet

 

What insight can be gained from a gross profit/margin analysis?

Are you faced with a summary of gross profit variances, for your various products and associated families, that appears as follows:

Graphic showing 1Q gross profit variance of $682K.
Click here to purchase an Excel template, that you can customize, to begin your own gross profit margin bridge efforts ($29.95)

After viewing a summary such as the one above, and being the inquisitive type you are, up are going to want to drill down into the source of the $682K unfavorable variance.  Here are some common questions:

  • Is our pricing strategy working as planned?
  • What is the gross profit impact of new products?
  • How are volume differences within a product family or group impacting gross profit?
  • Are our assumptions regarding standard/average costs (or prices for that matter) correct?
  • Are volume vs. price vs. mix favorably or unfavorable impacting total gross profit and if so, by how much?
  • Which products/families are having the greatest impact to gross profit?
  • Given our options for influencing gross profit, what will have the greatest “bang for the buck?”

 

Step #1 – Determine what part of the total variance can be attributed to Average Sales Price (ASP) differences

As an example, we will drill into the Product B.1 variance, starting with the ASP Variance impact.  ASP is computed by dividing gross invoice price by the number of units shipped.  An ASP Variance is computed as follows:

ASP Variance = (ASP Actual – ASP Plan) * Actual Volume (or units actually shipped)

Below are the ASP Variances for all the Products.  We will spend time drilling into the Product B.1 variances, which totals $968K favorable, with $575K associated with the ASP Variance. 

Gross Profit Variance with ASP Variance Parsed Out for detail discussion regarding Product B.1
Click here to purchase an Excel template, that you can customize, to begin your own gross profit analysis efforts ($29.95)

Viewing the 1Q plan vs. actuals for Product B.1 on our supporting schedules (shown later), the plan assumed that the business would ship 9,000 units @ $750/each for total revenue of $6,750K.  In actuality, the business shipped 9,700 units @ $809/each, totaling $7,850K.  Here’s the ASP Variance calculation for Product B.1:

ASP Variance = ($809 – $750) * 9,700 Units ≈ Favorable $575K

 

Step #2 – Determine what part of the total variance can be attributed to Average Cost differences

 Products are usually costed using either standard costing or average costing (FIFO and LIFO are less common, but it is all the same variance analysis).  Whatever the case, you can gain valuable insight by comparing your planned cost of goods shipped vs. actual using the following formula:

Cost Variance = (Unit Cost Plan – Unit Cost Actual) * Actual Volume

Below are the Average Cost Variances Product B.1 highlighted:

Schedule displaying the Product B.1 Average Cost Variance, which is $186K unfavorable due to lower than planned total shipments for the quarter.
Click here to purchase an Excel template, that you can customize, to begin your own gross profit analysis efforts ($29.95)

Same as above relative to Average Cost Variance, the 1Q plan vs. actuals for Product B.1 on our supporting schedules, the plan assumed that the business would ship 9,000 units @ $455/each for an extended cost of $4,095K.  In actuality, the business shipped 9,700 units @ $436/each, totaling cost of $4,228K.  Here’s the Average Cost Variance calculation for Product B.1:

Average Cost Variance = ($455 – $436) * 9,700 Units ≈ Favorable $186K

 

Step #3 – Calculate Sales Volume Variance impact to Gross Profit

Plan vs. Actual volume variance affects gross profit through changes to revenue and costs.  Generally, the more favorable the volume of units shipped, the more favorable the revenue variance and conversely the more unfavorable the cost variance, for obvious reasons.  Here’s the formula for computing the Sales Volume variance:

Sales Volume Variance = (Actual Volume Shipped – Plan Volume Shipped) * Planned Unit ASP

Below are the Sales Volume Variances for Product B.1 highlighted:

Sales Volume Variance calculation
Click here to purchase an Excel template, that you can customize, to begin your own gross profit analysis efforts ($29.95)

The Sales Volume Variance above is a favorable $525K, indicating the volume for Product B.1 was significantly higher in 1Q than planned.  As we mentioned earlier, the 1Q planned volume was 9,000 units, while we were actually able to ship out 9,700 units in 1Q.  The Sales Volume change is multiplied by the planned ASP,  as follows:

Sales Volume Variance = (9,700 Act. Units – 9,000 Plan Units) * $750 Plan ASP ≈ Favorable $525K

 

Step #4 – Calculate Cost Volume impact to Gross Profit

As you would expect, if you have a favorable Sales Volume Variance as we do above, then you are going to have an unfavorable Cost Volume Variance because you are shipping a greater volume of inventory to customers.  The variance is computed by multiplying the difference between the planned and actual units shipped, by the planned Average Unit Cost.  Here’s the formula:

Cost Volume Variance = (Plan Volume Shipped – Actual Volume Shipped) * Planned Unit Average Cost

Below are the Cost Volume Variances for all the Products with Product B.1 highlighted:

Schedule displaying the Product B.1 Cost Volume Variance, which is $319K unfavorable due to higher than planned shipment volume.
Click here to purchase an Excel template, that you can customize, to begin your own gross profit analysis efforts ($29.95)

In our example, there were 700 more units shipped than planned.  The planned Average Unit Cost was $455, generating the following calculation:

Cost Volume Variance = (9,000 – 9,700) * $455 ≈ Unfavorable $319K

As you can see above, shipping more than the planned unit volume results in a reduction to gross profit due to the higher volume of items being shipped to customers. 

 

Step #5 – Calculate the Family-Level Volume Variance (or Final Volume) impact to Gross Profit

Next, we need to compute the Family-Level Volume variance, which will produce a slightly different result than summing the individual Product-Level Sales and Cost Variances.  This is only applicable if you have summary groupings.

As you can see in our example below for Product Family A, there are two different products: 1) Product A.1; and 2) Product A.2.  Both Products have their own plan, actual and variance.  Product A.1 has a $50K unfavorable Sales Volume Variance and a $23K favorable Cost Variance, totaling $27K unfavorable.  Product A.2 has a $1,042K unfavorable Sales Volume variance and a $597K favorable Cost Volume Variance, totaling $445K.  Though it is not shown below, the total of Product A.1 and A.2 volume variances is an unfavorable $472K (Product A.1 $445K + Product A.2 $27K).  That is helpful information, but we now need to compute the Final Volume Variance for Family A, which will produce a different result than summing Product A.1 and A.2 volume variances.  Here’s the formula for computing the Final Volume Variance (note: FL stands for Family-Level):

Final Volume Var. = (FL Unit Gross Profit * FL Actual Units Shipped) – FL Total Plan Gross Profit

The Family-Level A Unit Gross Profit equals $790/Unit, actual Units shipped equals 14,700 and total planned Gross Profit equals$12,185K.  Here’s the Final Volume calculation:

Final Volume Var. = ($790 * 14,700) – $12,185K ≈ Unfavorable $578K

As you can see, the Family-Level A volume variance is a worse than if you only analyzed the individual products within the family.  This is because there is a Mix component to the variance, which is our final calculation needed to reconcile the total variance for all products.

Schedule displaying the Family A Mix Variance, which is $106K favorable due to a mix shift toward Product A.1, which is the more profitable product.
Click here to purchase an Excel template, that you can customize, to begin your own gross profit analysis efforts ($29.95)

 

Step #6 – Calculate the Family-Level Mix impact to Gross Profit

The Mix Variance is applicable to the interaction between products within a group, or in our example a Family.   Quantifies the impact of differences between individual products, with different proportional variances and gross profit amounts.  Looking at the example above, you can see that the Product-Level Volume variances total $472K unfavorable, but the total Final Volume variance for Family A is $578K unfavorable, which reflects the Mix Variance.

You can either compute the Mix Variance by squeezing for the difference between the sum of the individual Product-Level Volume Variances and Subtracting the Family-Level Final Volume Variance ($472K Sum of the Family A Product Level Volume Variances – $578K Family-Level A Final Volume Variance = $106K Favorable Mix Variance) of compute the Mix Variance separately as follows (again FL is an abbreviation for Family Level):

Mix Variance = (Sum of Product-Level Plan Unit GP * Product-Level Actual Units Shipped for all products in Family) – (FL Actual Units Shipped * FL Plan Unit Gross Profit)

As can see, calculating the Mix Variance rather than squeezing for the Mix Variance is a bit involved, but the specific calculations/formulas are on the Excel template for your use.

 

Step #7 – Step back from the trees and gain some actionable insight

The expanded Gross Profit Analysis is displayed below.

Graphic of the Summary Gross Profit Analysis Including all the variance categories, including ASP Variance, Average Cost Variance, Sales Volume Variance, Cost Volume Variance, Final Volume Variance and Mix Variance
Click here to purchase an Excel template, that you can customize, to begin your own gross profit analysis efforts ($29.95)

Here are some insights we can gain from our Gross Profit

 

Step #8 – Reviewing the Gross Margin Bridge results

Finally, here are the gross margin variances that result from the earlier gross profit variances.  All the margin variances are relative to the planned gross margin %, which in this case was 61.7%.  The actual gross margin of 60.2% was calculated by dividing the actual gross profit ($22,740K) by the planned extended total sales ($46,102K), producing an unfavorable variance to plan of 1.5%.  All the other gross margin variances are relative to the 1.5% unfavorable result.

Final full analysis display for all variance categories in our example
Click here to purchase an Excel template, that you can customize, to begin your own gross profit analysis efforts ($29.95)

This view is very helpful for understanding the scope of the impact in terms of margin differences.  It becomes much clearer that the Accessory #1 is the main culprit in under achieving the company’s gross margin goals.  The primary reason for missing the goal is a sales volume problem on that same product.  Product Family B is producing strong results, primarily due to favorable ASP results (1.2%), followed by both some favorable volume (0.4%), and a slightly lower average cost for the total product family.  Product Family A has a smaller unfavorable volume issue (1.3%) that is partially offset by good ASP results and some favorable mix performance.  In conclusion, the focus should be: 1) figure out how to fix the Accessory #1 sales volumes, and; 2) work on Product A.2 volumes as well.

 

Step #9 – Purchase your own Gross Margin Analysis Tool Kit and expedite your efforts toward insightful margin analysis!

Get a copy of our Gross Profit/Margin Analysis Tool Kit workbook to ignite your company’s efforts to increased profitability and success.  Here is what is included in the Tool Kit:

>

  1. Worksheet of your time phased sales plan (sales dollars, units and ASPs);
  2. Worksheet for your time phased cost of goods plan (cost of goods, units, unit costs);
  3. Worksheet for your time phased gross profit plan (gross profit dollars, gross margin, unit contribution dollars);
  4. Worksheet of your time phased sales actual (sales dollars, units and ASPs);
  5. Worksheet for your time phased cost of goods actual (cost of goods, units, unit costs);
  6. Worksheet for your time phased gross profit actual (gross profit dollars, gross margin, unit contribution dollars);
  7. Worksheet with the Gross Profit and Margin variances that includes an ability to easily change the period of review from a specific month, quarter or year to date; and
  8. Worksheet with some simple reporting tools to facilitate your analysis review.
  9. If you would like a copy of the Excel workbook used in the examples, please use the Contact Us button at the bottom of the page.  Please feel free to leave comments and likes.

The Tool Kit is designed to analyze gross profit/margin variances against plan, but you can easily modify the template to perform the same analysis vs. your prior year results too.  Very nice!

If you need assistance, we are available for consulting at reasonable rates.  Contact us with any questions.

Picture of author -- Chase Morrison
About the Author
Chase Morrison provides CFO services, utilizing Profitwyse’s 3D Growth Platform™, enabling his business owner clients to more readily achieve their goals for wealth creation and family legacy.  Contact him today to learn how your business can hit the accelerator using Profitwyse’s proven platform.

 

 

Take Action! – Read The Monthly CEO Advisory Report For May 2020 to For Business Tips During These Challenging Times


Profitwyse is a monthly contributor to the The CEO Advisory Report for May 2020, published by Ken Keller with Strategic Advisory Boards.  The Monthly CEO Advisory is a compilation of timely articles from professionals with deep expertise in all areas affecting the privately-held business space.  If you are a chief executive or a C-suite member of a midsize business, The Monthly CEO Advisory contains valuable information take you can put into action today.

Explore The Monthly CEO Advisory for May 2020 to gain insight and solutions into relevant issues affecting business owners today. Click to Tweet

If you would like to be added on regular distribution, please send us a note via our Contact Us page.

What’s in This Edition of The Monthly CEO Advisory For May 2020

CFO Insights | Chase Morrison

Four Essential Cash Flow Metrics You Need Today

Business Growth & Profits | Ken Keller

There Will Be Better Days Ahead

Commercial Real Estate | Sheryl Mazirow

The Office Space Market and COVID-19

Commercial Insurance | Paul Palkovic

Ideas For Affecting Commercial Insurance Premium Reductions — RIGHT NOW!

Human Resources Compliance | Barry Cohn

Employment Lawsuits Are Expected to Rise Dramatically Due to COVID-19

Manufacturing Excellence | BJ Schramm

Preparing For a New Normal

Information Technology | Craig Pollack

Tips to Spot Online Scammers During The COVID-19 Pandemic

Company Benefits | Peter Ettinger

Why You Need To Improve Your Employee Benefits Communication

Alternative Financing | Kristy Melton

Small Business Benefit From Working With a Business Finance Broker

Sales Management | Paul Mitchell

Create An Effective Crisis Sales Plan

If you have additional questions regarding any of the above segments, please reach out to authors.  Their contact information is included in with the content.

As always, if you need financial management expertise to accelerate growth, improve profitability or enhance cash flow, feel free to contact us today