Calculating The Impact of Volume and Conversion Rate Changes

H

Hebblerock

New Member
Joined
Oct 15, 2019
Messages
1
Hello everyone,
I'm trying to calculate the impact three ever changing variables are having on our month on month performance. We have 10 buckets that are filled with applications and the scenario is:


  1. The total number of applications changes month on month
  2. The % of total applications that sit in a bucket may change month on month
  3. The conversion rate of any buckets can also change.

So if for example volume shifts from one bucket to another where the conversion is lower that results in a worse performance, but this could be offset by an increase in conversion on another bucket.

How is it best to take into consideration all these moving pieces and pinpoint where performance went wrong (Or right). Example data below



[TABLE="width: 512"]
<colgroup><col width="64" style="width:48pt" span="8"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]No. Apps[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]65000[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]No. Apps[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]70000[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"]Month 1[/TD]
[TD="bgcolor: transparent"]P/O No.[/TD]
[TD="bgcolor: transparent, colspan: 3"]Month 2[/TD]
[TD="bgcolor: transparent"]P/O No.[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]2%[/TD]
[TD="bgcolor: transparent, align: right"]130[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]2%[/TD]
[TD="bgcolor: transparent, align: right"]140[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]12%[/TD]
[TD="bgcolor: transparent, align: right"]780[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]700[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]2%[/TD]
[TD="bgcolor: transparent, align: right"]130[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]2%[/TD]
[TD="bgcolor: transparent, align: right"]140[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]5%[/TD]
[TD="bgcolor: transparent, align: right"]325[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]5%[/TD]
[TD="bgcolor: transparent, align: right"]5%[/TD]
[TD="bgcolor: transparent, align: right"]175[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]7%[/TD]
[TD="bgcolor: transparent, align: right"]455[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]20%[/TD]
[TD="bgcolor: transparent, align: right"]5%[/TD]
[TD="bgcolor: transparent, align: right"]700[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]2%[/TD]
[TD="bgcolor: transparent, align: right"]130[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]5%[/TD]
[TD="bgcolor: transparent, align: right"]2%[/TD]
[TD="bgcolor: transparent, align: right"]70[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]3%[/TD]
[TD="bgcolor: transparent, align: right"]195[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]3%[/TD]
[TD="bgcolor: transparent, align: right"]210[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]5%[/TD]
[TD="bgcolor: transparent, align: right"]325[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]5%[/TD]
[TD="bgcolor: transparent, align: right"]350[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]650[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]9%[/TD]
[TD="bgcolor: transparent, align: right"]630[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]1%[/TD]
[TD="bgcolor: transparent, align: right"]65[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]10%[/TD]
[TD="bgcolor: transparent, align: right"]3%[/TD]
[TD="bgcolor: transparent, align: right"]210[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 256"]
<colgroup><col width="64" style="width:48pt" span="4"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Hi Hebblerock,
this boils down to what's important for the people who are looking at your "management summary" of that data. I know that "every number is important", but generally people are mainly interested in "what are the most important things for my KPI/financials". So something like this?

No Apps this week: 70000 +5000 (+7.7%)
P/O TOTAL 3325 +140 (+4.4%)
Biggest changes in P/O:
Category 05 +245
Category 10 +145
Category 04 -150
Others -100

You could provide your complete table with some icons for the changes with conditional formatting (see e.g. https://stephanieevergreen.com/dashboard-icons-in-excel/ ), but that's probably too much detail for most readers.
Cheers,
Koen
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Question Question
List the date when the month is changed
Replies
2
Views
211
gtd526
gtd526
A
  • Solved
Sum hours by date range by employee ID
Replies
3
Views
263
alessiaexcels
A
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
112
Fluff
Fluff
M
  • Question Question
Macro to change data from vertically to horizontally
Replies
4
Views
151
Mussa
M
S
  • Question Question
Pro Rata a Sumproduct Calculation
Replies
1
Views
312
myall_blues
myall_blues

Forum statistics

Threads
1,223,099
Messages
6,170,114
Members
452,302
Latest member
TaMere

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top