- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Actuals vs Budgets Scorecard
Hello everone,
I am working on creating a Marketing Scorecard based on Actual vs Plan values by channels.
There are 3 channels that we need to calculate the Scorecard for.
- Broadcast
- DirectMail
There are 5 measures for which I already have the Actual and Plan values per channel per month for this year (2019):
- Applications
- Approval Rate
- ROA
- CostPerApplication
- Funded Dollars
Each measure is assigned a weightage :
Measures | Weighting |
Applications | 10% |
Approval Rate | 5% |
Funded Dollars | 30% |
CostPerApplication | 10% |
CostPerLoan | 20% |
ROA | 25% |
I need to create a Scorecard that loks like this:
The % Performance on the second last column above is (Actuals - Plan)/(Actuals). The Scoer on the rightmost column is based on a Score assigned to the %Performance and the below chart.
Score | %Performance |
5 | >10% of Plan |
4 | > 5% of Plan |
3 | Meets Plan |
2 | 5% below Plan |
1 | >10% below Plan |
25% |
Finally, the %Weighted Score is the sumproduct of each of the Scores and the weights assigned to the measures above.
Could you please guide me for the same. Any help is really appreciated.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I was able to resolve this myself.
Created a custom matrix with the row names of 'Actual', 'Plan' and %Change' and Column names of the measures.
Then I fed the data into it using a combination of Row and Column value for each cell and it worked out.
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Anonymous ,
Could you share the sample data and give the expected result?
Regards,
Jimmy Tao
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Team,
I have already shared the expected result in the image format in my request.
In terms of sample data,
I have two tables that look something like this:
ActualsData ( Date column represents the end of month date for each month of year 2019)
Date | Channel | Applications | ApprovalRate | FundedAmount | AvgLoanSize | CPA | ROA | Spend |
1/31/2019 | Broadcast | 2000 | 33% | $ 300,010 | $ 4,500 | $ 45 | $ 450 | $ 65,000 |
1/31/2019 | Broadcast | 2300 | 30% | $ 303,000 | $ 4,200 | $ 42 | $ 450 | $ 62,000 |
1/31/2019 | Broadcast | 2500 | 40% | $ 400,010 | $ 4,300 | $ 43 | $ 420 | $ 61,000 |
1/31/2019 | DirectMail | 2767 | 33% | $ 300,010 | $ 4,500 | $ 45 | $ 450 | $ 65,000 |
1/31/2019 | DirectMail | 3017 | 30% | $ 303,000 | $ 4,200 | $ 42 | $ 450 | $ 62,000 |
1/31/2019 | DirectMail | 3267 | 40% | $ 400,010 | $ 4,500 | $ 45 | $ 450 | $ 65,000 |
1/31/2019 | 3517 | 33% | $ 300,010 | $ 4,200 | $ 42 | $ 450 | $ 62,000 | |
1/31/2019 | 3767 | 30% | $ 303,000 | $ 4,500 | $ 45 | $ 450 | $ 65,000 | |
1/31/2019 | 4017 | 45% | $ 400,010 | $ 4,200 | $ 42 | $ 450 | $ 62,000 | |
1/31/2019 | Native | 4267 | 30% | $ 300,010 | $ 4,500 | $ 45 | $ 450 | $ 65,000 |
1/31/2019 | Native | 4517 | 45% | $ 303,000 | $ 4,500 | $ 45 | $ 450 | $ 65,000 |
1/31/2019 | Native | 4767 | 30% | $ 400,010 | $ 4,200 | $ 42 | $ 450 | $ 62,000 |
And the plan data that looks same as above but with the planned values for each of the measures.
Please do let me know if any other information will be helpful to proceed.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I was able to resolve this myself.
Created a custom matrix with the row names of 'Actual', 'Plan' and %Change' and Column names of the measures.
Then I fed the data into it using a combination of Row and Column value for each cell and it worked out.
Thanks!
Helpful resources
Subject | Author | Posted | |
---|---|---|---|
04-06-2019 03:13 AM | |||
07-18-2024 12:37 PM | |||
06-13-2024 12:26 AM | |||
03-12-2023 07:20 PM | |||
05-17-2022 08:16 AM |
User | Count |
---|---|
122 | |
77 | |
62 | |
50 | |
49 |
User | Count |
---|---|
175 | |
125 | |
61 | |
60 | |
58 |