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.

Spread Revenue Equally Over 6 Or 12 Months Depending On Which Product Is Sold

Jul 15, 2014

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.

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.

Formula To Spread Costs Between Months And Years?

Dec 3, 2013

See attached. (I added in some arrows for the first month so it is easier to follow the logic)

I am working with monthly revenues. Revenues are earned in a specific month but only a % is received in that month and over the next 4 months. I need a formula that will spread these revenues earned in 1 month over the next 4 months.

Currently, I have a complex Index/Match formula which works until September. September, October, November, and December do not work correctly because once the months roll over from month 12 (December) back down to month 1 (January), my formula cannot recognize the range.

Essentially, I need the formula to return the correct amount that is being paid, even across different years.

I don't know if my formula can be modified or a new formula would be more efficient.

(This post originated from Commercial Services, which I just requested be removed from that forum)

Macro To Spread Amount Over Multiple Months Using Working Days As Weighting

May 5, 2014

Phasing Design.xlsx

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

[Code] .....

Conditional Format - Dates Greater Than 18 Months And 24 Months?

Dec 6, 2013

I have a date column (Column E: Date Entered) on my spreadsheet that I need to set conditional formatting on. There are two conditions:

1) 18 months from the date in the cell needs to be highlighted yellow

2) 24 months from the date in the cell needs to be highlighted red

Elapsed Months (many Months Have Gone By Since Todays Date)

Jul 19, 2009

Cell A1 is a past date. In cell B1 I would like how many months have gone by since todays date. eg. Cell A1 = July 07, B1 would = 24 months.

Add Months & Return Decimal As Years & Months

Jul 31, 2007

I am looking for a formula that will add months and return the year. E.g. if I add 1.05 and 1.07 I should get 3.01. i.e. 3 years and 1 month.

Revenue Summation By Account Name

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.

Find How Much Revenue I Earned

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.

Calculate The Total Revenue

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?

Sum Revenue After 3 Filters And 1 Criteria

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.

Getting Average Revenue Per Client By Month?

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.

Formula For A Deferred Revenue Model?

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.

Calculating Revenue By Month Between Two Different Dates

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?

Set Up Solver In Order To Maximize Revenue?

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)?

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.

Revenue Rank & Date Calculations

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?

Extrapolating Revenue Over Various Time Periods

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]

Aug 23, 2008

I have created a model that shows me the money that I am owed each month. However, I receive the money over a 3 month period.

Using a formula I need to determine how much I actually receive in total each month, factoring in that I receive each months revenue in three equal payments over three months.

For example

Month 1 Month 2 Month 3 Month 4 Month 5 Month 6
\$A \$B \$C \$D \$E \$F

In month 1, I am owed \$A, but this payment will be split over 3 months.

So in month 1 I collect \$A/3. In month 2, I am owed \$B but this payment will also be split over 3 months so in month 2 I collect (\$A/3 + \$B/3), etc etc. In month 3 I collect the (\$C/3) + (\$B/3) + (last payment of \$a/3) etc etc

How do I present this in a formula so I dont have to adjust every single column manually?

I need to follow the same structre for another scenario using 18 months as well.

Figure Out How To Interpolate Revenue Values

Nov 7, 2008

I have a question on data interpolation with Excel 2007. Normally, this wouldn't be too much of an issue for me, but for some reason I cannot figure it out.

I'm working on an Income Statement, which is designed for 5 years. I have values 1 and 5, which are given to me (B7: 3,500,000 and F7: 5,200,000). I'm trying to figure out the other 3 years of revenue assuming a growth trend. The values should fill the series B7:F7.

Revenue Allocation Using Combined/Nested IF Statements

Jul 24, 2008

I have been asked to go through some information which has over 200 000 lines, what I need to do is as follows:

where order number is unique, revenue source = "Unique"
where order number appears more than once, check division to see if it appears in more than one division, if it does revenue source = "Cross Selling", if it does not then revenue source = "Divisional Package"

I need all of the above in formula line which will be inserted in the revenue source column

I have attached the file with a sample of the information I am using.

Conditional Formatting - Calculate The Numbers Of Month And The Revenue?

May 5, 2014

i wish to do some conditional formatting. my target for the first 6 months from date activated is 50,000 after 6 months is 100,000. and for those below target, i will need it to be highlighted in red. thus i have 2 sets of conditions.(calculate the no. of months and the revenue).

How To Add Total Revenue And Cost For Each Product And Overall To Pivot Table

Aug 5, 2014

Pivot_example.jpg

Regarding the attached pivot table screen shot, I would like to be able to add total revenue and cost for each product and overall to this pivot table. IOW, instead of just showing the net amount of 3,200 for all blenders, I would like it to show Revenue of 12,500, Cost of (9,300), and the net amount of 3,200 for all blenders (i.e., for Boise and Chicago combined), in addition to the totals for each of the two plants. Also would like to see total revenue, cost, and net for all products and plants combined at the bottom. Can this be done?

Sum The Sales Revenue Column Of The Attached Per Month Into The Yellow Highlighted Cell @ The Bottom

Sep 30, 2009

is there a formula i can use to sum the sales revinue column of the attached per month into the yellow highlighted cell @ the bottom?

Nov 6, 2008

writing a formula or creating a macro

The first step will be to get the formula or macro to fill in the cells under the date columns that will show how the consumption of the 555 is assessed to be spread out over the period. The formula should portray an equal distribution of the consumption over the period.

******** ******************** ************************************************************************>Microsoft Excel - claimv1.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2=BCDEFGHIJKLM2Start DateEnd DateTotal consumptionLast week day14-dec21-dec28-dec04-jan11-jan18-jan25-jan01-feb317-dec24-jan555Assesment        4   Actual        Sheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Apr 30, 2006

i have to make it to where when i put a number in it also puts in the description and
unit price just by the number i put in to a cell. I am making an invoice... and using a look up table to do the vlookup function but dont know what i am doing wrong

Dec 29, 2009

I've enclosed a sample summary page to see how I'm going to use the formula.

Name Of Both Column APPEAR IN SPREAD SHEET

Mar 4, 2008

I have a spreadsheet with names on and I need to check to see if the surname in column C, also appears in column A&B.

I would like to list, in another sheet, the names that are in Column C that are NOT in A&B. Then I can see what data is missing.

Feb 14, 2010

I have a spread sheet (sheet2) that runs an add-in and does some calculations and arrives at a result which is a number in cell P6. All I have to do is type in a Stock Symbol in cell A1, and the sheet runs the add-in, calculation automatically. If I change the symbol in A1, the sheet re-runs the add-in/calculation and gives me the result for the new stock in cell P6.

Sheet 1, has a list of stocks, indexed in Column A.

Rather than me changing the symbol manually in sheet 2 cell A1, how can I automate it such that it will run the functionality automatically for each symbol in Sheet1 column A sequentially and then tabulate the results for each stock/symbol in Sheet 1 column B.

I tried to attach the sheet/book herewith, but could not. It is less than 100KB in size