LinkBack URL
About LinkBacks
I have about 10 awards with different dollar amounts that span different lengths, from 3-15 months. I'm trying to project sales starting slow, ramping up, progressing down. Is there anything out there that would do it for me?
I have about 10 awards with different dollar amounts that span different lengths, from 3-15 months. I'm trying to project sales starting slow, ramping up, progressing down. Is there anything out there that would do it for me?
Last edited by cegarbage; 05-05-2011 at 01:12 PM. Reason: solved.
Can you be a bit more specific? You could do some testing of this nature using random number generating functions in Excel to perform Monte Carlo type simulations, but I'm not sure what you're trying to accomplish.
Can you post a sample spreadsheet with typical data and expected results?
I haven't figured out how to do this, so I have no sample spreadsheet at the moment.
Let's say I have a 2 million dollar award over a 15 month period. On average, that's about $133k of sales a month. But generally sales start out slow the first few months, ramp up and peak at month 4 or 5, then gradually slow down at the end of the period. A normal distribution. I'm looking for a way to calculate the spread of sales over the time frame
I have a lot of other awards with different dollar amounts over different time periods, generally from 9-15 months.
Here's an example of what I'm trying to do. $2M of sales over 12 months is $166k a month. I'm trying to use some type of formula to show a gradual upward slope of sales that peaks in the middle and goes back down. Sales aren't going to be the same every months
In the second portion, I made an attempt to guess and included a chart. It doesn't have the normal distribution/bell curve (not sure of the right word) I'm looking for.
NASA uses a Beta curve to spread labor expenditures over the period of performance; you can read about it in the NASA Cost Estimating Handbook, and in the 1995 edition of the NASA Systems Engineering Handbook, both of which you can find on line.
The curve has two continuous parameters, A and B, that can make the distribution front loaded, back loaded, or somewhat level. Their sum must fall between 0 and 1 inclusive. See attached.
Entia non sunt multiplicanda sine necessitate
Is this file developed by NASA and available for external use, or did someone outside of NASA build it based on the methodology in the handbook above? I'm curious if it's free to use in another organization.
I don't see it posted anywhere on NASA website.
Last edited by cegarbage; 04-23-2011 at 08:45 PM.
Hi
The above solution is nice.
Try this solution.
I use sin function to create a uniform distribution curve.
Use "Goal seek" under tool menu to adjust the total.
In goal seek:
"Set cell" is "C6"
"To value" is your total amount
"By changing cell" is "C4"
It may at least help you to solve the problem if it is not complete
there are two examples
Works on me
Thanks
Regards
Mark the thread as solved if you are satisfied with the answer.
In your first post under the thread tools.
Mahju
The workbook is mine, based on NASA's formula. You may use it as you wish, other than claiming it as your own work.
And if it were NASA's, you could still use it. The US Government is precluded by law (http://www.law.cornell.edu/uscode/17/105.html) from copyrighting material they produce.
Last edited by shg; 04-24-2011 at 01:18 PM.
both of these are exactly what i need, thanks!
You are well come
please remember to mark this thread as solved
Regards
Here's an update with a pair of sliders to explore A & B.
Mahju,
Can you explain the distributer formula? Does it ever need to be modified? And I believe you mean "set cell to 'c5', not 'c6'?
Hi
I think there is no need to change.
The formula in C8 & down uses Sin function (A trignometry function) listed in Math and trignometry group in excel.
At 90 degree it has maximum value (1) and at 0 lowest (0). Gradually increased up to 45 degree and then decreased.
So I divide the total duration by two to have a full cycle in the duration and at 0.5 to add one more month, otherwise it will be zero in the last month.
In C8 I have use sin formula and multiply by 1 through 12 (total duration duration) to increase the amount gradually.
Thanks for correction, Yes I mean C5 (Your total amount) because C6 is empty.
I think now it may be clear
Thanks
Regards
SHG,
The sliders are a huge help, but the value column is no longer present. Can you add it back in? Trying in my own and having some problems... Thanks!
See attached.
Last edited by shg; 04-27-2011 at 03:35 PM. Reason: corrected an error
You had a "value' field in the first iteration that was the shape of the standard distribution, and showed up on the chart in that manner. It appears you added something called period amount, but it does not show the normal distribution. I'm not sure what that value indicates. I
'd still like to see the normal distribution dollar values and a representative chart. I'm trying to re-work it, but it appears a lot of the named ranges have changed names from the first iteration...thanks again!
Last edited by cegarbage; 05-05-2011 at 01:11 PM. Reason: solved
It's not a normal distribution at all; it's NASA's Beta polynomial.
Yes, I'm getting confused on the terminology. Took a closer look at the first and last version. Is it possible to change this into whole dollars? The decimals are throwing me off.
Sorry, I had introduced a bug to the period expenditure formula. Grab the last attachment again, and then tell me what you don't see that you think you should.
Could you change the Y axis to refer to period amount? Thanks...
Select the series on the chart, then drag the data selection two columns to the right.
This board helped me a ton. I've spent 10 minutes trying to mark this as solved. It can't be that hard. Can you point me to the directions?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks