Data Tables

July 3, 2011 by Leave a comment

Performing what-if analysis in business is very common, crucial even. What if a new project is delayed by 3 months? Or what if the company is unable to secure financing at the projected cost of capital? Successfully answering questions like these often means the difference between a business finally turning the corner and becoming profitable and, well, it not. But this type of analysis is also helpful for individuals. Far from overly complicated but seldom used by consumers, tools that allow for thoughtful consideration of important personal finance decisions should be used much more often then they are. They can help one understand how much more a car will cost over time or what term and interest rate combination is required to make sure the monthly payment fits into the family budget. They can even reveal how your credit scoreis helping or hurting you.

Data Tables, one of the features in Excel's What-If-Analysis Package, is a very useful tool for comparing.

So let’s dig in. How would you perform this type of analysis? What tools would you use? Microsoft Excel, of course! Until recently, I’d approach this—or any other 2 dimensional, two variable scenario– by building a grid in Excel with all of the intersection values. For example, I might look at an auto loan with six possible interest rates, 5% to 10%, and five different possible terms, ranging from one to five years. To fill out the grid, I’d utilize the power within absolute and relative references to allow easy copy and filling across all rows and columns of the grid. Read more of this post

Advertisement

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with , , , , , , ,

Your own personal Problem SOLVER

April 11, 2011 by Leave a comment

The business world is full of “what-if”s.  What if one of your suppliers raises its prices significantly?  What if your boss demands that you spend at least 20% of your advertising dollars on sports programming?  What if the corporate office suddenly shrinks your overall budget by 25%?  With scarce resources, companies (and individual consumers, as well) are always looking for ways to get the most bang for their buck.  Making matters more complex and difficult to navigate, often times decisions involve careful manipulation of several equally (or almost equally) important levers.  For example, you want to minimize your costs, but the cheapest vendor is not as effective or dependent as the more expensive ones.  And even if you had one clear choice, you never want to put all of your eggs in one basket, depending too heavily on one supplier.  Or maybe you’re asked to minimize your costs on marketing and advertising, but must reach a minimum number of potential customers.

One way to solve these types of problems is to build a spreadsheet, linking all of the important variables and calculations.  You could then give the user (you or someone else) the option to change those variables, viewing the results of different scenarios as changes are made.

The problems with this method should be obvious.  Not only is it tedious, requiring the user to manually change the numbers in order to see what happens, but it is also incomplete.  Insofar as these problems involve more than a few moving parts, it’s nearly impossible to manually account for and analyze every conceivable combination or possibility.

Luckily, Microsoft Excel provides a nifty tool to do all of this work for you.  You can think of it as your own personal problem Solver available at your command.  What more could you ask for? Read more of this post

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with , , , , ,