Is there an easy way to generate this matrix and then graph it?
Define "easy." You have a "surface" in 5 dimensional space, and you want to "picture" it using a 2 dimensional medium (paper or screen).
Assuming that this is the best way to analyze this sort of thing, I might generate the tables and charts like this. The way I usually represent multidimensional data is in contour plots. One contour plot can show 3 dimensional data. It might look like this:
x,8
z,50,,d
,,,180,=d3+30,copy of e3,copy of e3,...
,,y,50000,=product($B$1:$B$2,$C4,D$3),copy of D4,copy of D4,...
,,,=C4+50000,copy of D4,copy of D4,...
,,,copy of C5,copy of D4,copy of D4,...
...
From that, plot C3:J13, and you should get a nice contour plot showing r vs y at constant d, x, and z (assuming it is plotted as columns. If Excel decides to plot as rows, then you will get a contour plot showing r vs d at constant y, x, and z).
From there, I would make 5 copies of that tab, increasing z in each tab. Then make 5 copies of that block of 6 tabs (for a total of 36 tabs) to generate contour plots at all 36 combination of x and z. By comparing individual contour plots, you can show the trend in r as each variable changes. It sounds like a lot, but, once you have one table/chart created, it is a relatively simple operation to make copies.
If I wanted to show it on one contour plot, I would probably put the lowest and highest combinations of x and z only into one plot, recognizing that the function is "linear" so the in between "contours" will fit smoothly in between these limits. All 36 combinations will almost certainly show too much information for one plot.
As shg points out, without a discussion of the interrelationships between the independent variables (how does increasing price decrease the number of people per year, for example), the function is very linear and constantly increasing. It will be up to you to decide if this kind of analysis will really show you what you want to see.
Bookmarks