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!
In this post I’m going to show you how to work with text files and turn them into something useable within Excel. Sometimes you may have a report that you want to work with that’s in text format instead of Excel. Plus, it’s designed to be a report with many pages with column headings on each page. Trying to work with this in Excel can be challenging. I’m going to show you how I’ve worked with these in the past.
I’m going to use some data I pulled from a government site. This file is a text file listing of weather stations. Here’s the file if you would like to follow along.
To open this file in Excel you will need to use the Open, Browse command then navigate to the file’s folder. In the lower right of the Browse box, click on the dropdown for file type. The default on mine says “All Excel Files”. Change this to all files. You should now be able to see the text file, click on that to open the file.
Excel will bring you into the Text Import Wizard. In the first step make sure the original data type is Fixed Width and leave the other options the same. Then click “Next >”. In step two we’ll be setting the places where Excel will start a new column. It’s best to use the scroll bar on the right to get down to the actual data you’re going to be working with. In this case move down until you can see “Alaska” at the far left and then data below it. At this point you would look at the data and put in column breaks to separate the various data fields. I put my breaks in at the following positions: 3, 20, 26, 32, 39, 47, 55, 62, 65, 68, 71, 74, 77, 79, and 81. Then click “Next >”. (If you do this for another spreadsheet, make sure to write down these numbers, because there are cases where you need to recreate what you did.) For this spreadsheet we don’t need to do anything for step 3, so you can click on “Finish”.
In the spreadsheet that opens scroll down to row 41. You will see Alaska spread over two columns followed by the data with column headings. To make this easier to work with let’s get rid of the rows above this that we don’t need. Delete rows 1 through 40. Now we have the data we want to work with. First of all, we’re going to put in the missing column headings in column O and P. In cell O2 type in “Priority” and in cell P2 type in “CNTRY CODE”.
Most people don’t know the two-letter code for all fifty states, much less the Canadian provinces and other countries that are use later. Let’s add a column where we can pull in the heading with that data into each row. First insert a column at the beginning of the spreadsheet. In cell A2 type in “Location”.
Now in cell A3 we need a formula that will return “Alaska” or whatever names are used later on. You can see in row one Alaska is spread between two columns. So, well concatenate (meaning put them together) them. In cell A3 put in the formula B1&C1. This will put the text from these two cells together. But you’ll notice that if you increase the width of column A, that there is also a 1 in that text. We don’t want that; it belongs with the date in the next couple of columns. Counting the number of spaces, we find that come before the number gives us 16. We want a formula that will concatenate cell B2 and first 16 characters of C2. The function LEFT can be used to give us the formula to do that. We’re also going to want to get rid of the extra spaces after the end of the name but before the number. The function TRIM will do this. Change the formula in cell A2 to TRIM(B1&LEFT(C1,16)). *[I’m not going to explain how the formula works as you can get that information from your Excel help.] That gives a result of “ALASKA”.
Now to extend this to cover the whole spreadsheet. We can’t just copy the formula down all the way to the end. That would only work for the first row of each location. We could use a formula to pick up the result from the cell above each cell (For example cell A4 would have a formula =A3). But that wouldn’t work when the location changes. What we really need is a formula that will either pick up the result from the cell above or if the location changes, use the formula we put into cell A3. If you’re thinking an IF function might work, you are correct. But first we need to figure out how to tell when the location changes.
Scroll down to the spot where Alaska changes to Alabama, row 211. This way we can see what happens when the location changes and figure out how to use that in our formula. I’m looking at the columns to the left of the location and date and seeing that there empty. Let’s use that. Return to the top. In cell A4 we’ll use the following formula: =IF(H2=””,TRIM(B2&LEFT(C2,16)),A3). This formula should accomplish what we wanted to do.
Unfortunately, upon examining the results, there are some stations where the location is blank. Investigating shows that the locations in the US, Canada and Mexico all have column headings while the others that are all further down in the spreadsheet do not. Looks like we need to adjust our formula. For locations that do not have a column heading we want to adjust the formulas to refer to a different row. Setting up a data filter on the spreadsheet, we can filter on one of the column heading titles and see that the last column heading is in row 4653. We can add another IF function to do this. So the new formula for cell A4 would be: IF(ROW()>4653,IF(H3="",TRIM(B3&LEFT(C3,16)),A3),IF(H2="",TRIM(B2&LEFT(C2,16)),A3)).
Copy this formula to the end of the data. You can page through the spreadsheet to verify the formula has worked. Use save as and save the spreadsheet as an excel file. Just in case you won’t have to reenter the formulas if there is a problem later. Also, if the file you worked with changes, such as a report that covers a different month, you can just copy the formulas rather than recreating everything.
Now we need to eliminate the rows that do not contain data. This is easy to do. Because we’re going to eliminate rows this will mess up the results of our formulas in column A. We can change those formulas to data, so we don’t have to worry about that problem. Highlight all the data in column A and copy it. Now while that data is still highlighted do a paste, special, values to change everything to data instead of formulas.
Next delete row 1. Now the column headings are the first row. The next step is going to involve sorting and to make sure we can get the data back in its original order easily we can add a column with the current order. In cell R1 type in “Row”. In cell R2 put in the formula =ROW(). Copy that formula to the end of the spreadsheet. Then copy and paste special values to change the formula to numbers.
Next click on Sort. In the popup box make sure the box next to “My data has headers” is checked. Then set the sort by to CNTRY CODE and sort A to Z. Moving to the end of the country codes you will see all the rows after that are the ones we want to eliminate. You can delete all these rows to the end of the spreadsheet. Then if the spreadsheet is resorted A to Z for column R we have all the data in its original order.
I wanted to show you this so you could get the general concepts. Sometimes data isn’t in an easy to use format. But if you can convert it to Excel you have something that is easier to work with.
Trees and Forest Challenge Answers
All requests that mention Trump: Find these by clicking on the drop down for Request Description and typing trump into the search box above the list at the bottom. The resulting filter should show 4 items. Tracking numbers FY19-25,31,37 and 45.
The quickest responses: Find these by first clicking on the drop down for Final Reply Date and in the list at the bottom uncheck (Blanks). This will remove all of the large negative number items from Days. Then click on the drop down for Days. In the list at the bottom uncheck Select All then click on zero to select that one. The resulting filter should show 4 items. Tracking numbers FY19-03, 57, 58, 59 and 82. My guess is that these took zero days because there was nothing to report for these items.
The final reply date entry error: This one was a little trickier to find. When you looked for the quickest responses you might have noticed that after eliminating items that didn’t have a response date there was still one negative item in Days. Selecting that pulls up Tracking Number FY19-60. The final reply date is 3/12/18 which is nearly a year prior to the date received. This probably had the year entered wrong and should have been 3/12/19.
[Note: Apologies if you visited on Wednesday looking for a post. I usually write in the evenings and we had a power outage last night. My posts will be up by Thursday mornings.]
What’s that old saying about not being able to see the forest and the trees, or is it the trees and the forest? OK, Google tells me it’s “see the forest for the trees” with the first known use being in 1546.
In this post I’d like to show you how to take a forest of Excel data and pull out the trees that interest you. You might have a large spreadsheet with a lot of data that you’ve summarized and graphed to show the forest. But sometimes you’ll need to look at specific trees where there’s a part of the forest that’s not in line with expectations. That’s where data filters can help you.
First, we need a spreadsheet to work with. I like to work with real data; it’s a lot easier than trying to develop sample data. So, I have used the Advanced Search function in Google to find a spreadsheet that we can use for practice. I searched for Excel files that were published on websites the had a domain of “.gov”. I used the dot gov filter to make sure I was getting something in the public domain. I found a spreadsheet of freedom of information act (FOIA) requests to the US Trade Representative. You could do the same. Or you could just use my copy here:
Let’s open the file and take a look at it. We’ll work with just the first tab that is named Intake. Let’s set up the data filters so that we’re able to pull out just certain records to view. This is the easy part. First click on any cell within the data that you can see. Then click the filter icon. If you put this on your quick access toolbar (as I discussed here: https://www.tkanecpa.com/blog/excels-quick-access-toolbar) all you have to do is click on the icon that looks like a funnel (pictured on the left).
Or if you haven’t set this up on your toolbar you can find it on the Data ribbon. Alternatively, because there’s usually multiple ways to do things in Excel, on your keyboard you can simultaneously press the CTRL, Shift and “L”. Excel automatically figures out the top row for your data and how and the data to include. Dropdown arrows appear on the headings in row 3. Now you have filters set up.
Let’s see who’s been requesting data. Click on the dropdown arrow in column D, Requester Organization Name. You’ll see a menu followed by a list appear. We’ll try looking at just one item. In the list at the bottom click on the check box next to Select All. This will remove the checks from all the boxes. Now click on the check box next to the first item and we should see only the AFL-CIO’s requests.
But that didn’t work. You will see the AFL-CIO data request, but there’s a purple row followed by all these other requests. What’s going on? Remember back when you clicked the filter icon and I said Excel would automatically figure out what data to include. It turns out that the method it uses to figure that is to assume that an empty row or column is the end of the data. So, if there’s an empty row in the data (even if it’s colored purple) it assumes the data ended with the row above that. In order to fix this, we’ll need to turn the filters off. Just do the same steps again that you used to turn to filters on and they will turn off.
There are a couple ways to correct this. The first would be to just delete the blank row. Which in this case is fine, because looking at the data I can see that there is only the one blank row. That’s what I’m going to do as we work with this data. So, go to row 15 and remove it. (Right click on any purple cell and pick Delete from the menu that pops up. On the next menu pick Entire Row and then OK. The row is deleted.) You can now turn the filters back on.
The other method is better if you’re dealing with a lot of data and don’t want to find all the empty rows or columns and delete them. You will need to choose all the data from the top to bottom and side to side. With this spreadsheet you would start at with the column headings at cell A3. From there highlight the range A3 through G122. With that area highlighted turn the filters on.
The filters will now include all the data. Now filtering for just the AFL-CIO will show just their one request. You will also notice that there are a lot more Requesters to choose from when you look at the list.
Now that we have our filters, let’s work with this data a little bit. Let’s say we want to look at how long it takes to get a response from a FOIA request and be able to filter that.
We’ll start by setting up the formula to calculate how many days it takes. Let’s insert a column for that data. Move your cursor up to the top of the spreadsheet to the column headings and put it on the “G”. Right click your mouse and on the menu that pops up click insert. A new column appears, and it is already filtered. (We could have just put our formulas in column H, then we would have had to turn the filter off and on again to pick up the new column.)
Move to cell G3 and give the column a title. I’m calling it “Days”. Now move down one cell to G4. We’ll just do a simple formula to give us the difference between the “Final Reply Date” and the “Date Received”. In cell G4 enter the formula =H4-B4. The date 12/19/1905 appears. This is due to the way Excel treats dates, if we convert this to a number it will give us the days between the two dates. I just click on the comma icon on my quick access toolbar to do this and it becomes 2,180.00.
Now we can extend the formula to all the data. I do this by picking cell H4. It now has a border around it that includes a small filled in square in the lower right-hand corner. Holding the mouse over the border it will change to a black cross with arrowheads on each direction. Moving it over the small square the arrowheads will disappear. Double clicking at this point will extend the formula to the end of the data. We now have our forest of data, can we use filters to look at some trees?
Now if you left click on the G in the column heading, you’ll see some stats – average, sum and count of the data in column G.
But how can the average number of days be negative? Look at row 5, the AFL-CIO again. This request doesn’t have a final date so the amount in column G is a big negative number. We can correct that. Leaving column G highlighted, click on the dropdown for column H, then on the list at the bottom unclick the check mark in the box marked (Blanks). This will filter out the requests that have no final date. Now the average response time changes to 81.56 days.
Now, try clicking on the dropdown for column G. Instead of filtering on the list of numbers, let’s try something different. Just above the list you’ll see the menu choice “Number Filters”, click on that. You’ll see some choices that you can use to look for some data. Click on Top 10, then click “OK” on the box that appears. Only the ten requests that took the longest time to finalized will display and the average time for these is 460 days.
Let’s try something else. Start with nothing filtered. So, click on the dropdowns for columns G & H then click on the “Clear Filter” menu choice. You’ll notice that when the data is filtered that the row numbers on the left of the spreadsheet change to blue. When your filters are cleared, they will change back to black. Now click on the dropdown for “Request Description”. Just above the list of items there is a search box. You can type terms into that box to use as a filter. Try typing NAFTA into that box. There should be three items that pop up.
You can see from playing around with this that filters can be a powerful tool to use when looking at data.
Now a challenge for you. Using this spreadsheet find the following:
All requests that mention “Trump”.
The five requests that received the quickest responses.
The final reply date that was probably an entry error.
I’ll post the answers in my next entry.