What quarter is it?

April 2, 2011 by 1 Comment

When you’re working with financial data (forecasts, revenue or sales targets, financials), you are certainly used to working at the annual and monthly level.  And doing so, whether with a spreadsheet or accounting software, is straightforward.  Grouping by quarter, though, is not as simple, at least when working in Excel.  But, as we accounting and finance people learn, it can be the most useful.  Monthly information often represents too small a slice of data to offer any useful insight- at least in its own context.  On the other hand, while annual information gives you a much better feel for what’s going on with the business, it happens too infrequently to allow management or ownership to do anyting about it.

It makes sense, then, to organize and analyze important information by quarter.  After all, there’s a reason big companies report these results to all who are listening. Read more of this post

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

A Bit of Workout Advice

March 30, 2011 by 3 Comments

Knowing why you're training is the first step!

Jared, a friend of mine at work is getting himself back into shape.  He comes to me now and again for tips and to answer any questions he might have.  I consider it a privilege and obligation to be as helpful as I can.  This morning, he walked into my office and told me that, after a few weeks of working out, he was no longer getting tired, or at least not as tired as when he first started.  Well, first I chuckled and told him that he was probably bullshittin’ (anybody who knows me, knows that’s one of my patented sayings; in fact, I’ve reserved the domain stopbullshittin.com for later use!), After that, I gave him my thoughts.

I first asked him to give me an idea of what his workouts were like.  He said he was trying to get toned (then ripped, but first things first) and that he typically did about 15 reps (repetitions) for each set he did.  After hearing him talk for a few minutes, I realized this was something that I’ve heard–in one shape or form–several, several times in the 20+ years I’ve been working out.  I feel blessed to be able to have kept in shape for this long, both for the physical ability to work out and also for the inner drive God has given me.  And as is the purpose of this site/blog, I am here to share those gifts with others. Read more of this post

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

Hiding your work- Part 1

March 25, 2011 by Leave a comment

What if you want to hide your work?

There are times when you are working on a spreadsheet, when you must create placeholders. Perhaps you are applying some custom formatting to a specific area, but must reference information in another sheet. (For more information on Custom Formatting, see Custom Formatting). You’ll quickly discover that Excel doesn’t allow you to do that. Or maybe you are modeling your business and want to refer to a different sheet and perform different calculations when the month in question is closed and you have actual data than when it is in the future and you are forecasting. An easy way to tell Excel where to look is to put an “A” (for actual) or “F” (for forecast) in the top row. (How you get Excel to automatically determine which to use can be done in a number of different ways, but some functions you might use INDEX, VLOOKUP, IF, or MAX functions; I’ll be sure to fit in a discussion of each of those at some point.)

But you don’t necessarily want the user of the spreadsheet to see this work, right?  Well, you can hide it in one of several ways. You could change the font color to white (the same color as the background), but any user who selected that and other cells, would still see the cell contents. Read more of this post

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

It’s Absolute(ly) Relative

March 23, 2011 by Leave a comment

If I had a dollar for every time someone asked me “what the heck are those dollar signs for?,” I’d be a millionaire.  Well, anyone worth their weight in gold as an Excel user should really understand what those pesky dollar signs are used for.

Most of us know how to use relative references

It’s really pretty simple.  The inclusion of dollar signs ($) in a formula changes at least part of the cell reference from relative to absolute.  Let me explain.  You see, Microsoft Excel is a very, very powerful tool. So powerful, in fact, that more times than not, it tries to help you get your work done faster and more effectively, even attempting to save you, the user, from yourself.  Like auto-numbering and spell-check in Microsoft Word, this type of “help” is what we’ve come to expect with Microsoft products.  So when you copy a formula for use somewhere else in your worksheet, which I’m sure you’ve done, Excel assumes that you want to keep the same relationship.

For example, say if you have three columns of data that represent daily transactions for your store.  Column A represents quantity sold, Column B lists the price per item, and Column C is the product of the two (A*B).  Because of this intuitiveness built into Excel, you need only enter “=A2*B2” into column C (assuming the first row is your column headings).  You can then copy and paste or fill-down the formula. And, as I’m sure you know, Excel will continue with the relative positions (A3*B3, A4*B4, etc.). Read more of this post

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

Easy way to get the last day of the month in Excel

March 19, 2011 by Leave a comment

There are a lot of times I need to get the last day of a particular month.  One example I’ve used lately is trying to forecast sales for the remainder of a month using the average to date.  If it’s February 12, for instance, and the average daily sales so far are 12k.  I want to extrapolate that out for the rest of the month.  To do so, I need for Excel to know how many more days are remaining in the month- February.

Excel makes this very easy in one of two ways: Read more of this post

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

← Older posts

Newer posts →