Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help deciding which formulas/format to use
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help deciding which formulas/format to use
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help deciding which formulas/format to use
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
more info
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? "iamdazey" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help deciding which formulas/format to use
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. "iamdazey" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help deciding which formulas/format to use
Sorry about that Dave! I posted further information in reply to my original
post. "Dave O" wrote: 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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help deciding which formulas/format to use
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? "Pete_UK" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help deciding which formulas/format to use
I haven't actually used Pivot Tables before, but I was curious about them. I
will definitely look into this option, thank you! "Ronco" wrote: 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. "iamdazey" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help deciding which formulas/format to use
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help deciding which formulas/format to use
Thanks for feeding back.
Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|