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

Profitwyse is a monthly contributor to the The CEO Advisory Report for April 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 April 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 April 2020

CFO Insights | Chase Morrison

Six Eye-Opening Questions You Should Ask Your Controller Today!

Take a look at the Monthly CEO Advisory Report for April 2020 and learn important lessons from the C Suite Click to Tweet

Business Growth & Profits | Ken Keller

Things You Can Do To Better Manage Risk In These Turbulent Times

Commercial Real Estate | Sheryl Mazirow

Learn More About The Kind of Savings And Other Benefits That Can Accrue To You When Utilizing a Tenant Rep

Commercial Insurance | Paul Palkovic

Do We Have Coverage If We Shut Down Our Business Due to COVID-19?

Human Resources Compliance | Barry Cohn

Learn Tips and Tricks For Making Your Employees More Effective

Manufacturing Excellence | BJ Schramm

It’s Time Reconsider Non-Essential Tasks And Focus On the Core Mission

Information Technology | Craig Pollack

5 Tips For Maintaining a Work Life Balance When Working From Home

Company Benefits | Peter Ettinger

Is Your Group Health Plan ERISA Complaint?  Learn What To Look Out For

Alternative Financing | Kristy Melton

The Fed Just Dropped Interest Rates Again!  Learn Why An SBA 7a Loan Might Be Right For Your Business

Sales Management | Paul Mitchell

6 Things To Keep Your Sales Moving Forward During a Time of Crisis — COVID-19

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



How to Improve Inventory Turnover Ratio Using DIOH

Why is Knowing How to Compute DIOH Important to my Business?

Knowing how to improve inventory turnover ratio using DIOH metrics is fundamental skill for all business owners that need to optimize their inventory levels.  DIOH, which stands for Days Inventory On Hand, is a metric for determining how efficiently a product-based business is utilizing a very important current asset–Inventory; consequently knowing how to compute DIOH is important to improving your inventory turnover and to your business.  But more importantly, how you take action using the DIOH metric to balance inventory and service levels are critical to your long-term success as a business owner.  

Knowing how to improve inventory turnover ratio using DIOH metrics is fundamental skill for all business owners that need to optimize their inventory levels. Click to Tweet

Inventory Turnover ratio and DIOH are essentially two sides of the same coin.  As mentioned above, DIOH is the number of days required to exhaust your current stock.

DIOH is meant to reflect the number of days it will take a business to consume the inventory on hand using either historical or forecast demand.  You can compute DIOH metrics for total inventory (raw material, work in process, finished goods), for an individual finished good and for an individual raw material.  The Inventory turnover ratio and DIOH are essentially two sides of the same coin.  This is because you can compute DIOH with the inventory turnover ratio and vice versa.  To compute the inventory turnover ratio using DIOH, just divide 365 days by the DIOH to get the turnover ratio.  For example, if you have a DIOH of 37 days for an inventory item, then your inventory turnover ratio is 12X (365 Days / 37 DIOH = about 12X).  The goal is to pursue either lower DIOH or higher turnover.  Both have the same effect.

Relative to DIOH, the fewer the number of days you can maintain your inventory at, translates into higher positive cash flow.  But your inventory level needs to be balanced with your desired Service Level.  Service Level pertains to the probability you manage to relative to stock outs.  If you set a 95% Service Level, then you are accepting the possibility of stock outs 5% of the time.  More on this later.

How to Compute “Days Inventory On Hand”

Like nearly all accounting metrics, DIOH is retrospective in that you use historical business activity to compute the current monthly metric.  As described briefly above, there are two ways to compute DIOH that are a function of what you are measuring.  If you are attempting to measure an entire company’s DIOH, you would look to the Cost of Goods methodology.  If you are looking at individual inventory items, you would use the Units Consumed methodology.

Computing DIOH Using Cost of Goods

To understand how Cost of Goods is computed, consider what happens when a sales order is fulfilled.  When a sales order is fulfilled, inventory is shipped to an end customer and title of that asset transfers from a vendor (you the business owner) to a customer.  With that title transfer, the cost of all the items shipped is reduced by the standard or average cost of the items shipped.  Or speaking like an accountant, there is a debit to cost of goods and credit to inventory.  I hope your eyes are not already glazing over.  A cost of goods account on a P&L represents the accumulation of costs for all the items shipped to customers.

In order to compute the actual metric you have to have the period ending inventory valuation, which is a summary of the inventory costs for all inventory items a business possesses or holds title to as of the report date.  This will be the numerator in a computation.  The denominator will be the average daily Cost of Goods.  Generally you calculate the average daily cost of goods by using a 12-month rolling total of Cost of Goods and dividing by 365 days. 

Below is an example of the Cost of Goods methodology comparing the inventory efficiency of Walmart vs. Macy’s using their 2020 SEC 10K filings from EDGAR.  Both businesses are in the retail industry, but they have rather dissimilar inventory management capabilities.  Walmart can be considered “Best in Class” especially when you consider the fact that they ended their 2020 fiscal year generating $395B in cost of goods, and did it ending with $44B of inventory on their balance sheet.  In contrast, Macy’s generated $15B of cost of goods, ending their fiscal 2020 with $5B of inventory.  Let’s compare the two:

DIOH Comparison Between Walmart and Macy's with Inventory Turnover

Using Walmart’s SEC filing, we divide their $394,605M by 365 days to $1,081M of average daily cost of goods (if that doesn’t take your breath away…).  Then we divide their 2020 year-ending inventory valuation of $44,435M by the $1,082M average daily cost of goods to get 41.1 days of inventory on hand (DIOH).  Likewise using Macy’s SEC filing, we get a 124.8 DIOH.  We also include their turnover ratios, which are 8.9X and 2.9X, for Walmart and Macy’s, respectively.  

So why is this import–because Cash is King!  Walmart only needs to hold onto 41 days of inventory while Macy’s needs a 125 day cushion.  That is a very “eye popping” distinction.  You also need to think about the turnover, with Walmart at 8.9X and Macy’s at 2.9X.  This means that Walmart is 3 times more efficiently (8.9/2.9 = about 3) turning over their inventory.  Let’s start with the cash issue.  Macy’s has $5.2B of cash securing the inventory they need to run their business.  If Macy’s was able to approach Walmart’s “Best in Class” inventory performance, they could reduce their inventory from $5.2B down to $1.7B (Walmart’s 41.1 DIOH X Macy’s $42M average daily Cost of Goods = $1.7B).  That would free up $3.5B of cash to pay down debt!  How about interest expense?  Assuming Macy’s pay’s, on average 5% on their outstanding debt, that would free up another $1.8B of cash for other purposes.  The reduced inventory, plus reduced interest expense, would increase Macy’s cash flow over $5B.  An event like that would no doubt move Macy’s stock back on to the S&P 500!

Computing DIOH Using Units Shipped/Consumed

When analyze a single inventory item, you want to look at the actual units being shipped for finished goods or consumed for raw materials.  To perform this type of analysis, you will need to develop reports and potentially a data warehouse, where you can retain historical information on shipments and work orders. 

Below is an example of a single finished goods item, that includes some information on the major customers purchasing this items over past 12 months (March 2019 through February 2020), the month-end February 2020 inventory units on hand and the computed DIOH for this one item. 

Excel example showing how to display units shipped in 12 months, units on hand and DIOH as of ME Feb 2020.
The monthly columns for April ’19 to January ’20 are hidden above.

The DIOH is computed in column Z, using the 8,412 units shipped for the most recent rolling 12 months, which comes to shipping on average 23.05 units per day.  Then dividing the month-end February inventory of 5,463 / 23.05 units per day means this business is holding 237 days of inventory as of the report date.

Getting a handle on the DIOH for raw materials is a little more complicated.  Generally, raw materials are consumed on work orders, during the inventory conversion process, and are subsumed into their parent sub assembly or finished good inventory item. 

For example, let’s assume you assemble bicycles and then sell them to bicycle shops.  You need to assemble 15 bicycles of the same model.  Each bicycle requires 1 front wheel.  When you start the work order, you have 100 front wheels in inventory.  Once you confirm the work order, you will have only 85 front wheels in your inventory, assuming your Bills of Material are set up correctly of course, and 15 new assembled bicycles ready for shipment.  In the case of raw materials, you have to get reports that detail how many units are being consumed on work orders to determine the DIOH for each raw material inventory item.  How you do that is beyond the scope of this article, but can be done fairly easily with a data warehouse, where you collect work order confirmation details outside your ERP system.

Gaining Actionable Intelligence Using DIOH

Gaining actionable intelligence using DIOH starts with monitoring trends.  In the Inventory Metrics graphic below you can see that we are measuring the Net Inventory dollars on the left-side Y axis and DIOH on the right-side Y axis.  This graph includes how the business ended both 2018 (200 DIOH), 2019 (208 DIOH) and the currently completed month-end February result of 223 DIOH.  The dashed line reflects improvement over the remainder of the year, trending down to around 190 DIOH by 12/31/20.  Looks like we will need a few initiatives to get there.

Example of inventory metrics with invdentory dollars on the left-side axis and DIOH on the right-side axis.

The actionable intelligence from the above is that inventory is continuing to grow faster than sales.  That’s a problem.  Step #1 is to find an industry benchmark for inventory turns.  Knowing your industry metrics should be an imperative for all business owners to better identify low hanging fruit that can put additional dollars in the bank.

Step #2 is develop more robust inventory reporting to begin reviewing the DSR trends for each finished good item.  In the report below, we have a 6-month DIOH trend for the NM09781 product we discussed above.

DIOH Trending graphic showing 6-month DIOH trend for Part Number NM09781.

Above, you can see the 237 DIOH for month-end February 2020.  In addition to February’s DIOH, you can see the DIOH metrics going back to September 2019, when there was 349 DIOH (or nearly a year’s worth) of inventory on hand.  Every month for the past 6 months, inventory has been trending downward.  This would indicate that our initiatives appear to be working. 

Step #3 is to compute the reorder point (ROP) for this item and work with the Purchasing Department to implement the latest forecast.  The typical ROP is comprised of two components, safety stock and stock consumed during the lead time required to replenish the inventory item (AKA demand over lead time).  Lets run through an example.

How to Compute Reorder Points Using Safety Stock and Demand Over Lead Time

To start our ROP update process, we retrieved the following information from the Purchasing department regarding Item Number NM09781:

Key ROP Parameters for our example Part Number

How to Compute Demand Over Lead Time

First thing to compute is demand over lead time, which is just the amount of stock we plan to ship/sell during the time required from the day a Purchase Order is placed to the day the stock arrives on our dock.  In this example, we have a lead time of 1.00 month combined with a trailing 12-month shipment history of 8,460 units.  The demand over lead time will just be 1/12 of the annual demand or 1/12 X 8,460 Units, which equals 705 Units.  As I said, the demand over lead time is fairly simple.  Now for the safety stock, which is more complicated.

How to Compute Safety Stock

In order to compute the safety stock, we need the following:

  • Service Level % — This is the probability of an item being in our warehouse when an order is scheduled for shipment.  Or 1 minus our Service Level % is equal to the probability of a stock out.  Generally, 95% is a reasonable place to set your Service Level %.  As you increase the Service Level %, the amount of additional inventory required to get 1% higher Service Level begins to get massive.  Conversely, if you want to lower total inventory and are willing to increase your chance of a stock out, then you may want to lower your safety stock to 93%, or whatever.  For our example, we are going with 95%.
  • Lead Time — We already know that our assumed lead time is 1 month.
  • Z Score — Hopefully you had a statistics course or two in college.  A Z Score is the way that we effectuate a statistical estimate of safety stock based on our service level %.  The easiest way to get a Z Score is with Excel.  Just use the following formula which will produce the correct Z Score:  =NORMSINV(SS%).  By using Excel, a 95% service level will produce a 1.645 Z Score.  The lower your service level, the lower the Z Score. 
  • Standard Deviation — We need the standard deviation of demand over some period of time.  Given that we have our lead time and historical demand all summarized by month, we need a monthly standard deviation for our product.  This is fairly simple using Excel, which has a number of methods for computing standard deviations.  Given that we are only going to use 12 months of past shipment history, we are going to use the Student’s T Standard Deviation formula which is: =STDEV.S(Range).  If you have 30+ data points, then use the Population standard deviation formula (=STDEV.P(Range)), which will give a lower safety stock value, reducing the amount of overall inventory required to maintain the same Service Level %.

Here is an example, for the same product we have been discussing, using the 95% Service Level, 1 month Lead Time:

Detail of example of how to compute Reorder points that include demand over lead time plus safety stock.

Though the above is a bit complex, creating a VBA application will make this much easier, which is a service provided by Profitwyse.  The difficult part is establishing a data warehouse were all the information required to compute the analytics can be accessed by a VBA application.  We can help with that too!

One final metric that is helpful for your Purchasing team is to compute a forecast date for the next Purchase Order.  Think of this as the date when the on hand inventory balance reaches the Reorder Point.  In the above example, the information is as of month-end February 2020 (or 2/29/2020). 

Here’s the way to approach an answer to the question when will need to issue the next PO.  Hopefully inquiring minds in the Purchasing Department would like the answer.  As of ME Feb ’20, we have 5,463 units on hand.  The reorder point is 1,275.  Now we just need to determine how many days to get from 5,463–Units on Hand–to 1,275–ROP–the difference being 4,188 units.  Using the information above, we can determine how many days it will take to sell 4,188 units, which is 4,188 / (8,412/365 Days) = 182 days.  Adding 182 days to the ME report date indicates that Purchasing will not need to take action until around August 29, 2020.  Using this report, the Purchasing department can focus on more pressing issues than our example part above.

Final Comments on Days Inventory On Hand

Though we did not mention it above, knowing your industry average DIOH performance or inventory turn over rate is very important relative to providing some objective direction on how aggressively to pursue inventory reduction initiatives.  Regardless, business owners should always take a continuous process improvement philosophy to their inventory improvement initiatives, given that inventory management is a balancing act between maintaining reasonable inventory levels that also achieve service level goals.

If you are a business owner that is in need of inventory management expertise to help implement your own continuous improvement initiative, please contact us today


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.


Display of Receivables definition in purple highlighter


How to Compute Days Sales Receivables or More Commonly “DSR” and Improve Cash Flow

Why is Knowing How to Compute Days Sales Receivables Important to my Business?

Days Sales Receivables (DSR) is an efficiency metric for determining how quickly a business is able to convert a receivable into cash.  Sometimes it is referred to as Days Sales Outstanding or DSO.  In either case, it is the same business metric.

Since cash is king, the faster a business is able to convert a receivable into cash, the better.  Essentially, a receivable is created when a business extends credit to a customer.  Some businesses do not need to extend credit at all, and consequently have $0 receivables.  For example, McDonalds does not extend credit when you purchase a cheese burger.  You give them the cash and you get your cheese burger.  If you were to look at McDonalds SEC filings and review the level of receivables on their publicly listed balance sheet, you would see a very small number given their revenue size. 

Learn how to compute days sales receivables, or commonly know as DSR, and learn ways to utilize this valuable metric to improve cash flow. Click to Tweet

Most businesses have to extend credit to their customers.  For example, if you sell products to retail stores as a wholesaler, then you are going to have to extend credit to your customers.  If you were able to negotiate 30-day terms with all your customers and they precisely paid in 30 days, then your DSR would be 30 days.  In reality, no business collects in precisely the number of days they negotiate with their customers and generally the resulting DSR is worse than what is negotiated for various reasons.  But you want to pay particularly close attention to your business’ overall DSR performance to ensure it does not drift upward, or if does drift upward you understand the circumstances and have someone “bird dogging” customers for payment as well as enforcing credit holds when merited.  As a CFO Services firm to midsize businesses, we frequently see business owners ignoring their overall DSR trends until they have a significant liquidity problem that could have been prevented by more diligently monitoring their metrics. 

How to Compute “Days Sales Receivables”

DSR like most financial metrics is retrospective in that you have to use historical business activity to compute the current month metric.  There are basically two methods: 1) the look back method; and 2) the rolling 12-month method.  I made up the name of the second method, because I am not sure if it has an actual name, but it is the more commonly used.  This is because it is much easier to implement the rolling 12-month method in a spread sheet vs. the look back method.  We will start with the look back method.    

Computing DSR Using the Look Back Method

In either methodology, you start with the month-ending accounts receivable balance for a business.  Though both methods given fairly similar results, the look back method is more well suited for monitoring DSR at a more granular level.  For example, you would probably want to use the look back method if you were a construction firm and needed to understand DSR at a customer level.  In summary, you start with the month-end receivable balance and you begin subtracting the monthly sales from the outstanding receivables balance, adding the calendar days from each month, until you go back far enough to bring the balance to $0.  Typically you end up having to prorate the final month.

DSR calculation example using look back method

The example above reflects a single customer (Customer XYZ), with a month-end October receivable balance totaling $450K.  On the next row you can see the monthly invoicing to this customer.  To compute the DSR, we work backwards beginning with October 2021, by subtracting the monthly invoicing from the month-end October receivable balance, accumulating the days in each month.  Starting in October, with a $450K balance, we subtract the October 2021 invoicing (invoicing equates to monthly sales) and get a balance of $375K.  We then subtract the $375K balance from the $43K September invoicing to get $332K.  This goes on until we get to $0K remaining receivables which happens in March.

The invoicing in March is greater than the $73K remaining balance from April; consequently we do not add the full 31 calendars for March, but prorate the 31 days.  The March days are prorated by dividing the remaining balance ($73K) by the total March invoicing ($80K) and multiplying that fraction by the number of calendar days in March (31) or $73K Remaining / $80K Total Mar Invoicing * 31 Days = 28 Prorate Days for March.  Then we add the days for March through October and get 242 Days Sales Receivable.  This is huge DSR and indicates that the business have 242 days worth of invoicing extended as credit to this customer or about (242 Days / 30 Days Per Month) 8 months of work. 

As you can surmise, using the look back method is a little complicated.  But it does provide accurate DSR metrics when looking at an individual customer or in situations where invoicing/revenue is lumpy throughout the year.  To easily implement the look back methodology, you will need a custom function macro.  Contact us if you would like a copy of the function macro to perform this calculation.  If your revenue tends to be relatively consistent, then you want to use the rolling 12-month methodology.

Computing DSR Using the Rolling 12-Month Method

As mentioned earlier, the rolling 12-month method is much easier to implement in a spreadsheet application.  The method works well for businesses that have fairly stable revenue, with multiple customers.  Here is an example of the rolling 12-month method:

Example of computing DSR using the 12-month rolling methodology.

In this example, we have hidden the April through August columns for brevity.  Here we are computing the DSR for ME December 2021.  Since this is a 12-month rolling method, we accumulate the prior 12 months of sales, which was $12,685K.  Then we need to compute the average daily sales, which is $12,685K / 365 Days = $34.75K per day.  Now that we know what the average per day sales equates to, we just need to divide the month-end December receivables balance by this factor to determine that we have 46 days of receivables currently (or $1,585K / ($12,685K / 365 Days) = 46 DSR). 

The following month, we would divide the January 2022 month-end receivable balance by the 12-month average daily sales, which would include sales for February 2021 through January 2022, and so on and so forth.

Gaining Actionable Intelligence Regarding DSR

The first step is gaining actionable intelligence is to put together a time phased financial plan that includes a forecast for both revenue and receivables.  The starting point for this exercise would start with computing the DSR trends for the previous year to gain a good understanding of collection effectiveness.  Using the prior year trend as a baseline, you can develop a generalized plan as to the DSR target.  With the DSR target in hand as well as the revenue projection, you can back into the receivables plan. 

Graphical Trend of DSR for 2020

This is a good template for tracking receivables.  You can see that on the left Y axis we are measuring monthly dollars of receivables.  On the right Y axis we are measuring the DSR.  The 2018 and 2019 year-end total receivables and DSR values are posted for reference.  The 2018 ending DSR was 52 (see the yellow dot with the 52 below it) and 2019 ending was 51 days.  The total receivables dollars increased from 2018 to 2019, from $1.5M (see the bottom of the column) in 2018 to $1.9M in 2019, while the net DSR decreased a day.  This occurred because 2019 sales were 20% higher than in 2018.  This is a good example where you cannot just look at the total receivables to figure out how efficiently you are collecting receivables. 

You can also see that sales have been strong in 1Q 2020 and collections through February have deteriorated to 63 days of receivables on the balance sheet.  Now is a good time to take a deeper dive into collections to better understand what is driving this increase to make sure there are no major issues.  Using a tool similar to the above display helps business owners know when to dig into the detail and when to just tweak business processes, such as the number of days of credit extended to new customers, to affect cash flow.

Benchmarking and It’s Relevance to Managing DSR

Benchmarking is essentially the process of comparing a company’s financial metrics against an industry cohort of similar size.  There are a number of places to get industry cohort metrics, one of which is the Almanac of Business and Industrial Financial Ratios, written by Leo Troy, Ph.D. and published by CCH. 

We have posted a copy of the page that relates to the this business below.  The columns relate to the asset level with the industry group.  For our company referenced above, the Receivables Turnover metric was 14.9x.  To convert the Turnover to DSR we have to divide the number of days by the Turnover 365 Days / 14.9x = 24 Days.  Given that our business DSR is in the low 50 days, this would indicate that other businesses within this industry are doing much better.

Benchmark Almanac

The 24 Day DSR more than likely requires more research.  The cohort businesses may have a greater level of cash sales than our example company.  But if our example company were able to achieve a DSR at 24 days, the Receivables would be cut in half increasing cash flow about $1.2M.  Benchmarking is helpful, but the key is continuous improvement.  The business ended 2019 at 51 days, which is better than 2018.  With resources, the business should attempt to bring the DSR closer to 45 days by implementing strategies with key customers.

Final Comments on Days Sales Receivables

Managing receivables effectively is a management imperative for all businesses that extend credit.  Your ability to management this important asset will be the difference between a business owner being a prince or a pauper.  Learn the basics.  Set up metrics so that you can monitor monthly changes in efficiency, such as the graph displayed above, and ask probing questions when trends are going in the wrong direction.  At least quarterly, go through the sub-ledger details with your accounting staff and identify actions for improving collections and write offs.  If you need a copy of the look back method Excel function macro, please contact us.


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.