Help figuring out how to represent data

[cranfordrd]

I would appreciate some thoughts on how I can best accomplish th
following task. I have a spreadsheet showing projected billing amount
for the next 12 months, the columns are labeled with the month and eac
row represents a different "type" of bill, the data is the amoun
billed in each month for each type. These are all projecte
figures,each month I will need to somehow reflect the ACTUAL billin
that occurred and then come up with the % difference between th
projection and actual. How can I best represent this in a spreadshee
(umm.. maybe I should go to Access?). In addition, I have cas
projections which are based on the amount billed but once I figure ou
how to handle the above I can incorporate the cash projections. Thank
for any thoughts you may have

 

[Paul]

I would appreciate some thoughts on how I can best accomplish the
following task. I have a spreadsheet showing projected billing amounts
for the next 12 months, the columns are labeled with the month and each
row represents a different "type" of bill, the data is the amount
billed in each month for each type. These are all projected
figures,each month I will need to somehow reflect the ACTUAL billing
that occurred and then come up with the % difference between the
projection and actual. How can I best represent this in a spreadsheet
(umm.. maybe I should go to Access?). In addition, I have cash
projections which are based on the amount billed but once I figure out
how to handle the above I can incorporate the cash projections. Thanks
for any thoughts you may have!

The way I have done something very similar to this is basically to have
three worksheets laid out with identical row and column headings. The first
contains projected data, the second actual data and the third has formulas
that calculate the percentage differences.

You already have the first, with your projected data, so you could just copy
this complete worksheet twice (right click the worksheet tab, use "Move or
Copy", then don't forget to check "Create a copy"). Then, in the second,
select and clear the data area, and type in the actual data as it becomes
available. In the third, put a formula in the first cell where the other
worksheets contain data (say B2). Then copy this and paste into all the
other relevant cells. Format all these cells as %.

The formula could be simply
=Sheet2!B2/Sheet1!B2-1
However, you may prefer to suppress the spurious results where actual data
is not yet available, in which case you could use this formula:
=IF(Sheet2!B2="","",Sheet2!B2/Sheet1!B2-1)

Alternatively, you may prefer to have everything on one worksheet so that
each month has three columns side-by-side: projected, actual and percentage
difference. In principle this is similar to set up. I would suggest setting
up the extra two columns for January, including the formulas; again you type
in the first and copy/paste to get the others. Then you can copy these two
columns and insert a copy for February, then for March, etc.

Suppose the January columns were B, C and D. Then the formula for D2 would
be
=C2/B2-1
or
=IF(C2="","",C2/B2-1)

 

[PegL]

There are several ways to do this, depending on your
preferences and the details of your project. You can have
one sheet that contains your projections, another sheet
(same format) that contains the actuals and a 3rd sheet
that summarizes them. You could also add columns to the
sheet you have for actuals and variance. In that
scenario, January would have 3 columns. One thing to
think about: Do you have the same Bill types every month,
or does it vary? That may affect how you want to do this.

 

[Paul Falla]

One way to do it would be to have 3 separate worksheets in
the same workbook. Copy and paste your projections
information onto the second sheet, and change the
description(s) to actual instead of projection, and do the
same for the % diffs and put the calculation into the
amount fields.
Alternatively, it may be feasible to add new columns for
for actual billing and % difference