Some Excel Ideas

Note: This post took forever to develop. Creating examples took a lot of out me so I’m thinking of doing the second half next weekend.

Over the holidays I was thinking over the conversations I had with others about using Excel and it got me thinking about ways I use Excel that others don’t. It looks like, from what I’ve seen, that most people stick to additions, subtractions, multiplications and divisions and maybe some VLOOKUPs here and there. They don’t actually make Excel do the work for them. I’m not sure why.

Here are some ideas I’ve used with Excel, some easy and others more difficult to implement, but they move beyond simple additions and subtractions. It would be better to use a software or application such as SAP to do most of the heavy lifting and I start from there. I start with the main application and try to make it do the work. But there are times when the main application does not do the “last mile” and you’re forced to use Excel.

Idea 1: Comparing Data

You can create a pivot table that compares two files or two datasets. Examples would be comparing actuals to budgets, comparing payroll deductions with your healthcare provider’s data, or simply comparing your file with another. It works best if both datasets or files have the same fields you want to compare (both rows and header fields) that way your pivot table can recognize the pairings. You will have to add a column to denote which dataset or which file the data came.

Idea 2: Timeline of Spending

Use pivot tables to create a timeline of payments or cash flows to visually see how the numbers flow. This is one way of converting numbers into visuals for analysis.

Idea 3: Workload Analysis

I once worked where we had up to hundred plus projects alongside numerous project managers (PM), so I did some workload analysis. Amazingly, the workload was dispersed evenly amongst the project controllers (PC) – I didn’t have to do anything. The attached document showed some tricks on how to pull such information; these tricks include array formulas which can be tricky and takes a while to learn. There are times when pivot tables won’t work and you’re forced to use some kind of formulas – in this case, array formulas.

Idea 4: Predictive Analysis

You could also do some type of predictive analysis. As a project controller, I devised a projected spending analysis versus contract value to see which projects could potentially run out of budget before the end of the accounting period. Again, I’m talking about tracking up to 100+ projects. If the project manager ran out of money and did not receive special authorization to continue with the project, the accountant would not book any revenues for that project for the period because that revenue could potentially be at risk. So, I created a spreadsheet that would identify the projects that could potentially run out of funding based upon its historical spending rate. This was a real quick and dirty effort where I would just download the spending data, plop it into my spreadsheet and presto, the spreadsheet identified the projects that we potentially needed to get spending authorization.

You might need to move the scroll bar to the left as the window seems to have shifted to the right.

Here’s where I’m stopping. I’ll continue next week with only a few more ideas that I’ve used over the years.

---