I have excel and a sense that this is possible
August 7, 2016 10:39 PM Subscribe
So imagine I have a population of like 100,000 people. And I have goals for them to achieve for which they get money. Now I want to calculate what my budget might be.
To keep it simple lets say I have 5 goals. I imagine 80% of the people will achieve 1 goal, and some lesser percentage will achieve 2, and a virtually none will achieve all 5.
Each goal might be worth $50
How can I set up an excel sheet to simulate this world where I can play with the variables? My biggest confusion is where 1 person achieves more than one goal.
To keep it simple lets say I have 5 goals. I imagine 80% of the people will achieve 1 goal, and some lesser percentage will achieve 2, and a virtually none will achieve all 5.
Each goal might be worth $50
How can I set up an excel sheet to simulate this world where I can play with the variables? My biggest confusion is where 1 person achieves more than one goal.
Goal 1 2 3 4 5 % 80.00% 26.67% 8.89% 2.96% 0.99% First Goal % 80.00% Goals 80000 26667 8889 2963 988 Decay Rate 0.33 Cost $4,000,000 $1,333,350 $444,450 $148,150 $49,400 Population 100000 Budget $5,975,350.00Unless you have some prohibition against contestants winning more than one prize then it shouldn't matter to the bursar. A dollar is a dollar regardless of who it's paid to.
Pn preview - yeah, what salvia said
posted by mce at 11:05 PM on August 7, 2016
The first two answers will give you what you want if you know what the probabilities are of reaching a specific number of goals. I was unclear on whether you actually know those or not -- if you want, you can also derive them from the probability of reaching each *individual* goal. If the probability of satisfying any given goal is 25% and everyone gets 5 chances, then ~76% of people will achieve at least 1 goal, 36% at least 2, ..., and 0.09% will achieve all 5. That comes from the binomial distribution, which you can play around with here; it's one of the best-worked out statistical distributions so there are easy formulas you can use.
If the probabilities of each goal are different, you can't use the binomial distribution anymore to set those probabilities, but you can still totally calculate the probabilities for a fixed number of goals, though it might be more tedious. The case for two goals is maybe easier to see. Let's call the probability of succeeding at a goal P(G1=Y) and P(G2=Y), and the probability of failing goal P(G1=N) or P(G2=N). Note P(G1=Y) + P(G1 = N) = 1 because one of those options has to be true.
You can then calculate the probability of each possible outcome: P(G1=Y, G2=Y), P(G1=N, G2=Y), P(G1=Y, G2=N), and P(G1=N,G2=N). (Make sure all of these add up to 1, or something is wonky.) The probability of at least one goal is then going to be sum of probabilities for which one goal was satisfied: i.e., P(G1=Y,G2=N) + P(G1=N,G2=Y) + P(G1=Y,G2=Y). And so forth. (The probability of zero, one, and two goals should also add up to 1, as a sanity check, since it's impossible to have fewer than zero or more than two successes in this scenario.)
You should be able to set this up such that P(G1=Y) and P(G2=Y) (or out to however many goals you want) are just values in a cell that you can set arbitrarily, so you can see how they affect the outcome.
The money stuff is pretty straightforward: your expected payout is equal to the prize money, times the probability of getting that prize, times the population size.
(Note that this is a model with particular assumptions. In particular it assumes that the probability of succeeding at any given goal is independent of the probability of succeeding at any other goal. If succeeding at one goal means you're actually more likely than average to succeed at others, this is not a super safe assumption, but it also gets much more complicated to model. You could always set those probabilities arbitrarily, or empirically if you have real-world data on that scenario.)
posted by en forme de poire at 11:11 PM on August 7, 2016
If the probabilities of each goal are different, you can't use the binomial distribution anymore to set those probabilities, but you can still totally calculate the probabilities for a fixed number of goals, though it might be more tedious. The case for two goals is maybe easier to see. Let's call the probability of succeeding at a goal P(G1=Y) and P(G2=Y), and the probability of failing goal P(G1=N) or P(G2=N). Note P(G1=Y) + P(G1 = N) = 1 because one of those options has to be true.
You can then calculate the probability of each possible outcome: P(G1=Y, G2=Y), P(G1=N, G2=Y), P(G1=Y, G2=N), and P(G1=N,G2=N). (Make sure all of these add up to 1, or something is wonky.) The probability of at least one goal is then going to be sum of probabilities for which one goal was satisfied: i.e., P(G1=Y,G2=N) + P(G1=N,G2=Y) + P(G1=Y,G2=Y). And so forth. (The probability of zero, one, and two goals should also add up to 1, as a sanity check, since it's impossible to have fewer than zero or more than two successes in this scenario.)
You should be able to set this up such that P(G1=Y) and P(G2=Y) (or out to however many goals you want) are just values in a cell that you can set arbitrarily, so you can see how they affect the outcome.
The money stuff is pretty straightforward: your expected payout is equal to the prize money, times the probability of getting that prize, times the population size.
(Note that this is a model with particular assumptions. In particular it assumes that the probability of succeeding at any given goal is independent of the probability of succeeding at any other goal. If succeeding at one goal means you're actually more likely than average to succeed at others, this is not a super safe assumption, but it also gets much more complicated to model. You could always set those probabilities arbitrarily, or empirically if you have real-world data on that scenario.)
posted by en forme de poire at 11:11 PM on August 7, 2016
If this were an actual budget that I was paying out, then I would want to make sure that, however clever my modelling of individual goal attainment probabilities, I was covered for the case of cheats. If your goals are such that somebody could share instructions on how to attain them on social media - things could get expensive.
posted by rongorongo at 11:54 PM on August 7, 2016
posted by rongorongo at 11:54 PM on August 7, 2016
This thread is closed to new comments.
- Total population
- Percentage achieving goal 1
- Percentage achieving goal 2
... 3
... 4
... 5
- Award per goal
Row 1 are the column headings
Row 2 are those assumptions
On Row 3, you could calculate the number of people achieving each goal.
On Row 4, you could calculate the dollars paid by goal, and in total.
There might be more beautiful ways to lay this out, but that's a quick and easy way.
posted by salvia at 11:04 PM on August 7, 2016 [1 favorite]