​We are living in uncertain times. That’s always true to a certain extent, but right now with the Corona virus pandemic it’s more extensive than we’re used to. Governors are running scenarios to determine what is the best way to slow the spread of the disease while not choking off all economic activity. All businesses are under strain as people stay at home and non-essential businesses are closed. What should you do for your business?
 
I’d suggest that you copy what the Governors are doing. Run scenarios to determine what the dangers are. Use a model and adjust that to fit events as they happen. I’m going to give you some ideas on how to set that up. It’s a bit of work to set up, but once it’s in place it will be easy to update for changes.
 
You don’t have time to install planning software and you certainly don’t have time to learn how to use a new system. I say use your current spreadsheet software, Excel or something similar. I’ll be using Excel when I talk about things you can do in your spreadsheet.
 
Your first step is to create a baseline projection. Take your last twelve months so you get annual numbers. We’ll use that as your base. Get those numbers into a spreadsheet. You can put them in as the line items that show up on your financial statements or you can go detailed and put them in from your trial balance that lists all your general ledger accounts. Now take those numbers and spread them out over the number of periods you want to project. If you want a one-year projection by month you can divide everything by 12 and spread it out over 12 columns in the spreadsheet. This can be your baseline. In fact, to make it easier you can call this tab on your spreadsheet “Baseline”.
 
Now you’ll want to set up some tabs on your spreadsheet that refer to your baseline numbers to build the reports you want to see. You can name these tabs with the report name but be sure to also include “Baseline” in the name.
 
Next, you’ll want an area where you’ll be able to make changes and play with different scenarios. You’ll want this to be in the same format as your baseline tab. The easiest way to do that is to simply copy your baseline tab and rename it. In Excel you can hold down the Ctrl key and then click and hold your mouse on the baseline tab. Now still holding the mouse button down drag to where you want the new tab to go then let go with your mouse. A copy of the baseline tab should appear. Rename it as “Scenarios” Then go into that tab and erase all your baseline numbers leaving the titles in place. You can now put in different scenarios, or what if’s if you prefer and it will be in the same format as your baseline data.
 
My suggestion for how to use the Scenarios tab is that you set up separate tabs where you calculate the different impacts of your scenario and have the Scenario tab pull in the numbers from your calculation tabs. The numbers you put in here are the change from the baseline. So, for example, if your sales were $100,000 and the scenario had them fall to $75,000 the entry in the scenario spreadsheet would be for ($25,000) and not $75,000.
 
Once you have this built out, you’ll need a way to get your final numbers. That is fairly simple to do. Using the same technique as before, create a copy of the Baseline tab and change the name of this one to “Combined”. Once again, erase all the data leaving the titles in place. Now choose your first cell that would have numbers in it. Set up a formula to add the same cell from the Baseline tab and the Scenario tab. This will give you the combined number of your two tabs. You can copy this formula to all your cells with data and suddenly you have all your numbers. You can now create reports to show the results of your scenarios.
 
That sounds like a lot of work to create those reports again doesn’t it. There is a way to make it easier. For the reports you set up earlier with the baseline copy each tab and rename it. Now you can use the replace function to get this new report to point at the Combined tab instead of the Baseline tab. In Excel you can find this function on the Home ribbon under Find & Select. When the function opens up, you’ll be asked to put in “Find what:” and “Replace with:”. You’ll want to replace all the references to the Baseline tab to the Combined tab. In formulas that refer to other tabs the tab name is in single quotes. We’ll use that, so you can put ‘Baseline’ in the Find what box and ‘Combined’ in the Replace with box. Then click the Replace All button at the bottom. Presto, your report now pulls all it’s numbers from the Combined tab. You can now see the results from your scenario.
 
You can also use this technique to generate reports that show just the changes made from your scenarios by changing the references to ‘Scenarios’. This would help you to be able to explain the changes from the scenarios.
 
Hopefully, this brief overview gives you enough of a picture to develop your own tool to be able to try to figure out some of the uncertainties facing you. Stay healthy. 

With the Corona virus keeping everybody at home it’s a strange time for business. Personally, I’ve been laying low and feeling a bit low as I watch what the virus is doing to the world. So, apologies for not keeping up with my schedule for blog posts. I’m back to writing now and will try to keep up with my blog.
 
Most of the work I do for my business I can do from my home office. I was in between contracts when the Stay at Home order was put in place in Minnesota. I have since picked up a contract for a sales tax audit and am working on that.
 
When I’m done with that, I’ll hopefully land another. A common problem with those of us who are self employed is that when you’re working on a contract if you focus your efforts on that you end up with no work when the contract is done. You have to walk a fine line between doing your work and building your sales for what’s next. My guess is that my services that find money for people probably will become a bit more urgent for some people.
 
Now that I’m back to writing I’m planning on keeping my same schedule of posting every other week. 

​This post is a preview of things to come. As I’ve looked at what I want to write about I realized that some of the posts would be interconnected. So, this is a quick introduction to what I’m planning.
 
You’re going to have to work with data if you want to be able to show people what their numbers mean. And computers seem to record everything, so you’ll need to be able to work with large amounts of data.
 
Your goal is to know what causes fluctuations in expected results, what lies ahead and what can be done to improve things. In a nutshell you want to know what’s generating profits and what’s generating losses. In my coming posts I’ll talk about how you can look at your data to find out this information and what you can do with it. 

Part One of this series was an introduction to breakeven analysis. It defined what breakeven analysis is and gave a quick example of how it is calculated. Part Two talked about using breakeven analysis for upper level analysis of a business. This part will discuss using breakeven analysis to help with decision making for specific projects.

I did an analysis of margins by product line for a company. They were selling products from different manufacturers. Looking at the results after the data had been compiled it was clear that one of the manufacturers had a better profit margin than the others. We discussed how to shift our product mix towards the more profitable items. One of the suggestions was to increase the commissions on those products to get the sales force to highlight those products to the customer. I analyzed this proposal.

There was no way of knowing the exact amount that the sales mix would change with different commissions. And thus, there was no way to give an exact number for the results of the change. I decided to use breakeven analysis to assist with the decision. I wanted to show at what point the costs of increased commissions would equal the extra margin generated from shifting the product mix. Then the decision would be based on whether or not we could beat that number.

Ok, now I’m going to throw out some numbers to use so you can see how I calculated the breakeven point. These are, of course, made up numbers just for discussion purposes. ABC Company sells 1,000 units per month in total. Sales are divided between Product Line A with 80% and Product Line B with20%. Product Line A has a sales price of $250 and a cost of $200 per unit. Product Line B has a sales price of $300 and a cost of $200 per unit. ABC Company pays its salespeople a commission of $25 per unit. The proposal is to change the commission rate on Product Line B to $50 per unit.

Now let’s do the calculation. The cost of the increased commissions would be the difference between the new and the old commission rates multiplied by our current product mix. Current sales of Product Line B would be 200 units (1,000 total units x 20% share). The commission difference is $25 ($50 proposed less $25 current.) So, the cost of the increased commissions would be $5,000.

Next, we need to calculate the change in product mix that would be needed to cover the $5,000 cost. The margin on Product Line A is $25 (Sales price $250 less cost of $200 and less commission of $25.) The margin on Product Line B is $75 (Sales price $300 less cost of $200 and less commission of $25.) The margin difference is $50 per unit. However, with the new commission rate Product Line B would have a $25 additional cost from commissions. The new margin on Product Line B is $50 (Sales price $300 less cost of $200 and less commission of $50.) The margin difference is $25.

In order to breakeven then the mix would have to change by 200 units. This is calculated by dividing the additional costs by the margin difference ($5,000 divided by $25.)

Having this data changes the discussion from a general, “Maybe this will work” to a more concrete, “If we make this change will we be able change the product mix by at least 200 units?” We can’t know for certain what the final impact will be, but by putting a minimum number out there might generate a consensus around whether that number can be beaten.

The company I did this for decided to increase their commissions on the higher margin product line based on this type of analysis. The change in the commission rate incentivized the sales force to sell more of the higher margin product and the breakeven target was met and exceeded easily.
​
I hope you can see how having a target can clarify decision making. And I also hope you’ll be able to use this type of analysis. The next post in this series will talk about a breakeven analysis I did for a private school. 

I don’t remember the source of what I’m about to tell you about. I remember reading it in a magazine in the late 1980’s or early 1990’s. Also, the numbers might have been different, but the point remains the same. With those caveats, I’ll write about what I remember.
 
There was a study done in a manufacturing plant. It found that if two workers, one who could read at a fifth grade level and one who could read at a sixth grade level, working together would be able to understand instructions written at a ninth grade reading level.
 
This has stuck with me over the years. It’s a good proof that we can accomplish more working together than we can on our own. Each person brings their own strengths and talents that complement the others. Different viewpoints have value and need to be listened to. None of us is perfect.
 
So, those companies who have a goal of having a diverse workforce are building a competitive advantage by having different viewpoints from their workers.

​Part One of this series was an introduction to breakeven analysis. It defined what breakeven analysis is and gave a quick example of how it is calculated. This part will talk more about the calculation and using it to analyze a business.
 
Let’s set up an income statement for a sample manufacturing company and then use them to calculate the company’s breakeven amount.
 

​Looking at this we can calculate a breakeven number for the company. The first step is to determine the variable margin, which in sales less the variable costs. The variable costs would be those that vary up and down with sales, the column on the right that is each line’s percent of sales would stay the same for variable costs. In this sample the variable costs would be Materials and Labor. So, the variable margin would be $13,200 [24,000 sales less 6,000 materials and less 4,800 labor]. The variable margin percentage would be 55% [13,200 variable margin divided by 24,000 in sales].
 
The next step would be to add all the fixed costs up. The total fixed costs are $12,500 [3,600 Overhead + 1,200 Mfg Expenses + 7,200 SG&A Expenses + 500 Interest and Other]. From our calculations above we know that each dollar in sales results in 55 cents of variable margin. We now have all the elements for the breakeven calculation.
 
Breakeven is equal to Fixed Costs divided by the Variable Margin. So, our breakeven would be $22,727 [12,500 divided by 0.55]. And what do we do with this number? Read on.
 
Now that you know the breakeven numbers you can use this as a diagnostic tool for quickly analyzing financial statements. For our example, say the next period’s income statement shows sales of $20,000 and a loss of $1,500. You can easily see that because sales were less than the breakeven number, we didn’t have enough sales to cover our fixed costs.
 
What if sales were over breakeven, but we still had a loss. You can check the components of your breakeven calculation for the period to see what’s off. Is the variable margin lower? Are the fixed costs higher? Looking at it this way, you’ll be able to easily know what to focus on so you can explain the financial results.
 
My next post in this series will focus on using breakeven analysis in a more focused way. 

​Hello all. I’m sorry I’ve been away from the site for a while with Christmas busyness and other things. I’m back and writing again. I started a series with my last post, and I will get back to that. But for my first post of 2020 I wanted to talk about New Year’s resolutions.
 
Businesses make New Year’s resolutions. But they call them “budgets” and “strategic plans”. They help them to know where they are going and how well they do things. They expect to measure themselves against these goals.
 
We make “New Year’s resolutions” and nobody really expects to keep them. I’d like to recommend to you that you take your resolutions and turn them into goals. Have a strategic plan for yourself; both career-wise and for your life. Make them something that you will track and will also achieve. You may not reach all your goals. Businesses often miss their budgets. But in the trying and in the measuring yourself against them, you will have a better understanding of yourself.
 
One of my goals for this year is going to be to write blog posts regularly every other week. So far, I’m on track for the year. (For the decade also!) Check back in two weeks to see if I can keep it up.
 
Have a great year.
 

​I’m going to do a series of posts on breakeven analysis. This is an underutilized tool you can use in a variety of ways. This first post will be an introduction to breakeven analysis. Future posts will discuss how to calculate it and different ways it can be used.
 
Breakeven analysis involves finding the point where the net profit for something is zero. So, when there’s no profit you’re just breaking even. It is an easy concept to explain to non-financial people. They can understand the concept of it even if they don’t understand the actual calculation. I’ve been in meetings where I’ve presented a breakeven analysis to a group of non-financial people and you would have thought I had just invented sliced bread from their reactions.
 
The calculation looks at what items are variable, that is they fluctuate based on the number of units involved, and items that are fixed, they stay the same regardless of volume.
 
For example, let’s say a company was considering purchasing some equipment but was unsure if the investment would be worthwhile. You know that the cost of the equipment is $25,000. That is a fixed cost; it won’t change based on the number of parts sold. The equipment will allow the company to manufacture parts that will cost $5 less than with the old equipment. That is a variable cost; it changes as the number of parts sold changes.
 
So, the breakeven number of parts the company needs to sell would be 5,000. This is calculated by dividing the fixed costs of $25,000 by the variable component of $5 per unit. (25,000 divided by 5 equals 5,000.) Then you can compare that to the actual number of the parts sold in the past to see if 5,000 is a reasonable number.
 
Think of what the reaction would be if instead of a discussing payback periods and return on investments with non-financial managers, you say we need to sell 5,000 parts to cover the cost of the equipment and in the past year we sold 12,000 parts.
 
That was just a simplified breakeven analysis, but you can see that this can be a powerful tool for communicating the financial picture.

I’ve spent some time in my last posts showing you how to use Excel to work with data; filtering it and converting text files to a format usable to Excel. What’s the value of that?
The value is that your data can tell you a story if you can work with it. Excel (or the spreadsheet software you use) is your best tool for ad hoc analysis of your data. You can’t predict everything you’re going to need to analyze and how you’ll want to show it. So, you need something that is extremely flexible. Excel fits that and you already know how to use it.

You’ve heard the expression, “can’t see the forest for the trees.” In a work setting the impact of that translates to the people doing the work aren’t good at seeing trends and patterns. On the other end of the scale is the executive who boasts about flying at 30,000 feet to manage the business. Translated that means they’re not into details.

There is a lot of room between the worker who can see that the trees he can see are good or bad and the executive looking out the plane window and seeing a forest below. Using the data allows you to target what you look at. To stick with the metaphor, you can look at the forest from 30,000 feet then dive to 5,000 to see how healthy the forest is. You can zoom to areas that look bad perhaps even flying at tree top level. I’ll give you a couple examples of when I was able to use data in this way.

I worked at a manufacturing company. One of our customers set up a supplier summit; they invited all their vendors to come for a presentation. Prior to that I analyzed the customer. I had put all sales history into data I could work with. The sales history had customers and part numbers along with the sales and associated costs. Looking at the data made me do some more digging into the customer’s parts because the profit was very low. The customer was ordering parts in small batches but needed us to have any of the parts they might order available at any time. This caused us to keep large amounts of inventory. Looking at all of this and the extra costs incurred from meant that we were not making any money on this customer. At the supplier summit the customer said they were requiring all their vendors to give them a 30% price cut. We said no. Even though we lost the customer, we were better off.

Later at the same company we were told by operations that another customer was bad. They were too much trouble for the amount of money we made on them. Looking at the data showed a very profitable customer, even with the extra costs for their requirements. I wanted to present this in an easily understandable way. I chose to use our financial statement that everyone was used to reading. Using the P&L as a template I put our results from the last 12 months in the first column. In the second column I put the sales and costs from this customer. Finally, the third column showed where we would be without that customer. Using a report that people were familiar with allowed us to focus on the results and not on how to read the report. That third column looked pretty awful and we stopped hearing suggestions that we should drop the customer.
​
If you can get the data and work with it, you can look at all the trees in the forest and see the ones you need to see. Because successful companies know they profit and where they lose. 

​What do you do when you have a blog entry to write, but the seventh game of the World Series is on?
 
You make a choice. In this case, since the blog is not time sensitive, I elected to watch the game. I’ll be back with a post next week.
 
Congratulations Nationals on becoming the second Washington franchise to win a World Series!