Basic Spreadsheet Monkey Math: Scaling a Series
Part of my job is being a spreadsheet monkey that analyzes or creates mountains of data. One of the things I love to do is find quick or elegant ways of doing things in Excel. Very commonly, I have to do is to take a cash flow stream that totals one number and "scale" it so that it totals a different number, but keep the relative amounts the same. For example, suppose you are budgeting the sales trip expenses for your department for next year. Usually you would start from this year's expenses, say they were $30,000 . Now suppose that your boss wants you to improve your performance and make the same amount of sales but use only $25,000 next year. It might not be right to just budget 1/12 of $25K every month because most businesses are seasonal. Maybe your salesforce doesn't travel as much at Christmas and during the middle of summer because both the salespeople and the clients are likely on vacation. Most of the business trips happen in Spring and Fall.
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Total |
| $1 | $2 | $3 | $3 | $2 | $1 | $2 | $2 | $5 | $5 | $4 | $0 | $30 |
The easiest way to keep the seasonality of the cashflows but scale them so they meet the target is to multiply each number by 25/30, which is how much the new target is in proportion to the old target. Now each month will also be scaled by the same proportion.
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Total |
| $1 | $2 | $3 | $3 | $2 | $1 | $2 | $2 | $5 | $5 | $4 | $0 | $30 |
| $0.83 | $1.67 | $2.50 | $2.50 | $1.67 | $0.83 | $1.67 | $1.67 | $4.17 | $4.17 | $3.33 | $0.00 | $25.00 |
On very important thing to watch is that you don't blindly include errors. In real life there are often exceptions. For example, maybe you have normal travel bills but in one month you discover an error where the salesperson submitted his expenses for $100 but accidentally wrote an extra one, making it $1100. Once the mistake is found, possibly the next month, it is corrected and the $1000 is returned to the company. Now if we are making the budget and we decide we want to double travel expenses then simply multiplying everything by two will get your manager is trouble one month because he will have to go get a job to earn $2000 to pay back to the company! That is because you scaled up a correction that probably won't happen next year.
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Total |
| $1 | $1 | $1 | -$1 | $1 | $1 | $1 | $1 | $1 | $1 | $1 | $1 | $10 |
| $2.00 | $2.00 | $2.20 | -$2.00 | $2.00 | $2.00 | $2.00 | $2.00 | $2.00 | $2.00 | $2.00 | $2.00 | $20.20 |
You can get far if you mechanize things like scaling cash flows. Simple skills like this help me analyze a lot of data, but you also have to have knowledge because as smart as formulae seem, they're pretty dumb sometimes.
