dding Labor and Overhead Costs to QuickBooks BOMs

Ignite Profitability By Adding Labor and Overhead Costs To QuickBooks BOMs

The Issue — Weak Cost Accounting Processes

Nearly without fail, when we engage with a new manufacturing client that is using QuickBooks to build finished goods and subassemblies, the business owner and accounting team are excluding the cost of direct labor and overhead from their BOMs.  The impact of not adding labor and overhead costs to QuickBooks BOMs is an understatement of the actual cost of inventory.  This artificially increases gross margins, as well losing the ability to analyze labor and overhead efficiencies.  Learn how to overcome weak cost accounting by adding labor and overhead costs to your QuickBooks BOMs which will improve profitability and generate actionable insight into your decision making process. 

How to Create Fully Costed BOMs Using QuickBooks

First – Creating the Labor Absorption Factor

We are going to take you through a fairly simplistic manufacturing scenario, where the main product is bicycles, with just two varieties–Men’s Bicycle and Women’s Bicycle.  Each bike consists of the 2 brake calipers, 2 brake levers, a frame, a crank, a front wheel, etc.  Our manufacturing supervisor estimates that it takes, on average, one of troops 1.5 hours to assemble and test a completed bike. 

The first step before we create the Inventory Assembly in QuickBooks, we need to create the Labor Absorption and Overhead Absorption factors that will be integrated into each assembly.  In an early set of articles, we went into detail on how to generate direct labor and overhead rates, as well as how to analyze them during month-end accounting review. 

The Labor Absorption factor represents the fully burdened direct labor cost, per hour, for an average direct labor employee.  We wrote an earlier post on how to compute direct labor rates and determined that one hour of fully burdened direct labor equals $28.47. 

To create the Labor Absorption factor, in QuickBooks navigate to Lists — Item List, and select New Item (Cntrl + N).  Here is the detail set up for that Labor Absorption factor:

Display of Labor Absorption factor in QuickBooks, that is attached to Labor Variance account on P&L.

The keys here are selecting Non-Inventory Item, Cost at $28.47, and choosing a Labor Variance account in Cost of Goods, which will be the account that receives the Credit side of the Build Assembly process.

Second – Create the Overhead Absorption Factor

The Overhead Absorption factor will be used to absorb overhead costs into the inventory item.  Similar to the other factor, we wrote an earlier blog post describing how to develop Overhead Absorption factors or rates and determined the per hour rate to be $29.30.  Overhead will be absorbed as a function of direct labor absorption, in that when we capitalize one hour of direct labor, one hour of overhead absorption will be included as well.  This process can be modified to absorb overhead based on machine time or whatever is the key metric driving manufacturing volume.

Using the same process as for the Direct Labor factor, here is the Overhead Absorption factor Item set up:

Display of Overhead Absorption factor in QuickBooks, that is attached to Overhead Variance account on P&L.

As you can see above, the only real difference other than the name of the Non-Inventory Items is the Cost and the Expense account, which in this instance was Overhead Variance.

Third – Add Labor and Overhead factors to Inventory Assemblies

As mentioned earlier, our example company assembles Men’s and Women’s bicycles.  Referenced below is the Inventory Assembly for a Men’s Bicycle.  Take a look toward the bottom, and you can where we added both Direct Labor and Overhead factors to our BOM.

Display of Men's Bicycle assembly, highlighting the Direct Labor and Overhead absorption factors at $28.47 and $29.30 per hour, respectively.

As you review the BOM above, you can see that we have the Direct Labor requirement to 1.5 hours of direct labor required to assemble one bicycle.  From this, you can also see that each bicycle is going to absorb $42.71 in Direct Labor and $43.95 of Overhead, bringing the total cost per bicycle to $173.41 This means that there is $86.75 in direct materials, $42.71 in labor and $43.95 in overhead.  As you can imagine in this case, if we were to exclude direct labor and overhead from our BOM, we increase the chance of making poor pricing decisions without the full picture.  Also, this will allow us to account for certain models that require more or less labor to assemble in our pricing decisions.  

How to Structure a Manufacturing Profit and Loss Statement in QuickBooks

Now you know understand the mechanics of adding labor and overhead costs to QuickBooks BOMs, we will now describe how to make sense of it.  We probably should have started with this, but to enable your ability to segregate direct labor, overhead and G&A expenses, you will have to arrange your accounts and functional organizations accordingly.  QuickBooks has two ways to accomplish this objective, which is through the Chart of Accounts or through Classes.  Generally, we recommend to smaller clients, that have only a small number of functional department (e.g., a direct labor work center, a factory overhead department,a  sales and marketing department, and finally an Admin department), to make the segregation using the chart of accounts without having to add the burden of assigning a class to each transactions  In cases beyond that simple structure, using the Chart of Accounts becomes too burdensome and we resort to Classes.

Direct Labor Work Center Account Set Up

QuickBooks accounting listing for Direct Labor workcenter

The only unique thing here is the 51090 DL Transfer account, which we will describe shortly.

Overhead Department Account Set Up

Overhead Department Account Strucuture

This should look fairly standard for an Overhead Department.  We have decided that all the Facility costs will be absorbed into product costs.  Above, you can see the issue with using the chart of accounts rather than classes to segregate the organizational functions.  For example, we have preceded the Airfare account with “MO” for Manufacturing Overhead.  If we were using Classes, there would only be an Airfare account.  But we are trying to keep it simple given the size of the organization.

Other Cost of Goods Set Up

QuickBooks other cost of goods account chart of accounts set up

We already discussed the Labor and Overhead Variance accounts, which initially will reflect the monthly absorption.  The 50010 Cost of Goods account is where QuickBooks is posts the cost of goods when product is shipped.  The EE&O Expense and Scrap accounts are beyond the scope of this article.

Let’s Build Some Product

We created the custom report below to illustrate how our Labor and Overhead Absorption factors affect the P&L.  We completed 5 different Build Assemblies or Work Orders, where we built 3,150 bicycles, generating 4,725 hours of earned labor.  The 4,725 hours of earned labor equated to $134,521 earned labor cost and $138,443 of absorbed overhead. 

Summary of work order labor and overhead absorptin with Earned Hours totaling 4,725 for month-end January 2020

The Month-End January P&L Before Closing Entries appears below.  Take note of the “Total 50000 – Cost of Goods” summary, which as this point only includes the Direct Labor and Overhead absorption created when we completed the Build Assemblies, as well as the total Direct Labor–$151,819 and Overhead–$106,115. 


ME January Closing Cost Accounting Entry

With all the month-end January expenses post, we can post our final cost accounting entries, moving the Direct Labor to the Labor Variance account and the total Factory Overhead to the Overhead Variance account.  This is the Journal Entry required:

Display of QuickBooks journal entry that transfers the month-end January Direct Labor Department and Overhead Departments to their respective Variance accounts.

Post-Close Cost Accounting Analysis

With the final cost accounting entries posted to the January P&L, we can begin the analyzing our  results.  This is how the post-close P&L now appears with the Direct Labor and Overhead transferred to the variance accounts:

Profit and Loss result after transferring Direct Labor and Overhead Spend

As you can see, the Direct Labor and Overhead departments total expense has transferred to the Labor and Overhead Variance accounts.  The net is that operations produced $15K of total favorable variances, with an unfavorable $17K Labor Variance being more than offset by a $32K favorable overhead variance.  The question now is should we be satisfied with that result or concerned.  Digging a little deeper with help us better understand what is happening.

Direct Labor ME Results Analysis

The month-end January Direct Labor variance was an unfavorable $17K.  Is that bad, good or nothing notable.  Well we really need to compare what we planned the variance to be vs. what actually occurred.  Let’s start with the payroll register and see what actually transpired to generate this variance:

Labor variance analysis showing, for January 2020, the $17K unfavorable labor variance is $18K unfavorable to rate and $1K favorable to efficiency, neting to $17K unfavorable.

Reviewing the payroll register and summary analysis above, you will note that there were two payrolls in January and given the time no need for any ME payroll accrual.  The total January payroll was $151K (i).  We are going to adjust down the total payroll hours of 4,922 by the holiday and vacation hours (k+l), to derive the number of actual hours worked (or the number of hours that direct labor employees were available for productive work) totaling 4,706 hours.  As you displayed earlier, the January earned hours were 4,725, which was computed by QuickBooks using our Labor Rate factor/Item.

To compute the Rate portion of the variance we need the actual rate, which is $32.26/hour ($151,819 Payroll $s / 4,706 Available Productive Hours).  To get the variance, we compute the rate difference, which is an unfavorable $3.79/hour ($28.47 plan rate – $32.26 actual rate) times the number of earned hours (4,725) produces a $17,839 unfavorable rate variance. 

To compute the Efficiency component, we need the difference in the number of Available Productive Hours (4,706) vs. the Earned Hours (4,725), which is slightly favorable, times the plan rate.  This produces $541 of favorable labor efficiency. 

Our analysis shows that we appear to have the labor standards set reasonably accurately at this point, but the labor rate variance is a concern.  In this case we would want dig a little deeper to analyze if the issue pertains more to labor rates, the amount of vacation take vs. what we expected and/or the health insurance cost vs. what was expected.  Once we better understand this final component of the labor variance we can determine if there is additional action.

Factory Overhead Variance Month-End Analysis

If you are not familiar with how to compute an overhead absorption rate when creating an annual financial plan, you may want to peruse our post on how to compute overhead rates and generate meaningful analysis, posted last year, to refresh your memory.  For this example, we have used the same Factory Overhead projection used in the earlier post.  You can see below that the January plan reflected spending $96.9K of expense, while absorbing $148K, on 5,052 projected earned hours, producing $51.4K of favorable factory overhead variance.

Summary of 2020 overhead plan showing the monthly absorption variances based on the earned hours forecast, which nets to $0 at year end.

Below we elaborate on our comparison to plan.  In actuality, the business spent $106K (a), against 4,725 earned hours (b), which meant that $138.5K (4,725 hours times $29.304/hour overhead rate) of overhead was absorbed into product costs.  On the surface, we have favorable absorption of $32.3K.  In comparison to the original plan where we were going to absorb $51.2K (f) of overhead, we are in reality $19.8K unfavorable to our 2020 plan.  What happened and should we be concerned?

Summary overhead analysis separating overhead variance between Spend ($9K unfavorable) and Absorprtion ($10K unfavorable) totaling $19K unfavorable to plan.

We really do have something to be concerned with given that we delivered fewer earned hours than planned, 4,725 actual vs. 5,052 planned.  Also, we spent $106.1K in Factory Overhead when we planned to only spend $96.9K or nearly a 10% expense overrun.  In the real world, this would require additional review between Operations Management and Finance on what are the primary drivers of increase spend and what is driving the shortfall in earned hours.  With that information, we could then determine whether or not there were any corrective actions and generate an updated 2020 year-end projection.

Next Steps

We hope you have learned more about why adding labor and overhead costs to QuickBooks BOMs is so important.  Having the ability to compare cost standards to actual results is an imperative for any manufacturing organization that is seeking to closely manage profitability and gain insight into business dynamics that influence success.  With a few more months of actual results, a business owners and managers will deepen their understanding of the interaction between direct labor efficiency, material costs and overhead spending to an extent that they can then proactively influence profitability through cost improvement initiatives.  This would include the identification and tracking of targeted cost reduction activities focused on improving profitability while also maintaining high quality standards.  If you would like to learn more, please contact Profitwyse today.

Picture of the 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.



Use ROE to super charge your business

Use ROE to look under the hood and super charge your business

Looking under the hood requires getting into the detail, which entails getting your hands a little dirty

If you have ever watched a mechanic diagnosis problems on a modern car, you will have noted that the process begins with the mechanic plugging in an electronic scanner.  The scanner enables the mechanic to quickly identify electronic and mechanical issues with your vehicle.  This vastly reduces the amount of time the mechanic has to spend determining the root cause of your problem.  Using ROE to analyze and improve business performance is analogous to a mechanic using a scanner to diagnosis what’s ailing your vehicle.

The actual tool used to analyze ROE is the the DuPont equation (or formula), which is aptly named given that it was developed at the DuPont Corporation way back in the 1920s.  The employee that created this methodology–Donaldson Brown–used the DuPont equation to evaluate, compare and improve DuPont’s far flung business operations.  Of note, Brown later moved on to GM and worked with Alfred Sloan to super charge General Motors from 1936 through 1946, indirectly helping with America’s war effort.  Though Brown used his equation on an expansive scale, the DuPont equation can provide valuable insight and keys to business performance improvement for entities both large and small; consequently, we are going to describe how to use ROE to go under the hood and super charge your business.

So what is ROE?

ROE is a measure of return or profitability from a shareholder’s or owner’s perspective, given that it is the result of dividing net profit by equity.  Because equity represents stakeholders’ investment, ROE reflects the return shareholders are realizing on that investment.  This facilitates a shareholders ability to compare an investment in business XYZ to other investment opportunities.  But there are other reasons to focus on ROE, one of the primary reasons being that ROE equalizes many of the factors that affect businesses in completely different industries, e.g. businesses with low profit margins because of fierce competition or huge investment outlays due to long development cycles and distills all those factors into one meaningful metric–ROE. 

This means that two companies with very different business models, because each company operates in a different industry, can get to the same ROE using different strategies.  But there are other reasons to focus on ROE, one of the primary reasons being that ROE equalizes many of the factors that affect businesses in completely different industries, e.g. low profit margins because of fierce competition, huge investment outlays due to long development cycles, etc., and distills all those factors into one meaningful metric for comparison–ROE.

 For example, J&J a large consumer products, medical device and pharmaceutical company can have a ROE that is very similar to Walmart, a large brick and mortar and internet retail company.  Both have loads of inventory, but J&J has significant assets in accounts receivable, where Walmart has practically none.  J&J generates has huge gross margins (≈65%), while Walmart is fairly thin (≈25%).   So how do these disparate businesses get to relatively the same return, or ROE, for their shareholders?  Read on to learn how…

What is the DuPont Equation or Formula?

The DuPont analysis will help us begin peeling away, or decomposing, the major factors affecting financial performance and provide a “drill down path,†using a little algebra.  This is the getting your hands a little dirty part.  So pull out your financials statements and let us begin…

Initially, we will us DuPont analysis to separate ROE into three components– profit margin, asset turnover (or asset utilization efficiency) and financial leverage.  Later will will further decompose the factors affecting ROE.

Decomposing ROE into 3-part formula

First we begin with the basic ROE formula which is:

               ROE = Net Profit / Equity

Next, we can decompose ROE into Return on Assets (Net profit / Assets) or ROA and financial leverage (Assets / Equity), as follows:

               ROE = Net Profit / Assets * Assets / Equity

ROA measures a company’s return on their total assets.  Again, depending on the industry, some companies have a higher ROA than companies in completely different industries.  But companies in the same industry can produce the same ROE by utilizing financial leverage differently.  The financial leverage, computed by dividing assets by equity, measures how shareholder money is being leveraged to deploy the assets required to generate sales.  A financial leverage equal to 1.0 means essentially no leverage, since shareholders have provided every dollar used to deploy the required assets.  A financial leverage of 3.00 indicates that for every dollar of assets, $0.33 has been provided by shareholders while the other $0.67 has been provided by vendors (in the form of AP) and banks (in the form debt).  Financial leverage increases as shareholders own a smaller piece of total assets, which means our lenders have a greater claim on corporate assets.

Now we decompose the ROA part of our two-part formula into a three-part formula as follows:

               ROE = Net Profit / Sales * Sales / Assets * Financial Leverage

Net profit divided by sales is Return on Sales (ROS), which we can think of as a measure of how effectively a company is able to recover their cost of goods sold after delivering whatever products and/or services they provide, as well as all other expenses.  This is an important metric when comparing companies within an industry; consequently ROS is a good Industry benchmark, but it does not indicate how much money is going into the bank, which is what we really care about at the end of the day.  

Sales divided by assets can be thought of as a turnover metric.  Our turnover metric tells us how fast we are able to turn over assets in support of generating revenue.  An asset turnover metric of 2.0 indicates that a business is able to turnover assets twice a year.  Higher the turnover, the better because it reflects management’s ability to efficiently utilize assets.  Think of assets are just piles of cash that need to be minimized in the pursuit of revenue.

Decomposing ROE into a 5-part formula

Now we can expand the three-part formula to a five-part formula that will help us analyze ROS in a little more depth.  First we are going to remove the tax component from net profit by adjusting our ROS as follows:

          ROS = Net Profit / Sales = Net Profit / Pretax Profit * Pretax Profit/Sales

Then finally, we are going remove the impact of interest expense, which is a reduction to operating margin, to isolate our operating margin or Earnings before Interest & Taxes (EBIT), as follows:

ROS = Net Profit / Sales = Net Profit / Pretax Profit * Pretax Profit / EBIT * EBIT / Sales

Replacing the ROS calculation in the three-part formula with the above, we now have a five-part formula for ROE, which is as follows:

ROE = Net Profit / Pretax Profit * Pretax Profit / EBIT * EBIT / Sales * Asset Turnover * Financial Leverage

Using the DuPont formula above, we can quickly compare companies and begin to see where there are differences in operating efficiency, asset utilization and leverage.  With this information, we can then begin to drill down further.  For example, if we note a significant difference in asset utilization, more than likely that difference is going to exist in accounts receivable and/or inventory.  With detail in hand, we use both days sales receivables (DSR) and days inventory on hand (DIOH) to better understand the differences.

A ROE Example

Assume that we have been tasked with comparing Our Company to another rival in our industry (Company B).  Both companies have similar ROEs, but Our Company has significantly higher revenue.  So we start by lining up the basic financial statements for the two companies as referenced below (note that rather than comparing two companies, this same analysis could be used to compare the same company over time):

Summary financial comparison of our example company to our company B for P&L and balance sheet

As we can see from above Company B generates less revenue, but delivers a very similar ROE.  Since both companies are in the same industries and have the same equity, we need to determine where the differences exist.  We start with the three-part formula, which produces the following result with comments on the results:

Three-part formula ROAE analysis breakdown

Now we can start to see some of the differences that are helping Company B’s performance.   For starters, their ROS is 21.2% vs. Our Company’s 15.9%.  It is not clear what the drivers are of better profitability, but we will get to that.  Our Company has the better asset turnover ratio, generating $0.80 of revenue per asset dollar vs. Company B generating $0.41 of revenue per asset dollar.  Finally, Company B uses financial leverage more aggressively than Our Company, only using $0.50 to fund a dollar of assets vs. Our Company is funding each asset dollar with $0.73 of equity, which is a significant part of the difference. 

Now we will expand to the five-part formula to better understand the differences in ROS, which is below.

Five-part formula ROAE analysis breakdown

Now we can start to see some of the drivers for differences in ROS.  First, the marginal tax rate for Our Company is a bit higher than Company B (28% for Company % vs. 31% for Our Company).  Company B has a less favorable Pretax Profit to EBIT ratio because Company B services more debt (interest expenses).  Finally, Company B has a much better operating margin which could be the result of a number of factors, but provides some hints regarding the difference (most likely this is going to be some combination of better gross margins and lower Operating Expenses).

Using the DuPont Formula to super charge a business

Assume that management would like to see the current ROE increased to 25%.  We can use our DuPont analysis and some graphing skills to analyze the changes required to achieve a 25% ROE.  First we start with a template where we plot the potential ROE outcomes for a 10% ROE line, 20% ROE line and 25% ROE line.  We are only going to use the two-factor formula, which is as follows:

ROE = Net Profit / Assets * Assets / Equity or ROE = ROA * Financial Leverage

On the graph, we will use ROA for the Y axis and Financial Leverage for the X axis.  In order to create our ROE lines, we start with the ROE 10% line and then determine what combinations of ROA and financial leverage will generate a 15% ROE.  To plot the line, we solve for ROA (you can solve for either ROA or Financial Leverage), by dividing the desired ROE by each financial leverage interval we want to plot.  So for example, at a 1.5 Financial Leverage (or interval), to plot a 10% ROE data point we just need to divide the 10% ROE by the 1.5 financial leverage to derive a 6.7% ROA.  By doing the same for all the intervals of financial leverage for ROEs of 10%, 20% and 25%, we get the following:

Graph showing the change in financial leverage over range of ROA %s

Next, we have graphed our current ROE performance as well as extrapolated the current performance diagonally to the 25% ROE goal and computed the ROA and financial leverage along the 25% ROE curve.  We extrapolated out diagonally because this solution requires a measured improvement in both profitability and financial leverage.  The new graph appears as follows on the next page:

2nd example of on how to maximize ROAE

If desired, we can bound our solution by determining the upper and lower limits of our solution by determining what the ROA is required to produce a 25% ROE when holding the financial leverage constant at 1.36, as well as determining what financial leverage is required to produce a 25% ROE when holding ROA constant at 12.7%.  These data points appear as follows:

3rd example showing how to maximize ROAE

In the table below we have summarized a number of ways to go from our current ROE performance to achieve a 25%.  We can now use this data to start to evaluate approaches to actually realize the 25% ROE by looking at gross profit, operating profit and net profit as it relates to total assets.  The question for ROA performance is can we improve profitability without increasing assets proportionally or can we reduce assets while not decreasing profitability proportionally and the same goes for financial leverage.  With this information, we can start to get further into the details adjusting days sales on receivables, inventory turns, days payables outstanding, as well our effective tax rate assumptions, to analyze our alternatives.

Comparison of four different methods of maximizing ROAE: Current; Easy Way to 25%; Hold ROA Contant; Hold Financial Leverage Constant

A more analytical approach using Microsoft Excel Solver

Our Easy Way to 25% presented above, is conceptually easy but in reality probably infeasible.  Certainly, increasing the ROA from 12.7% to 13.9% seems realistically achievable.   Changing the Financial Leverage from 1.36 to 1.80 is huge.  Remember that assets are in the numerator.  Assuming that we are not going to be decreasing equity–the number in the denominator—we have to increase assets 32%+, with the offset most likely going to debt.  This would certainly increase shareholder financial leverage, but seems a bit aggressive unless we have a good uses for all those assets.

Excel Solver is an add-in program that comes with all current versions of Excel.  So you already have access to Solver, you just have to enable its use in Excel.  We are now going to use Solver to help us optimize our ROA and Financial Leverage targets with a little more granularity than was used in the graphical solution.  First we need to break out a few details from our summarized P&L and balance sheet that had been presented on a consolidated basis earlier in this article.  Our company financials now appear as follows:

How using Excel Solver results in more optimized solutions

You can see that we essentially have all the same values as the earlier model, but have broken out cost of goods, operating expense, accounts receivable, inventory, fixed assets, accounts payable, other accruals, debt and our two equity components—earnings (or retained earnings) and shareholder equity.

We are going to want Solver to alter certain aspects of the above financials to achieve our 25% ROE target.  We can change just about anything using Solver, but for now we are going to keep it simple.  So what we are going to want Solver to adjust the following items: 1) cost of goods sold, which will affect our gross margin; 2) operating expenses, which will affect our operating margin; 3) accounts receivable, which will affect total assets and be discernible through changes to our Days Sales of Receivable Outstanding or DSO (This is basically a measure of the number of days of sales represented by the current balance in accounts receivable.  For example, if we $100K in sales in the current month, and we have $100K of receivables, then one could say that we have 30 days of sales in receivables or DSO = 30 days.); and 4) inventory, which will affect total assets as well and be discernible through changes to Days Inventory On Hand (This is similar to DSO, but is based on the average daily cost of goods.  This means that if we have $200K of inventory and we incurred $50 in cost of goods during the most recent month, we would conclude that there are 120 days of inventory on hand ($200K Inventory / $50K cost of goods in current month * 30 days = 120 DIOH.).

We now need to adjust our model to consolidate the values Solver will vary when seeking our target goal.  To accomplish this, we created an area on the worksheet that summarizes the four values to be altered and then link them back to the financial statements as shown below:

Displaying Variables that Solver with adjust: Cost of Goods; Operating Costs; Acct Receivable; and Inventory

The next step is to establish the constraints that Solver will use to find a feasible solution to get us to 25% ROE.   We are going to add the following constraints to our model so that we do not end up with another infeasible 25% ROE solution similar to the graphical solution.  For the first iteration of the Solver model will utilize the following constraints:

Solver Constraints displayed, including: Gross Profit %; Operating Profit %; Days Sales Receivable (days); and Days Inventory on Hand (DIOH)

As can be seen, we are going to add four constraints, which include Gross Margin and Operating Margin target ranges.  The DSO and DIOH metrics will guide Solver to produce solutions that reflect some level of asset efficiency improvement off of current results, which are 150 Days Sales Receivable (DSR) and 279 Days Inventory On Hand (DIOH). 

With our baseline model set up and constraints defined, we need to get all this information loaded into Solver.   Solver is available on nearly all Excel installations, but Solver needs to be selected as an available Add-in.  In Excel 2016, this is done using the File tab, Options, and Add-ins screen, which generated the following dialog box on the next page:

Graphic showing how to install Solver on your Excel installation

Note that in the above example, Solver Add-In is beneath the Inactive Application Add-Ins category.  We select Go… which brings up a dialog that allows us to check the Solver application, which will then move the Add-In into the Active Application Add-Ins category. 

In Excel 2016 (it will available in a different location if you are using an earlier version of Excel), Solver is available on the Data Tab, in the Analysis group as shown below:

Graphic showing how to call up Solver once it is installed

We just click on the Solver menu option to begin defining our model.  The first step is to define the goal (25% ROE) and the location of the ROE calculation (cell I31 in our example spreadsheet).  Next we need to identify the location of the four values to be varied–gross profit, operating profit, DSO, and DIOH (cells M8:M11).  The last step is to create the constraints.  Using the add buttons, we added constraints for gross profit, operating profit, DSO and DIOH.  The buttons to the right of the constraint dialog were used to add the eight constraints (a <= and >= for each constraint).  The completed Solver dialog for our initial model appears as follows on the next page:

Display showing the Excel Solver interface and how the constraints and variables are added to the model

With our target set, variable cells identified and constraints established we select the Solve button to run our initial model.  After our initial run, Solver responds back that it could not find a feasible solution given our constraints.  If we check the “Reports†option, we get additional information on which constraint(s) is preventing Solver from finding a solution.  Below, we have captured both the Solver Result indicating that there was no feasible solution as well as the feasibility report identifying our Operating Profit constraint (maximum of 35%) as the variable that needs to be adjusted before Solver can find a solution.

Graphic displaying an example of the type of errors Solver may generate

With this information, we now increase the Operating Profit maximum slightly from 35% to 36% and rerun the Solver model.  To change the maximum on the constraint, we just need to update the values on the constraint table.  After increasing Operating Profit percentage and rerunning the model, Solver was able to find a feasible solution, which appeared as follows:

Graphic displaying the optimized ROAE result using Solver

Findings from Solver Results

The DuPont model factors are summarized at the bottom left of the graphic above and shows us achieving our targeted 25% ROE with an ROA of 18.3% and an unchanged financial leverage.  Note that we are not changing liabilities, nor equity; consequently assets are going to remain at $750K.  We could build a model to vary liabilities and equity, but for now we are keeping the model fairly simple.  Given that assets are going to remain the same, the financial leverage is going to remain constant.  But the focus in this model should be on what happens to cash.  In the beginning model, we set cash to $0.  As we push for more aggressive accounts receivable and inventory assumptions, then cash will increase to maintain assets at $750K.

As you will recall from our graphical solution, we just duplicated our graphical solution that held financial leverage constant and adjusted ROA to achieve ROE of 25%.  But with this model, we have much greater insight into what is required to achieve this goal, such as how much surplus cash can be created by gaining more efficiency in DSR and DIOH, as well as where the constraints exist in our assumptions.  With some time and effort this model could be modified to allow adjusting liabilities, if desired.  With more time and experimentation with similar models, you can make more realistic models that accurately reflect a given situation that identifies various strategies for super charging your business!

What would Donaldson Brown think today?

I am guessing that Mr. Brown would be overjoyed with the ease with which he could deploy his DuPont formula to optimize financial performance for both DuPont and GM.  Hopefully this blog post inspires you to take action and use ROE to go under the hood and super charge your business.

If you are a business owner that is looking for ways to leverage financial planning, cost accounting and/or data analytics to improve and grow your business, please contact us today


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.