skip to Main Content

Power Pivot, Power BI, and Microsoft Excel techniques for Accounting and Finance Professionals.

Budget Calculations That Change by Level

 
image

[Pct Sold Smart] Measure Matches [Shape Pct Sold] at the Shape Level (Orange Cells) But Matches [Raw Pct Sold] Down at the Color Level

Quick Post Today

I’m halfway on vacation today in Florida, visiting family, so I will keep this brief.  I saw this question today on the forums.

Sales and Stock Tables

The question is pretty simple – I have a Sales table and a Stock table:

image

Sales Table – the Numerator of our Pct Sold Measure

image

Stock Table – the Denominator of our Pct Sold Measure

And we want a measure that returns Percentage Sold. 

But there’s a twist:  at the Shape level, we want to use something other than Stock as the total.  We want to use another cap, a Shape-Level cap:

image

At the Shape Level, We Want to Use These Values (Instead of Stock) as the Denominator

First Step:  Lookup Tables

Read the Rest

Same Store Sales Continued – Using Store Open/Close Dates

 
In a comment on Tuesday’s post, Cory asked the following question:

“I want to use the stores start date to determine if it should be included in the same store calculation or not. Y/Y SSS calculation regardless if there are sales for that store this year or last for the specific date range I’m looking at (typically by month for last 13 months). The other twist to my SSS calculation is the store must be open for at least 15 months before it is included in my SSS Total. I hope my question makes sense. Thanks for any guidance you or anyone can offer.”

Remember that in my first post, if a store had sales last year, and this year, I included it in the SSS (Same Store Sales) calc.

If we have columns in our data indicating the date a store opened (and when it closed), it’s a very rational thing to want to use that instead.

So I invented some new columns on my fictional Stores table:

I'm Going to Use These (Fictional) Store Open and Close Dates to Drive my PowerPivot Same Store Sales Calc

I’m Going to Use These (Fictional) Store Open and Close Dates
to Drive my PowerPivot Same Store Sales Calc

So, how do we do that?

First, I create a measure to calculate when a store opened.  I add this to the Stores table itself:

Read the Rest

Calculating “Same Store” Sales in PowerPivot

 

Calculating Same Store Sales in PowerPivot

Another one from the forums

I love the PowerPivot forum over at Mr. Excel for many reasons, one of which is that it gives me useful, real-world inspiration for blog posts.  Recently, I saw someone asking about how to calculate “same store sales.”

Simply put, same store sales means comparing sales of ONLY stores that are open today and were also open at the same time last year (or last month, last quarter, etc.).

In other words, it’s a “year over year” (or month over month, etc.) comparison measure that only looks at stores that were open last year, and are still open today.

Read the Rest

Creating a Cycle Plot PivotChart

Guest Post by Colin Banfield [LinkedIn]

A cycle chart is a chart designed to show business sales cycles in a manner that cannot be expressed using other standard charts. For instance, a cycle charts can show how monthly sales vary over several years, or how daily sales vary over several weeks. For a good introduction to cycle charts, see this excellent article by Naomi Robbins.

Figure 1 shows cycle PivotCharts using 3M+ rows of sales data from the Contoso database.

 

image

Figure 1 – Monthly sales over years and daily sales over weeks cycle plots (click figure to see an expanded image)

Read the Rest

Monday Bonus: SalesForce Data Imported into PowerPivot Without Programming?

 
SalesForce.com data loaded into PowerPivot (No Special Skills Required)

SalesForce.com data loaded into PowerPivot (No Special Skills Required!)

This Whole Cloud Thing Just Might Catch On…

We live in pretty exciting times.  Sometimes it’s simply amazing what I can do from my desk, without having to take off my Excel hat.  All of these various technologies available to us in the cloud, plus PowerPivot’s ability to talk to them…  the net impact really starts to add up sometimes.

All of the Tables Available, Just Select and Click Finish

I didn’t have to do anything “technical” to pull this off really, I just end up using the PowerPivot import wizard:

Simple import of Salesforce.com data into PowerPivot, just select and click

Simple import of Salesforce.com data into PowerPivot, just select and click

What’s the Trick?

Read the Rest

Tiered Commissions, Income Taxes, and other “Tiered Rate” Calculations in PowerPivot

Guest post by David Churchward [Twitter]

Commissions Part 3 Report

I mentioned in my LAST POST that I would return with a variation on that commission calculation.  Two months later and very much overdue, here it is!  Apologies to those of you who have been waiting on me for this.

I left off with a calculation that applied a commission rate based on a rates table.  That rate was applied to the total value.

But what about a scheme where one rate applies to the first N dollars (or pounds, euros, etc.), then another rate applies for the next M dollars, and so on?  Income taxes are calculated this way, for instance.  And being able to solve this sort of thing in Excel very efficiently (using PowerPivot) will be generally quite useful.

In this post, I’ll explain how to do this in a model that I refer to as Base Plus.  And while I will discuss this purely in the context of tiered commissions, keep in mind that it applies to taxes and many other things as well.

Read the Rest

Commission Calculations in PowerPivot Part 2

Guest post by David Churchward [Twitter]

Team and Manager Commissions Report

You may recall in my last post, COMMISSION CALCULATIONS IN POWERPIVOT, we got to the point where we could dynamically calculate the sales value and attributable commission rate that should be applied based on time, value and team parameters, reading from a Rates table.

In this post, we’ll complete the commission calculation, providing a different value for individuals and the team manager.

Where are we?

Just to recap, we got to the point in my last post where we had calculated [Sales_Value] and [Comm_Rate] as below:

Read the Rest

Commission Calculations in PowerPivot

Guest post by David Churchward [Twitter]

Commissions Report

Firstly, I have to be clear that I’m not presenting a “one-size-fits-all” approach to sales commission calculations here.  That wouldn’t be possible because commission schemes vary extensively and, in my experience, some schemes aren’t even based on a logic that can derive a mathematical answer!  However, I’m presenting an approach here that will hopefully provide a template approach that can be modified to a number of different scenarios.

The Target Outcome

The scheme that I’m using in this example operates as follows:

  1. Commission is paid monthly based on the achievement in that month
  2. As a salesperson sells more, then accelerators trigger.  That is to say, for example, a salesperson may receive 1% of sales up to £10,000 and 2% of sales between £10,000 and £20,000.  The 2% is payable on the whole value.  Therefore, if a salesperson sells £11,000 then they would receive 2% of the full £11,000
  3. Percentages and bands can change monthly at management discretion
  4. This is a monthly threshold so each salesperson is reset to zero at the start of each month.
  5. Each product group carries a separate set of bands and rates
  6. Managers receive commission at a different rate to the sales team based on the total sales for their team.
Read the Rest