Using Excel More Effectively

I was asked a question last week about Excel and how we manage all the calculations we have going on.  Doesn’t it get confusing when you can’t recall what cell BalSht!AA192 represents?

Well, we hardly ever have that problem.  We use two distinct methods to help control our Excel programmatic flows:

  • Cell Naming
  • Data and Calculation sheets

excelexamplenaming1

In today’s example, I set up a very simple payment calculation worksheet.  Here you can see where I begin naming cells; in this case I have named B7 PerPayments, which I am using as short-hand for periodic payments.  But in the function wizard, you can see where I named the cell for the Rate = APR, the NPer as Years and Pv as PresentVal.  These correspond to B2, B3 and B4.

We set it up this way for two reasons – first and foremost, we try not to create new workbooks.  We have standard templates which we use and turn over to clients to use.  To make everyone’s life more hassle-free, we think that making it easier to follow the logic makes sense.  The second is that, in some of our work, the process is incredibly involved and trying to go back to a particular sheet and cell can be time-consuming and often leads to grabbing the wrong input.  Naming eliminates both the fog of not understanding and typing in the wrong cell reference.

namemanager1

Above is a partial list of the names we have running in our projections workbook.  There are almost 200 named cells; almost all of which come from the data page we named assumptions.   Each of these names actually helps us with some basic calculation on one of the other calc sheets.  Organizing our work this way makes it easy for us to navigate through the workbook and even copy sheets with the formulae intact.

Below is what our assumptions page looks like.  Currently we have about 85 lines in 9 sections covering about 400 variables which provide the static data we want to evaluate.

assumptionspg1

ClickDYr4 represents cell E29.  We reference ClickDYr4 in about 12 calculations on both the sales and marketing calculations sheets.  When we need to update the variable, we simply return to the assumptions page, go to the correct cell and enter it.  If we are not sure, we can go to the name manager and look it up.

It also makes formula creation much simpler. namingexcel2

Naming allows you to simply start typing, instead of using your mouse to jump to the page where you have stored your variable, click on the cell and then hit enter.  It can speed up your work and help you make sure you have important calculations documented.

So, how do you name a cell?  This is super easy.  In the box above Column A it shows the current cell reference.  In this case it is referencing B94, which we have defined in column A as Utilities and triple net costs.

namingexcel3

Click in the box and then type the name you want.  We are naming it NNNCosts for our workbook.

namingexcel4.png

Hit enter.  Now it is named.  You can then reference it anywhere in the workbook.  In our case, we have a facilities calculation worksheet which manages the outflows for facilities in our projection.

namingexcel5

There it is, ready to be used in your calculations.  It is much easier to follow than what was originally there; =D4*Assumptions!$B$94.

By following these simply and easy to follow rules, you will make your workbooks more reusable and user-friendly.  This will make your work in expanding your workbooks easier and also make your work that much more effective.  Your team and clients – if you hand your workbooks off to others – will thank you.

 

 

 

A little Excel Excitement

The other day at a client meeting we were discussing Pareto and the concept that 80% of the revenues were generated by 20% of the customers and It was likely that 80% of profit was generated from them as well.  Management was having a difficult time picturing this, but fortunately we had prepared for this meeting by using the Excel Data Analysis add-in.

The first graph we presented was the histogram of revenues generated by their clients.

histogram1

The most telling thing for management was that 40 of their customers generated less than $1,000 of revenue.  And notice how many generated under $15K in revenue.  Suddenly, they could see new patterns by changing the visualization.  So, if you haven’t tried to graph this yet using data analysis, here are steps to consider.

First, create a table with the information you want to chart.  In this case it was the customer and revenues for 2018.  Next, in your Excel, click on Data Analysis and the following modal box pops up:

histogram2

Select histogram.  It then advances to the next pop-up which asks for the data range and the Bin Range.  A little bit about the bin range:  It is the breakpoints you want to use to group your information.  In this case, I ran a filter to determine the largest amount and set the bin increment just above it.  In this case, we want to see the Pareto effect, so click on the Pareto (sorted histogram) box and also the Cumulative Percentage box.  And then chart the output.

histogram5.png

Click OK.

histogram8

After Excel generates the histogram table file and graph, you may need to make some modifications to get the graph to look the way you want.  In this case, I wanted to sort by revenue impact of the various bins.  To do that, I multiplied the frequency by the bin amount and then sorted the table file by the total bin revenue generated.  Finally, I added a calculated percentage for each bin and the cumulative percentage.  I then set the Cumulative Percentage to use the new percentage I calculated. In this case, The impact of sales between $15,000 and $25,000 account for almost 80% of revenues, even though their frequency is less than 15%.

Consider the impact that this type of presentation can have on a strategic conversation.  What does your staffing level look like if we focused our energy on that small group of customers?  What message resonates best with that group?  Should we discontinue sales to small buyers?  We had a great conversation from less than an two hours worth of work – mostly spent in defining the table structure, gathering the data and importing it.

Raw data and numbers are useful in the right context, but if you want to stimulate an effective business meeting, show some graphs which group data in ways that make your team think about what they are doing and why.

 

 

Planning over budgeting

Somedays it feels like all we do is parse semantics, doesn’t it?

Budgeting, planning, it is all the same thing, right?  Well, no – not really.  Yes, far too often they are used interchangeably, but the truth is that planning is far more important than budgeting.  Budgeting is really about allocating your resources.  Planning identifies what you want to accomplish and how your resources can help you make that happen.

This is why the distinction is important.  I am starting to help clients plan for the next year and part of that is looking at what we have accomplished through today.  It is part looking at the numbers and part looking at the strategy and seeing if we are off target.

To me, there is nothing worse than not being able to answer the question, “How did we get here?”  Or worse, hearing the glib answer, “We performed well (poorly).”  Duh.

What was the plan?  Did we think we were going to break into a new market?  Did we succeed?  What is the acquisition cost per new customer?  Are we getting repeat business?  Can we grow in that are?  At what cost?

There is nothing harder to do than listen to someone say how profits are holding steady, while knowing that it is because money is not being spent on carrying out the plan.  Of course you have profit, we are not spending money on project X, which, by the way, two years ago we all agreed that it was going to be the flagship product within 5 years.  Why did the company plan to allocate thousands of dollars on advertising if we don’t want to spend it?

These are hard questions to listen to, much less respond.  No one likes having their parade rained upon, and yet, well, there it is. The plan drives how resources are acquired and spent, otherwise you just have a budget.

That isn’t to say budgeting isn’t a useful tool, it is.  But my experience has been, budgets are typically devoid of planning.  We spend money in that area last year, lets continue to spend money in that area.  We generated revenues selling widgets last year, lets budget to generate 5% more in sales.  There is no plan, no vision, nothing to be held accountable to until it is too late.

I do lots of budgeting for non-profits and the most fun I have is helping them rethink the budget process into the planning process.  We start by looking at the big picture and then start asking how we get there.  Then, once we get consensus, we start putting numbers to actions.  It is extremely rewarding to see these professionals see the results and understand that there is a reason for spending money, other than the satisfaction of having money to spend.

So take time, do planning.  Think about where you want to be and how you want to get there and THEN allocate resources.  You will get tremendous benefits from it, mostly from thinking about how you are going to get from A to B.  If you need help, let me know, I am always open to helping companies and non-profits think about their plans and how they translate into action and money.

 

Understanding cost drivers

A few years back, I was brought in by the president of a small business which manufactured and installed custom storage systems for offices and restaurants.  His concern was that either his accounting system had a bug in it or that someone was stealing from him.

This came to light when he ran his most recent quarter profit and loss; it seems that for the first time in over 7 years, he lost money.  His shop has never been busier.  He is getting lots of highly profitable contracts so the quarterly results don’t make any sense.  I asked to see quarterly information and got to work.

The first report I reviewed was the profit and loss report.  Sure enough, there was a $53K unabsorbed overhead amount  This happens in one of two ways – quarterly overhead went through the roof or the driver, in this case direct labor hours, were substantially lower.  Sure enough, the direct labor hours were down about 10%, indicating that labor wasn’t recorded.  Which would be really strange as the controller had been doing this job for almost 5 years and consistently made the overhead allocation adjustment correctly.

overhead1

So I asked the president if there were any major changes.  His response was that he decided to outsource all of his installations.  As a test, he explained,  the company in the prior quarter elected to put the installation out to bid on one project and the price came in at about 60% of what his costs were.  So he decided that he was going to “sell” the installation department to his department supervisor who would then quote jobs as an independent contractor.

I explained to him that what he was seeing was a direct result of the decision to outsource without knowing all the available facts.

Fact: There were two different departments, each with its own overhead costs and driver

Fact: the Company was using a single driver addressing the total overhead

Conclusion: The same (essentially) overhead dollars were being allocated over fewer direct labor hours, leading to larger unabsorbed overhead since the rate was not adjusted to reflect that fewer hours were being “sold”.

I then showed him a spreadsheet of what this actually looked like:

overhead2.png

The company rate of $41 was correct, but only for its overall purpose, allocating company overhead to production costs.  In reality, very little of the overhead went to support the installation department.  As a matter of fact, when separated, it became obvious to him that the shop was being subsidized by the installation department.

When they bid a job and included the rate of $41 to the install hours, the company was in fact generating an additional $27 in revenue which went to the bottom line.  But this was hidden from the controller, the president and the estimating department.  Thus, the $100 per hour revenue rate appeared high when compared to the $75 rate that the subcontractor offered.

In truth, had the company been facing lost estimates, they could have reduced the hourly rate for installations from $100 to about $65 and still earned a decent profit.  But you have to look deeper into your company structure in order to understand that options like that are available to you.

When most of your costs are fixed, then basing make or buy decisions on your overhead absorption rate can be dangerous.  The key is understanding that allocating costs by way of hours turns that fixed cost into an illusory variable cost.  You begin to think that by eliminating the driver, the cost goes away as well.  It doesn’t work.

Once the president understood this, he was able to convince the installation team to rejoin the company, although he did have to make some concessions as to bonuses when it came to profit earned on installation jobs.  And with this information, the company went through the various areas of the business and examined how costs were incurred and allocated to projects to even more effectively estimate contracts and keep their bids competitive while improving their profitability.