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.

View 4 Replies


ADVERTISEMENT

Revenue Spread Over Months

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

Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)

Dec 23, 2008

I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...

View 4 Replies View Related

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?

View 4 Replies View Related

Not Sold Continuous Months Count

Mar 27, 2014

Find attached , expected result in on w2 needs to be 9 that I have punched manually : Team(A)01.xls‎

View 14 Replies View Related

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.

View 6 Replies View Related

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)

View 3 Replies View Related

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] .....

View 2 Replies View Related

Sum Product :: Within The Last 12 Months From Todays Date

Jun 9, 2009

I want to return a "total" result for all the cells which contain a date within the last 12 months from todays date.

View 9 Replies View Related

Lookup Manufacturing Details Depending On Product Type

Dec 15, 2009

I have created a drop down list of products that we manufacture on a tab in excel. I want to use this list so we can create production/manufacturing paperwork that can be used in our factory.

On other tabs I have created the manufacturing specifications which are a list of specifications and cutting sizes that vary depending on the 1st column /size selected. The rest of the data for follows across.

However for the three products the manufacturing specifications are different so what I want to know is can I create a formula that says if I select product A then look at product A's specification tab, if I select product B select product B's specification tab or if I choose product C then look at product C's specification tab?

I can create a vlookup formula that works for one product and drops the necessary information into a sheet I have designed but I don't know to get it to distinguish the correct cutting info depending on the product type selected.

View 13 Replies View Related

Excel 2010 :: Add Code To A Macro To Calculate Something Depending On Months

Aug 22, 2012

I am looking for something to calculate values based on the past three months. I already have written a macro to retrieve all of the data and just need this as an add-on.

for example, lets say it is 5/31/2012. I have a row with dates going like

10/31/2010 11/31/2010 .........

I want the formula to calculate SUM(march+april+may row 68)/SUM(march+april+may row 59)

I have an input tab where I can select the month, say 5, and get the periods.

3/31/2012, 4/31/2012, 5/31/2012

if I changed the 5 to an 8 it would get 6/31/2012 7/31/2012 8/31/2012

I don't know if i need VBA for this but I am trying to have something to calculate this depending on the period selected in the input tab.

I am using excel 2010. I am thinking something with an offset function and maybe a loop? but still not sure.

View 2 Replies View Related

Conditional PRODUCT Function Formula (return The Product Of A List Of Numbers In A Column)

Sep 30, 2009

I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:

Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula

The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:

{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}

This returns zero though works appropriately if I replace “PRODUCT” with a “SUM”.

View 2 Replies View Related

Dynamic Named Range For The Product Name On The Product Details Sheet

Dec 24, 2008

I'm working on a order spreadsheet system, and I have one sheet called Product Details, where the product name, list price and product code are found, these link to the Sales order page, and I need them so that they can be added to.

So far, I have created a dynamic named range for the Product name on the Product details sheet, and linked the the range via a list validation on the Sales order sheet.

The drop down list displays the products and can be added to by typing new product names on the other sheet.

What I need now is that when a product on the sales order page is selected, it draws the the list price and product code data automatically from the sheet, I tried using a normal vlookup, but I couldn't get it to work. I also need the list price and product code columns to be 'dynamic' so new values can be added further along.

View 5 Replies View Related

Macro For Name Generation From Product To Product With Sizes And Filenames

Jan 30, 2014

I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.

View 4 Replies View Related

Equally Divide Value From One Cell Into Array

Jan 11, 2012

I am trying to do:

Say value in A2 and A3 is 4.5 and 5 respectively
and the value in A1 is and 1 respectively
B2 and B3 have zeros.

Case 1 :
Now I would like to have value A1 in C2:AZ2 till the value in A2 gets exhausted.

Hence C2 = 1, D2 = 1, E2 = 1, F2 = 1, G2 = 0.5, H2 = 0, I2 = 0 ... and so on

Case 2 :
Values in C3:AZ4 should be A1 till A3 gets exhausted. But the constraint is the values in the above range should start from/next cell when we left CASE1

Hence C3 = 0, D3 = 0, E3 = 0, F3 = 0, G3 = 0.5, H3 = 1, I3 = 1, J3 = 1, K3 = 1, L3 = 0.5 ...

I am using the below formula -

For CASE-1 : C2 is
=IF($A$2>0,IF(SUM($B$2:B2)$A$2,$A$1,0),0)

D2 is
=IF($A$2>0,IF(SUM($B$2:C2)$A$2,$A$1,0),0)

and so on...

But this works only if the numbers are whole numbers, I tried a lot but I could not get a formula which works with integers. The above formula will fail if the number is say 4.5 -having decimal part.

For CASE-2 : C3 is =IF($A$3>0,IF(COUNTIF(C$2,1)>0,0,IF(SUM($B$3:B3)$A$3,$A$1,0)),0)

D3 is
=IF($A$3>0,IF(COUNTIF(D$2,1)>0,0,IF(SUM($B$3:C3)$A$3,$A$1,0)),0)

When the numbers in A2 and A3 are decimals and not the whole numbers.

View 9 Replies View Related

Pick Random Numbers Equally

Jan 25, 2012

I need VBA code for picking up six random numbers from 1 - 30

5 times and put them in the columns from A1 :E 6

in A1 :A6 six random numbers
in b1:b6 another 6 random numbers ;

Without any duplication in each row or in each column .

View 5 Replies View Related

Ividing Sales Leads By Zipcode Equally

Apr 15, 2006

The data is a column of zipcodes, a column of timezones for each zipcode, along with a column of this past years sales leads, showing a count of the number of leads from each zipcode. I want to assign territories to 10 sales agents based on an equal division of the number of leads in each time zone; that is, based on last years leads each agent would be assigned zipcodes in each time zone so that all agents would end up with the same number of leads in each time zone.

View 3 Replies View Related

Product Look Up: Look Up A Product Based On The Number And The Qty

Feb 20, 2009

i need to look up a product based on the number and the qty. see the example attached.

On tab "Printer - S" 1 need to lokup the costs based on the product code 8 and the qty 100. How do i do this and make it adjustable for any range of priduct codes and qtys. Again, becuase this will be used on a Mac Office, i cannot use macros.

View 4 Replies View Related

Type The Product Id In The A Column Of The Orders Sheet(Sheet2) To Auto Insert The Product Name In The B Column

Jul 16, 2006

In the first sheet I have two columns, one for the product_id and one for the name of the product. So the Sheet1 is like a small database. The second sheet is for the orders.What I want is when I type the product id in the A column of the orders sheet(Sheet2) to auto insert the product name in the B column so i dont have to write it every time.

View 2 Replies View Related

SUM Of Clumps Of Different Items Sold

Jun 11, 2009

I have a 7000 row spreadsheet of items sold over the past 6 months. I sorted them by their SKU, but some of them sold in multiple qty in a given day.

What is a formula to calculate the total qty of each SKU sold?

Example:
Column A = SKU 001
Column B = Qty Sold in a particular purchase (most have 1, but some are 2 or 3 or 4, etc.)
Column C, (Row 10) = Total where I would like to come up with a number for columns 1-10 (for example).

next item:
Column A = SKU 002
Column B = Qty Sold in a particular purchase (most have 1, but some are 2 or 3 or 4, etc.)
Column C, (Row 17) = Total where I would like to come up with a number for columns 11-17 (for example).

View 8 Replies View Related

Counting Products Sold Per Rep

Aug 27, 2008

I'm trying to write a formula to count under certain conditions. I tried the following (and some other variations), but it doesn't work, it comes back #VALUE!:

=COUNTIFS('[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$K$10:$K$100,D7,'[Compliance 20080804 Friday.xlsx]Compliance and Interview Log'!$A:$A,A34)

I need to count the number of products a rep sold in one day. He can have several sales in one day, and also sell more than one product per sale. The list I am pulling from has all sales from all reps for the day.

View 10 Replies View Related

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

View 6 Replies View Related

Cell Referencing: Enter A Product Name In One Cell And Have The Price For That Product Automatically Appear In Another Cell

Feb 3, 2009

I'm trying to create a supply order form. Is it possible to enter a product name in one cell and have the price for that product automatically appear in another cell? Is there a formula that I can use to make this happen?

View 2 Replies View Related

Conditional Summing (% Of The First 5 Units Sold Are New)

May 8, 2008

I need a formula that will tell me what % of the first 5 units sold are new, based on the dates provided. This is an example of the data I am working with. What i am looking for is a cell that will respond with .4 (meaning 40% of the first 5 units are new) ...

View 9 Replies View Related

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.

View 3 Replies View Related

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.

View 9 Replies View Related

Find Out How Many Of Part # 123456 Have Been Sold All Year

Mar 9, 2009

I have a one year sheet for a customer. Column A has a 6 digit part number, Column B has the quantity of that part number and Column C has the date they of the invoice. There could be several rows with the same part number (say, if they purchase them monthly, or weekly they would have 12 or 52 rows, etc.). If I want to find out how many of part # 123456 have been sold all year, is there a formula I could use in Column D?

View 8 Replies View Related

Calculating Of Total Number Of Tickets Sold?

Oct 7, 2013

I have created a spreadsheet as follows:

Date revenue total tickets sold total tickets sold per day
10-1 166,453 15374
10-2 166,915 15414

How do I formulate so that the numbers automatically update per day

View 2 Replies View Related

Maximum Number For Apple Sold On Monday

Dec 4, 2011

I have a table and I want to find out the what is max number for Apple sold on Monday.

FruitsQtyDaysApple20SaturdayApple50SundayApple100MondayApple500MondayApple20
MondayMango50MondayBanana640FridayMango30FridayGet MAX ForApples on Monday500

View 9 Replies View Related

Count Sale Days Between Sold Articles

Jan 29, 2009

In table A i have the purchase date of all the articles bought with their corresponding quantities.

Table A:
Purches DateArticleQuantity12/12/2008Radio2012/12/2008Binocular6012/12/2008Fan3112/12/2008Iron5012/12/2008Sun Glass15012/12/2008Tv612/12/2008Head Phone3712/12/20082G Card12


In table B i have the sold dates of all the articles with their corresponding quantities.

Table B:
Sold On ArticleQuantity15/12/2008Iron115/12/20082G Card115/12/2008Radio216/12/2008Tv116/12/2008Iron217/01/2008Head Phone317/12/2008Iron117/12/20082G Card122/12/2008Radio127/12/2008Fan1

An finally table C is a result chart which shows the days between the purchase and the selling dates of the articles.

Table C:
Article Sale After purches date 12/12/2008 Days ?Sale RepeatRadioBinocularFanIronSun GlassTvHead Phone2G CardAfter Every ? Days3163453After Every ? Days1145After Every ? Days5After Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? DaysAfter Every ? Days

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved