Formula Showing Revenue Recognition Based On Days And Months
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.
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.
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.
We have 5 - six month programs and five - 12 month programs we are going to sell; each have a different price. I Want to insert number of projected sales manually for each month and then have excel distribute/spread revenue evenly over six or twelve months into a separate set of cells. First payment would be received at time of sale.
I am using the following formula to calculate years months and days in Excell 2007 =DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days"
I have added 3 tables data .. also I had created a sample solution calc for emp1 and project1 ... I need to calculation the ratio between the months based on the working days and allocate the efforts accordingly.
I've attached a chart with variance data for each month of the year. I've used an IF formula to "NA" the months with no data, so nothing shows up in the chart. However, I would like to know if it's possible to have the chart only show months with data. The IF formula gets rid of the data point but it doesn't prevent the chart from showing the month on the X axis. In the attached file I manually selected the three months with data.
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.
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?
I use [=IF(F15="","",EDATE(F15,-D15))] to track time frames. The problem is Edate works great for months but on occasion I need days, like 40 days. In cell D15 I have a list where the user must choose from, but one of the choices needs to be in days so the Edate won’t work anymore.
I have set up a datasheet with information to be used in generating several pivot tables:
Column A - Our Invoice Number Comunn B - Vendor's Invoice Date Column C - Type of Vendor (Labor, PM, Subcontractor, Material, or Equipment) Column D - Vendor Name Column E - Vendor Invoice Number/Time Classification (Column C is Labor, PM, this is either Regular Time or Overtime. If Column C is Subcontractor, Material, or Equipment, the Vendor's Invoice Number is enetered.) Column F - Labor Hours Worked (Column C is Labor or PM, a value is entered, if not, formula enters "N/A") Column G - Labor Rate ((Column C is Labor or PM, vlookup value is entered, if not, formula enters "N/A") Column H - Amount Billed ((Column C is Labor or PM, formula multiplies rate by hours, if not, enter the amount of the Vendor's invoice.) Column I - Mark Up Percentage Column J - Mark Up Amount Column K - Total Amount Billed (Column H + Column J)
I need to set up a daily job sheet (like and invoice) for each date listed under Column B. For each day, I need to generate a daily job sheet showing all of that day's information. The location of the information is based on the value in Column C - Subcontractors's data go in one spot on the sheet and Labor costs go in another place.
my spreadsheet is setup with two dates: A date for when someone joins our group A date for when they are released/leave
I want to be able to see exactly how many years/months/days transpired between these two dates. I have tried lots of different formulas with no success.
I have a start date dd/mm/yyyy, and require a formula that everytime a spreadsheet is opened, based on the current date, to calculate how many years, months and days have elasped yy mm dd since the start date...
I have been struggling for almost 2 days with this problem.
I have a list of data, one column of which is the date displayed in dd/mmm/yy format. This date will always be entered by users and is variable.
I can't find a way of counting the number of occurences of each month and each day that the date represents.
So, I want to know how many occurences of March, June, May, Septemeber etc are on this list and Mondays, Tuesdays, etc.
I have tried several posssible routes, DCOUNT DCOUNTA COUNT COUNTIF SUM. I've also tried separating the date out into days & months using the MONTH & DAY functions but this didn't work either.
It also apepars (shock horror) that Excel has incorrect date & day values because entering todays date in one cell (11/05/2006) and then using the DAY function to find the day of the week for this date produces WED when it should be THU. (My system date is set as 1 Jan 1900).
I feel that Excel can't separate the months & days away from the date, since the date is stored as a numerical value and not as we humans use dates. The dates will always be manually entered by users, probably as dd-mm and Excel will automatically add in the year.
How can I count the occurences of each month and days of the week?
...and I need to determine if the difference between them is daily, weekly, or monthly. There are 79 of these lists, on 79 different sheets so I can't visually peg them as daily, weekly, or monthly.
The way I have been looking at the problem is to calculate the number of months between the last & first dates and see if the resulting answer (+1) matches the number of actual dates, and so on...
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.
I am using excel 2013 in my laptop (windows 8.1) after having upgraded excel 2007. Everything works fine during my daily tasks. Especially autofill which I use quite often works perfect.
At the same time I upgraded my girlfriends laptop (Windows 7) with the office 2013 (office 2007 was the older version) and somewhere there begun some isues. Autofill doesn't. Especially with months or days. I enter the value "Monday", I drag down (of course using the black filled cross down right of the cell) the cursor and all the cells get the value Monday. Even if enter the value Tuesday in the second cell and then try to autofill (having marked before both these two cells) I get the same results. Monday, Tuesday, Monday, Tuesday.
I tried to test the autofill with numbers. If i write only the one number (for instance the value "1") and then autofill, this doesn't work. It gives me the value "1" in all the cells. But if I enter the value "2" in the second cell, the autofill works fine (marking these 2 cells and then autofill).
We make some online courses using exactly the same excel exercise files. In my laptop everything works perfect.
In one column I'll have a list incrementing in 1w,2w,3w,1month and I want to be able to count the number of days that have elapsed till the latest cell. Right now I'm just winging it by saying there's always 31 days in one month using a COUNTA function, but I need it to be accurate.
I am looking for a formula that will return the number of months (periods) between two dates, ignoring days and years. Using the above dates, which cannot be changed, the result needs to be 63. The formulas I have tried keep returning 64 because my later date is at the end of a period, and my earlier date is at the beginning.
I have some cells which must be in the format 15/06/2007 15:25
I then need to add either days, months or years onto it.
Say the above date/time is in cell A1, when I do =YEAR(A1)+5 it displays 2012 if I choose the general cell format, but when I select the same cell format (date time) it comes out as 04/07/1905 00:00
We have a requirement to spread/phase amounts over multiple columns (representing months) using a weighting (represents working days per month).
We will calculate the weighting result in excel first (represented by a % per month), so the macro will be more of an allocation of row amount against the equivalent column %.
So far i have the following code:
VB: Sub phasing() Dim SourceA As Range Dim Weight_ResA As Range Dim TargA As Range Dim i As Long
I have subtracted two dates from B2 & B1 using DATEDIF() Function and the results are available in (B5) Year, (C5) Month and (D5) Days respectively. Now the problem is how to Subtract, Add days & Months using borrow, Carry forward to the previous cells (I need formula, function for the above). I have to take 30 days from month ie in C5 if the days (D5
There are dates in column C and I need to count how many days are coming due within 90 days of each date based on the today() function but do not exceed the 90 days.
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost Col B = The number of the month when the costs are starting (1=Jan etc) Col C = The duration or the number of months for which the costs are to spread Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.
Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10 10/15/2009 01/13/2009
