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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
103
CTMom
C
A
  • Solved
Calculate monthly booked percentage of hotel rooms
2 3
Replies
21
Views
339
arendberg
A
gtd526
  • Question Question
List the date when the month is changed
Replies
2
Views
216
gtd526
gtd526
A
  • Solved
Sum hours by date range by employee ID
Replies
3
Views
298
alessiaexcels
A
K
  • Question Question
Trying to make a formula to figure out what total hours in the month will be, based on what I already know
Replies
1
Views
50
Sufiyan97
Sufiyan97

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

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