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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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

C
  • Question Question
Subscriber numbers with different payment plans
Replies
2
Views
77
CTMom
C
gtd526
  • Question Question
List the date when the month is changed
Replies
2
Views
212
gtd526
gtd526
A
  • Solved
Sum hours by date range by employee ID
Replies
3
Views
266
alessiaexcels
A
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
123
Fluff
Fluff
M
  • Question Question
Macro to change data from vertically to horizontally
Replies
4
Views
154
Mussa
M

Forum statistics

Threads
1,223,885
Messages
6,175,179
Members
452,615
Latest member
bogeys2birdies

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