Creating Revenue Recognition Waterfall
Oct 3, 2008
So here's the setup: A customer purchases a service which lasts 6-months. As such, revenue recognition for the company can only occur as the service is provided. So if a person orders a $600 service at the beginning of a month, revenue will be recognized at $100 for the next 6 months. I'm trying to create an Excel Waterfall chart which will show the monthly revenue recognition amounts for all orders depending on the date which they were ordered.
I've attached a sample of what I'm looking to do. It's become tricky for me because Revenue Recognition is pro-rated based on the date ordered (i.e. order on the 20th of a month so at the end of the month 1/3 of the month is recognized as revenue). I'm looking for something that will populate the percentage of the order amount, each month, that will be recognized. I've populated what row 3 should be, but there doesn't seem to be an easy way to apply a formula or something across all cells.
View 2 Replies
ADVERTISEMENT
Mar 15, 2014
I know I will earn $5,000 of monthly revenue from a client. $5,000 is represented in a monthly revenue cell. I have 12 columns showing the 12 months of the year. There is an additional cell showing the customers implementation date.
If a company's implementation date is on or after the 15th of the month (example: 3/28/2014) then the next month (April 2014) is skipped and the $5,000 is returned to columns May through December. All months prior to May return $0.00. If the implementation date is before the 15th day of the month (example: 3/13/2014) then the next month (April 2014) and all months after will return $5,000. All months including March and prior must return $0.00. If the implementation date is unknown then 12/31/2099 would be in the implementation date cell and $0.00 is returned for all 12 months.
Essentially, if the implementation date is prior to the 15th of the month the revenue will show as of the following month. If the implementation date is on or after the 15th of the month the revenue skips the following month and will show the month after.
View 6 Replies
View Related
Apr 19, 2007
I need to create a waterfall structure to analyze a real estate problem. How do I do it?
View 2 Replies
View Related
Feb 4, 2014
I have a data and did not know how to create a bridge charts.
View 1 Replies
View Related
Jan 9, 2007
I have a Excel Charts (waterfall graph) which has let us say 10 items in the rows. the first one represents the intial and the last one represents finish. If I want to delete one item in between the first and last, the finish item vanishes. For example I have the following series and I want to delete item G from it. The Finish item vanishes from the graph
Intial200
A100100
B7030
C9020
D5040
G4020
Finish30
View 7 Replies
View Related
Jan 2, 2008
I am needing to reflect some of my work in a waterfall chart, but do not have that as an option in my list of charts. Is there a down load I can get?
View 2 Replies
View Related
Apr 19, 2012
I was asked to create a Waterfall chart in PowerPoint, which stumped me both in its purpose and how to create it. Is this a standard chart, an add-in, or a chart created by modifying a standard chart?
Two related questions:
What is the best resource (book or web) for advanced charting in Excel/PowerPoint 2010? I just ordered "Charts and Graphs: Microsoft Excel 2010" (MrExcel Library) by Bill Jelen because it had the Waterfall chart in its index. That chart isn't in the "Excel 2010 Bible" or the older "Excel Charts" book by John Walkenbach and is only mentioned briefly in Jelen's "Excel 2010 in Depth."
I'm an advanced Excel user and was wondering whether there are still more charting options in Excel than there are in PowerPoint. I know that used to be the case, but it seems in 2010 that PowerPoint charting has nearly the same functionality as Excel charting or are there charts that are best done in Excel? (...putting aside linking/updating functionality).
View 4 Replies
View Related
Nov 30, 2009
I have a spreadsheet that uses data which is linked to dates on which events are being held.
So, event 1 happens on 5/12/09, event 2 on 6/12/09. What I would like is the spreadsheet to identify the next event occurring and copy the information into a different cell on the spreadsheet.
View 10 Replies
View Related
Apr 25, 2008
I have a column of data (numbers) that are expressed incorrectly i.e 1,000 actually means 1, 0,3 actually means 0.3 and 15,500 actually means 15.5. I can make the first and second examples OK by using the replace command, but how can I check the number format of the cell and then make it change to what I want. All these numbers are in random order and sequence. I'm using the replace command in a macro, is there something I could put in the macro to solve my problem?
View 9 Replies
View Related
May 25, 2009
I play Keno a lot and have what is probably a very simple formula request. If I’ve got for example this result pasted into Excel: 06 12 15 21 23 27 28 31 32 34 48 49 54 56 60 67 68 74 76 77. How would I go about determining if a pair or two numbers had come in?
Say I was looking for 31 and 76, is there some way I can get Excel to recognize the two spot hit then post in response either 1 (in bold) if yes or 0 (not bold) if no the two did not come in together? Note this would not be only one or the other, they both would have had to hit at the same time.
View 3 Replies
View Related
Sep 4, 2009
In the Yellow Cells, I am looking for a formula or Macro that will automatically recognise and give the correct Premises number. As an example, in the first block of Yellow cells, it should be MOR001&2; The second block should be MOR003 and so on...
One could copy and paste the Premises number for each premises, but with 100 entries or more, it becomes a tedious and time consuming task. An autonomous function to recognise the correct premises number would be much easier
In the end, this is to be used when drawing up a PIVOT so that one may easily pick up a premises number or numbers and their related charges
View 7 Replies
View Related
Jul 22, 2014
if it is possible for vba to recognize the name of the file is into.
example:
You file's name is : Track
you change for : Music
Your code automatically recognizes that and change the code.
View 2 Replies
View Related
Aug 1, 2006
I do not write code in it, just use it for school and work on the most basic levels. I am trying to suck an Excel document into SPSS ( Statistical Package for Social Science) for my Engineering internship. I am logging temperature using Omega Engineering, Inc temperature probes. When converting the data recorded on the probe to excel, it does something funny. I will try my best to explain what it is doing. If you format the cell to general, it gives you this (38917). Then simply changing the format to date I get this (7/19). How do I change this to make it so the date is the only memory in the cell. I don't need the other number in there because when I suck it into SPSS it only sees the 38917 number and not the date. I need the date to do my statistical analysis on the data.
View 4 Replies
View Related
May 19, 2012
I am building this waterfall chart. I'd like to put conditional color formatting to change the vertical bar automatically e.g. if it is positive, the bar color is green and if it is negative the bar color is red.
View 3 Replies
View Related
Jan 27, 2012
Using Excel 2007. Is there a straightforward way to do a waterfall chart?
eg. stacked bars running from left to right.
First Column = 2011 Headcount
2nd Column = stacked bar with additions (by category) - stepping up from the 2011 total.
3rd Column = stacked bar with reductions (by category) - steppind down from the first column + 2nd colum total.
4th column = 2011 exit (which will be at the same height as the bottom of the 3rd column).
View 3 Replies
View Related
Jun 4, 2009
I have a bit of a complicated one here so I have attached my book, its probably easier to undertstand the query by looking at the book. Hopefully somebody can help me out here.
I have 2 worksheets:
A results worksheet - this contains all category of results, each category has been given a specific sort id.
A foreign worksheet - this contains all categories found on the results worksheet with sort id = 7.
Now the complicated bit. The whole exercise is too pick up the correct exchange rate for the foreign category (sort id 7). each foreign type has a specific number at the end of its description in brackets e.g (3).
At the bottom of the "results worksheet" is a key which says what type of exchange rate to use for this type e.g. (3) Price of CAD 22.9 converted using exchange rate of CAD 1.9645 = £1.
What I need to do is on the foreign worksheet, it to recognise the number in the item description, keep it in mind then switch over to the "results worksheet" find the keys at the bottom of the page (the key is not necessarily in the same place all the time!!), match the number in the items description with the corresponding key and then find the exchange rate.
As i said very difficult to explain, since I reletively zero experience with this kind of work in excel. Luckily I been muddling my way through a work project gradually thanks to a forum member here. so i hope we can keep this up!
edit:
two restrictions in the way this solution can be done:
1- Results worksheet can not be modified, but it can be referenced to using INDIRECT, and you can make any number of modifications outside of the results worksheet.
2. Any solution must be automatic.
/edit
I have attached my workbook and highlighted the bits I need to fill and the picks I need to pick up.
Would be ever so grateful if some1 could show me the light here.
View 14 Replies
View Related
Jul 29, 2013
I've created a spread sheet of hockey stats to prepare for my fantasy league. I've created a formula weighing different stats differently to create one "Fantasy Value" score. I've done it over 3 years of data and want to find the average 3 year score for each player on the front page of the workbook. There are 300+ names I'm tracking so I'm wondering if there is a way that Excel can recognize a player's name and pull the data from one specific column in 3 different sheets.
I've attached the excel file for your reference : NHL Stats.xlsx‎
View 4 Replies
View Related
Nov 7, 2012
I have a spreadsheet where I need the end result to be auto filled after recognition of an adhoc "top up" amount. How do I get E7 (3170) to automatically recognise the insertion of C7 (3000) to give this result without having to manually update the formula in E7?
DATE
PURCHASE
ORDER
PRODUCTIVE
[Code].....
View 4 Replies
View Related
Nov 11, 2009
I have the following two formulas in a spreadsheet, which sum and count, respectively loan information within a certain date range. Both of these are working correctly.
My problem is in substituting the dates with cell references for dates located in a worksheet with a table of dates. The dates from this table work with simpler formulas, but I cannot get them to work here, regardless of how many () "", etc that I try....
View 9 Replies
View Related
Aug 15, 2013
Attached is my sample data...Indicative Data_Revenue 2.xlsx
My aim is to populate columns C to G (Q1,Q2,Q3,Q4) automatically via vba against a particular account name(in expected output tab).The account names will have a Key word (listed in the "List Of Account" tab)The Q1 Sum should be a sum of Q1 revenue against all such accounts containing the Key word. Same applies for Q2,Q3 and Q4.Each account has 3 types. viz, BAU, Top Commits and Strong Prospect.
The BAU quarter sum should be pulled from "Revenue 1 - Assured" tab where Q1=Apr+May+Jun, Q2 = Jul+Aug Sep and so on. The Top Commits Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage 4. The Strong Prospect Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage
Currently I am using formulas but there is too much of hard-coding and its becoming difficult to manage as Accounts increase.
View 9 Replies
View Related
Nov 21, 2007
I own a local restaurant and want to streamline my excel spreadsheets. Currently, I have yearly workbooks (2005, 2006, 2007, etc.) that have all of my revenues included in them, split up monthly by worksheets. I then have lines for each day within the month for the revenues earned at that day.
So, if I want to find how much revenue I earned on March 1st, 2006, I go to my 2006 book, March worksheet, and look at the 1st. Here's where my problem lies.
I want to create a new spreadsheet where all I have to do is type in a certain date (say March 1st, 2006) and all of the information for the date will be automatically displayed on the new sheet.
View 14 Replies
View Related
Apr 21, 2009
I am trying to figure how to get a formula to work that will spread revenue number over a four month period. The dates will be dynamic so they will change but the spread stays the same.
I have attached a snapshot of what I am trying to do.
View 9 Replies
View Related
Feb 6, 2010
I have a table which shows the monthly revenue of Company ABC , by client / by location / by business line.
ClientCountryBusiness LineJan-10 Feb-10Mar-10A001SingaporeResidential$ 50,000.00 $ 23,333.00 $ 54,115.00 D003SingaporeRetail$ 50,000.00 $ 21,548.00 $ 54,654.00A001SingaporeCommercial$ 63,321.00 $ 75,542.00 $ 21,564.00 D003AustraliaResidential$ 26,564.00 $ 50,000.00 $ 45,654.00 G002AustraliaRetail$ 50,000.00 $ 21,546.00$ 63,321.00G002AustraliaCommercial$ 26,602.00 $ 65,341.00 $ 24,568.00 A001Hong KongResidential$ 33,565.00 $ 26,564.00 $ 64,454.00 G002Hong KongRetail$ 54,232.00 $ 50,000.00 $ 15,454.00 D003Hong KongCommercial$ 26,564.00 $ 21,564.00 $ 23,333.00
If I want to calculate the total revenue for the month of January based on the following variable criteria:
Criteria
Client: A001
Country: Singapore
Month: Jan-10
I can achieve that with the use of SUMPRODUCT. However next month, I will then have to manually change the SUMPRODUCT formula so that it will extract data from the Feb column instead of the Jan column.
Is there a formula which will not require me to change the formula every mth? Ie. I can get my answer simply by changing the criteria?
View 9 Replies
View Related
Aug 6, 2006
I made 2 sheets: the first one contains the database and in the second one i want to analyze it. Now i am looking for a special sum- function. I want to sum total revenue for a particular company (criteria 1), in a filtered country (criteria 2), month (criteria 3) and class (criteria 4). I'd like to first execute the three filters (country, month and class) and then be able to total revenue of that particular company. Note that after the filters still several companies are visible in the database. Does anyone know how to calcalate this in cell B9:B12 (Analyze sheet) of my attached file.
View 9 Replies
View Related
Jun 26, 2014
I have sales data for Clients that has the client name month and year(combined for date) and revenue for each month. I would like to take all clients in a given month and see the average revenue per client and then be able to display that in a bar chart by month. I also have the data loaded into a PowerPivot Data Model.
View 1 Replies
View Related
Dec 27, 2013
I have a deferred revenue model. Revenue from each month earns out at an equal rate over twelve months. Sales for each month are in column B (B21) and C. I created formulas for the first year but when I get to the second year, I'm not sure what to do, because the earned premium will include values from previous year sales and current year sales.
View 4 Replies
View Related
Jan 8, 2014
I have to calculate the flat revenue split by month between 2 different dates. The two dates may be in the same year or different years.
excel formula for the same.
Is it possible to do the same in pivot?
View 1 Replies
View Related
Jan 13, 2014
Any way to set up Solver in order to maximize revenue, but with the constraints equaling ONLY one of two values (e.g. 0 or 100)?
View 1 Replies
View Related
Mar 4, 2009
I need to work out Revenue Rank & Year to Date calculations.
Consider a simple table:
| Partner Name | Year | Month | Revenue |
------------------------------------------
| John Smith | 2008 | Nov | 2000 |
| John Smith | 2008 | Dec | 2200 |
| John Smith | 2009 | Jan | 1898 |
| Mary Smith | 2008 | Nov | 1767 |
| Mary Smith | 2008 | Dec | 1867 |
| Mary Smith | 2009 | Jan | 1953 |
------------------------------------------
etc..etc...
I'm not using Pivot Tables since there are more complex issues around presentation which are preventing me doing this so are using good old formulas..
Revenue Rank is in reference to the Partner in this case. I need to be able to say John Smith is rank x out of xx by summing up his revenues for both:
a) one month
b) a range of 3 months back
How do I work this out? Especially the date calculations when I just have a year and month in separate fields?
View 9 Replies
View Related
Sep 27, 2007
I have a spreadsheet that holds a list of customers and the contracts they have with my company - this sheet includes the Total Contract Value in £s - so how much each customer will pay us for the service - the period of the service - so the start date of 01/01/08 to end date of 31/12/08 for example. What I then have is all the months across the top of my spreadsheet and I need to extrapolate the contract value, based on the period between the months.
Initially I had complicated formulas that simply divide the value by the number of months of the contract - however, my company has since changed the policy and requires it to be done to the day.
For example, if the value was £12,000 and the period was 12 months from 01/01/08 - to calculate January 08 it would be - 12,000 / 365 * 31, Feb would be 12,000 / 365 * 29 and so on...
Does anyone know how I can do this - bearing in mind that each contract won't be as easy as 12 months - it may be start date of 15/06/08 to 08/10/10 for example... If anyone can help me do a formula or formulae to calculate the value for each month - I would be very very grateful - as always... [/img]
View 9 Replies
View Related
