need help deciding which formulas/format to use

I

iamdazey

Basically, I have a spreadsheet with a list of clients and when they signed
up for their services with my company. Some of them pay monthly and some pay
annually. The company owner wants to see a month by month breakout of the
money we'll have coming in, basically a forecast.

I'm a fairly savvy Excel user, but it's been a while since I've undertaken a
task this complicated, so any suggestions would be extremely valuable.

Thanks everyone!
iamdazey
 
D

Dave O

We'd need to know what information is available. For instance: do you
have an indicator for each client that says "monthly" or "annual",
along with the amount they should pay? Do the annual payers pay on a
particular date, or do they all pay on January 1 or some other date?

And then we proceed to the desired report format: does the owner want
to see an annual forecast, a rolling forecast of the next 12 months, a
monthly forecast? Please let us know!
 
P

Pete_UK

In addition to the columns you already have (client name, address, date
etc), you might need a column for the annual amount they are committed
for if this varies for each client. In another column you could record
whether they will pay annually or monthly ("A" or "M" - maybe also "Q"
for quarterly).

Then basically you will need 12 columns, one for each month and a
formula copied across and down which will take account of the
start-date, the payment frequency and the annual amount to determine
the income for that month - presumably annual and quarterly payments
are made in advance?

Finally, you could total each column to determine the projected income
for each month. If you want to add more clients as the year unfolds,
you might like to put the totals at the top of the sheet and use a sum
range beyond the number of clients you currently have.

Hope this helps.

Pete
 
I

iamdazey

Sorry for not giving enough info!

First, this spreadsheet comes from a database that I do not manage, so the
columns that are given to me are as follows: Company, Contact, Phone, Email,
Street Address1, Address2, Address3, City, State, Zip, Start Date, End Date,
User Type, Monthly Revenue, Prepaid Revenue, User Name, Password.

The "prepaid revenue" column is if a customer pays for a yearly
subscription, and it is good for one year from the day they pay.

And what I'm looking for is a way that my boss can populate this spreadsheet
with new users as they sign up, or as current users pay, and that will remain
accurate without me having to mess with it. So, in essence, I want something
relatively low maintenance on my part.

Does that help clarify some issues?
 
R

Ronco

I have a similar profile spreadsheet. Try Excel formula =TEXT(the cell of
the date of payment,"mmm") to give you the month, =TEXT(the cell of the date
of payment,"yyyy") for the year. Then use these two fields (columns) for the
Column portion of a pivot table. If you ever have the need, you can collapse
all the months of a particular year by double-clicking the year cell in the
pivot table. If you're not familiar with pivot tables, you might want to
take an hour to learn about them. They are extremely valuable tool.
 
I

iamdazey

Pete,

Good suggestions, thank you! I added some more information in reply to my
original post, in case that helps. Also, I'm wondering: what if I wanted the
monthly totals to be on a separate sheet, but in the same workbook? I have
an idea of how it would work, but I don't know what type of formulas I should
use.

Any ideas?
 
I

iamdazey

I haven't actually used Pivot Tables before, but I was curious about them. I
will definitely look into this option, thank you!
 
P

Pete_UK

I would advise you to do all the calculations in one sheet - from what
you have said this may continue beyond 12 months, so you will
accumulate more and more sheets otherwise.

You may then have two or three summary sheets giving you (or your boss)
different views of the data. You can obtain summaries of the income for
all months (or selected months if you prefer) by referencing the data
in the main sheet - you need to have a clearer idea of how you want the
data to look. If you follow my earlier suggestion and have the totals
in the top row of the main sheet, then you would have something like:

=main!R1
=main!S1
=main!T1

to bring the data from cells R1, S1, T1 etc into another sheet. You
will probably also want to bring the month from the header row below
it.

Hope this helps.

Pete
 
I

iamdazey

Alright, I ended up adding two columns: sub type (values being "A" or "M")
and renew month (also text). Then I used sumif formulas to get the financial
data on a separate sheet.

I was really hoping there would be a way to get the results I wanted without
adding more data (columns) to the sheet, but it was getting waaaay too
complicated. I think the new columns simplified things marvelously. And in
the end, my boss is completely fine with me changing his spreadsheet, so long
as it tells him what he wants to know, and it does. So, I'm happy with that.
:eek:)

Thanks for the suggestions, they definitely helped!!
 
Top