Actuals vs Budgets Scorecard

[Anonymous]

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.

1. Email
2. Broadcast
3. DirectMail

There are 5 measures for which I already have the Actual and Plan values per channel per month for this year (2019):

1. Applications
2. Approval Rate
3. ROA
4. CostPerApplication
5. 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.

[v-yuta-msft]

@Anonymous ,

Could you share the sample data and give the expected result? 

Regards,

Jimmy Tao

[Anonymous]

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	Email	3517	33%	$             300,010	$           4,200	$  42	$  450	$  62,000
1/31/2019	Email	3767	30%	$             303,000	$           4,500	$  45	$  450	$  65,000
1/31/2019	Email	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.

[Anonymous · Accepted solution]

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!