I consider myself modestly competent in Excel, but I’ll admit there’s a whole element I’m blind to, and that’s some of the more complicated analysis functions. I have a new need to generate a bunch of values, but I don’t have a grasp on what to call what I’m trying to do, so Google just isn’t there for me right now. I’m not (necessarily) looking for a solution to my need here, but pointers in what I should be looking for to learn for myself.
The setup is this. I have a fairly large, but also relatively straightforward spreadsheet that allows me to calculate gross profit and gross margin from a unit cost and proposed markup. This spreadsheet includes values from calculations like overhead dependent on office locale, software and hardware costs, area tax implications and client requirements. Although there are in effect, many many variables at play, for the most part I only am changing unit cost and markup, from which I’m looking at burdened GM and GP.
What I want to do is to take a range of potential unit costs and a similar range of potential markups and generate a a corresponding range of GM and GP values. I currently do this manually, by plugging in my start values and copying the outputs into a separate sheet for each set of other assumptions. This takes a while, makes it difficult to change variables on the fly in a meeting, and most importantly is so dull I could die.
Any ideas on what I can do to leave my base sheet alone, and set up another tab with the seed values and a spot for the outputs?
To clarify, you simply want to be able to load and calculate a series of numbers in place of the single numbers you’re calculating now? And these values would be for the same variable you’re you’re entering one at a time now?
I’ll think about some code, but it sound to me like you need a repeating loop, and between loops you need to increment your two variables, until you reach the final values.
You’ve got it more or less. As an example of my thinking, if I ported this spreadsheet to Apple Numbers, it would be pretty trivial for me to write an AppleScript that would step through my desired permutations and give me my output, with my only remaining task being to make it pretty.
BUT, I am also thinking that this static data output helps me once, but not down the road. Imagine this scenario: I have a table that represents my two outputs from my two variables. Now I want to flip my home office from Sioux City to Grand Rapids and see the numbers change based on the different burden. SO in otherwords, I want the table of outputs to be ‘live’ in case I want to change some of the other variables on the fly.
Seems like what you want is to take a new sheet in your spreadsheet, with a range of unit costs running down a column, and a range of proposed markups running across the top. (I believe Excel can fill in ranges like that easily.) Then at each intersection in that table, you want to take that unit price and that projected markup, run them through your existing spreadsheet as if it were a function, and put the resulting gross profit in the cell at that intersection.
What I don’t know is how to convert your existing, working spreadsheet into a callable function. But some Excel guru probably can. Maybe some VBA programming?