Excel Weighted Scoring

[TimothyA]

Hi!
I'm doing a GCSE project for which I need a little help with Excel.

I have a set of data about a persons monthly spending habits on

Food 300
Entertainment 200
Clothing 100
Transport 260

The pre calculated budget allowance for the month was 500

I would like to write a formula that would do the following; give the person an score out of 1.1-10 based on how well he spent on the above items which are in order of priority (i.e. it is better to spend more on food than on entertainment etc) And then an over all score (1.1-10) that will reflect how well they spent.

Any suggestions, ideas would be greatly appreciated.

Many thanks!

Tim

 

[Claymationator]

As all of the items are listed in priority, do you have a standard percentage of the allowable amount (500) that should be spent on each item?

For example, is it appropriate to spend the following amounts so that the allowable amount is not exceeded:

Food = 50% = $250
Ent. = 20% = $100
Clothing = 20% = $100
Trans. 10% = $50

With the information that you have in your post the examples exceed the allowable amount that you have noted (300+200+100+260 = 860), which is also 72% above the allowable amount. Or, is the $500 allowable amount per item, meaning that they get $500 for food, $500 for clothing, etc?

 

[TimothyA]

Thanks for the quick response.

The £500 budget allowance is the total amount for the month. Any over spending should be penalised for and any underspending rewarded, by way of reflecting on their end score.

I tried to first put a score on the spendings e.g.

If spending is 10% over limit then they'll be penalised by e.g. 1 point
If over 30%, 3 points and if over 50%, 5 points

and if spending is 10% they get an additional 1 point etc.

But I can't seem to put this all into one formula (If spending is >=10%, return -1 if >= 20%, return -3 amd if over 50% return -5)

Then I would add a weigting to each of the spending categories (in the order of priorit) and calculate the total (has to be between 1.1 to 10).

You may have a better way to tackle it anyway.

 

[Claymationator]

OK, so I might not be on the right track here, but I hope that I am. I have created the following template to go over your data:

Book2
	A	B	C	D
1	Item	Spending	Score	Weighted
2	Food	$300.00	-5	-3
3	Entertainment	$200.00	-2	-1
4	Clothing	$100.00	-2	0
5	Transport	$260.00	-5	-3
6
7	Totals	$860.00		Allowable
8
9	Allowable	$500.00
Checking

In C2 I put in this formula (and copied down) to return the score based on your information in the last post:

=LOOKUP($B2/$B$9,{0,0.1,0.2,0.5},{"1","-1","-2","-5"})

But I can't seem to put this all into one formula (If spending is >=10%, return -1 if >= 20%, return -3 amd if over 50% return -5)

B9 refers to the budget or what is allowable in your scenario ($500.00). Then the score is generated for each item of spending.

Then in D2 I put in the following formula to weight the item of spending against the other items:

=(B2/$B$9)*C2

That will return the weight of the score based on what percentage of the budget the spending took.

Then, in D7 I put in this formula to tell whether or not the spending fell within the absolute value of the weighted allowable totals (1.1-10). If the sum of the spending fell outside of that range the cell with return "Not Allowable."

=IF(AND(ABS(SUM(D2:D5))>=1.1,(ABS(SUM(D2:D5))<=10)),"Allowable","Not Allowable")

Again, I am not sure if that is what you are ultimately looking for, if not please correct me and I will give it another shot.

 

[Claymationator]

One thing that I forgot to mention is that in D7 the total is -6.8 (changed to 6.8
to follow your 1.1-10 range). I changed the formula to show the actual total if it falls within your range, if it falls outside of 1.1-10 then it will return "Not Allowable."

=IF(AND(ABS(SUM(D2:D5))>=1.1,ABS(SUM(D2:D5))<=10),ABS(SUM(D2:D5)),"Not Allowable")

Hope that helps.

 

[TimothyA]

Hi and thanks for your help!

You forgot to score for under budget spending!

The idea was for me to score the spender (0.1 to 10, where 0.1 is the best shopper and 10 is the worst). The granularity of the score is 1 decimal point (so 0.1 to 10 we get 100 scores).

For each item
Over budget assigns a negative score. the more over budget the more worse their score.

Under budget assigns a positive score, the less they are under budget the better the score is.


Keeping in mind that the spending elements are prioritised (food, ent, clothing, trans), recognising that a person needs to spend more on food > ent > clothing > tans) in any given month. So spending 10% over budget on Food shouldn't be as bad as spending 10% over budget on clothing etc.

The total score, which should be a combined score of the each elements (clothing, shopping etc) is like a spending indicator (10 is a spendthrift and 1 is somewhat miserly!!).

If I get to a solution before you I will post it here.

Thanks!

 

[Claymationator]

OK, I have a few more questions for you then. You state that the budget for each individual is $500, but then you also state that "spending 10% over budget on Food shouldn't be as bad as spending 10% over budget on clothing etc." Is there an overall budget as well as individual item budgets? If so, what are the amounts of each individual budget? If you simply stated that incorrectly, then what is the breakdown of scores for being under budget?

For example, if someone spends 400, 300, 500 etc., what is their score on the scale of 1.1 to 10? If you can provide that breakdown it should be pretty easy to add that part in and get the results that you are looking for.

 

[TimothyA]

Sorry I myself misunderstood the problem!

There are individual budgets for each item e.g.
Food - 250,
Ent - 100,
Clothing - 100,
Transport - 50

500 is the overall budget.

Now let's consider a new set of data for person A
Food - 200
Ent - 150
Clothing - 160
Transport - 20

Perhaps we should calculate the scores of being over budget in one table and being under budget in another, as their overall input to the final score will vary e.g. If someone is under budget on food (by 20%) with a total of 200 their score for food could be 2 but if they were overbudget on food by 20% their penaly score would be bigger e.g 4.

Now how we map the actual spending £xxx to a score between 0.1 and 10 is something i'm not told...I need to work that out. I was just suggesting that we break down the spending by saying

spending 10% > budget = a score of -1
spending 30% > budget = a score of -3
spending 50% > budget = a score of -5

spending 10% < budget = a score of 2.
spending 30% < budget = a score of 6
spending 50% < budget = a score of 10

and so on...

This doesn't have to strictly be done like this...I'm just trying to find a way to map £ amounts to a consisten score. We could further break down the score and assume e.g. every 2 pound spent over budget incurrs a
-0.1 score and ever 2 pound spent under budget incurrs a 0.2 score

To Final score (a value between 0.1 to 10) given to each person should be the sumproduct of their individual weighted scores in food, ent, clothing and transport. The weighting for food can be 4, ent 3, clothing 2 and transport 1.

I hope I'm making sense this time!
Thanks!

 

[Claymationator]

OK, so I think that this will give you the results that you are looking for. I have not tried it out too much, but from what I can tell it should work. Give it a try and let me know if it does what you need. It should be dynamic so you can just change the actual spending column and it should adjust. Use the drop down to see the different formulas.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Item	Budget	ActualSpending	%ofBudget	Over/Under	Ranking		OverBudget				UnderBudget
2	Food	$250.00	$200.00	80%	20%	2		-100%	-50%	-30%	-10%	10%	30%	50%	100%
3	Entertainment	$100.00	$150.00	150%	-50%	-5		-5	-5	-3	-1	2	6	10	10
4	Clothing	$100.00	$160.00	160%	-60%	-5
5	Transport	$50.00	$20.00	40%	60%	10
6	Total	$500.00	$530.00	106%	-6%	-1
Sheet1

Hope that helps.

 

[northwolves]

Perhaps making a list of scores as Claymationator has showed above is a better choice,with which you can change all the factors as you want.

And try to work harder than others to earn more money.

Regards
Northwolves