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
ADVERTISEMENT
Aug 21, 2012
Excel formula? What I would like to get is a period number for financially month - not sure if it is possible
For example: 29, 30 & 31 of July suppose to be period 8.
View 3 Replies
View Related
Aug 14, 2012
I have a range of dates in c1:c285. I have a range of liters sold on those days in e1:e285. I want to total the liters sold per month for the 2 year period.
I was trying to use =SUM(IF(MONTH(c1:c285)=1,e1:e18,0))
However this just returns a "value" comment. I think even if this worked it would add the months together for the 2 years so both June figures would be returned as one figure.
View 7 Replies
View Related
Jun 28, 2014
I have a spreadsheet that consists of date, product number with amount received against each date.
What I need are formulas that will automatically provide me with:
1. The sub-total monthly income for each product against each financial year.
2. Total income for each product by financial year.
3. Total income for each financial year.
View 11 Replies
View Related
Feb 13, 2010
This is for a report and on "Summary Worksheet" I want to post "Current Payment" totals IF the invoices from "Tab 3" equal the "month" in G6. Say the report is for January - if there are invoices on Tab 3 -worksheet with a January date I want to post all invoice amounts on Summary worksheet under current payment.
View 4 Replies
View Related
Mar 20, 2009
I have log data in two columns:
Column A: Date/time (at 30 minute intervals)
Column B: Numeric data
On the last row of each month, I’m trying to perform a SumProduct on the two columns and display that result in column C.
The end of the range is determined by the month in the current row.
I’m having difficulty finding the beginning of the range, though. I need to account for both the normal dynamic calendar days & the fact that I may get data starting mid-day and mid-month.
I have this formula, but I’m not sure how to make the first array dynamic or if this is even correct approach.
Manual
=IF(OR(MONTH(A1009)=A4)*(A$4:A$65536
View 9 Replies
View Related
Mar 27, 2014
I am writing a formula that will sum data for a 12 month period that is not based on a calendar year. For example may have data starting in June 2011 running to date, but not every month. If I set a start date for Year 1, say April 2012, I need to see Year 0 numbers (anything prior to April 2012), Year 1 numbers (April 2012 to March 2013), Year 2 numbers (April 2013 to March 2014), and so on. Date format is currently mmm-yy.
If this could be performed via a macro which references the database (spreadsheet), then even better. The database will contain multiple columns, of which two will contain the date and corresponding number respectively.
View 12 Replies
View Related
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
View Related
Mar 5, 2007
I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.
=IF((C26-B26)/B26
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
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
Nov 19, 2009
If today = any date in November, how can I display in a seperate cell that that is month 8 of an April - Mar Financial year.
A4 =Today() 18/11/09
a5 = can this display as 8 and then 9 when a4 becomes a december date?
View 3 Replies
View Related
Dec 9, 2006
I am currently tracking our company's service tasks in Excel and would like a formula or method that will take the total hours a task has been approved for and equally distribute the hours into columns under each month.
Column A = Resource
Column B = Task Owner
Column C = Task Name
Column D = Task Description
Column E = Approved Hours
Column F = Start Date
Column G = End Date
Column H = Jan 07
Column I = Feb 07
Column J = March 07 etc. etc.
I basically want to take the approved hours (Column E) and have those hours automatically calculated and filled into the month columns (Column H forward), as per the start and end date fields (Column F & G) so the hours are evenly distributed over each month.
View 9 Replies
View Related
Aug 31, 2007
i'm trying to find a way to distribute days per month between 2 dates. I have found a great exemple that should to de trick but there is still a problem left in it. it gives in the next year (this case '08) an +31 value and a negative value
View 4 Replies
View Related
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).
View 2 Replies
View Related
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?
View 2 Replies
View Related
Jan 28, 2010
I have a spreadsheet that is now a yeare old with 5000 rows and is now going into the 2nd year
Column A is for date input and the same date can be repeated several tumes :-
1 Jan 09
1 Jan 09
1 Jan 09
1 Jan 09
2 Jan 09
2 Jan 09
3 Jan 09
3 Jan 09
3 Jan 09
Sometimes there are all 30 /31 days but normally not .
I need to find the last ocurance of the last date used for each month and then use the cell number to calculate the column totals for that month.
View 9 Replies
View Related
Dec 25, 2013
Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.
View 10 Replies
View Related
Jul 10, 2014
I have created a time sheet in excel (see attached) that will be part of the larger workbook that will be linked with other sheets to auto fill in most fields. I am wondering if there is a way for an user to enter a Month and a Year at the top of the page and that in turn automatically fills in the days of the month by week.
So in attached sheet there are 5 boxes representing 5 weeks in a month. So if we used May 2014 as an example I would like to know if there is a way that once May 2014 is entered in up to top that. Excel fills in the dates in Week #1 with under Thursday showing 1st, under Friday showing 2nd as on for the entire month...
So as the month go by all user has to do is state the month and year and excel fills in the weekly dates for each day in month.
Attached File : Time and Attendance.xlsx‎
View 2 Replies
View Related
Apr 4, 2013
I am working in a spreadsheet that contains a bunch of data, it is not limited, and varies. It has some fiels such as: name, date of birth, address, and others. I am interested in creating a column with only the month digit of the date of birth for each row.
I have been working with some code, I am not quite sure how to continue. The following table would be a example that I have of the data, it has only to entry in the column of Date of Birth, but my data range will always vary. I want to get the month and past it in the next cell that is available in this case would be column c or 3... The worksheet name is REP.
VB:
Dim Cell As Range
' 1st cell with the posting date
Set Cell = Range("A2")
Do While Not IsEmpty(Cell)
If Cell = "Date" Then
[Code] .....
View 5 Replies
View Related
Dec 1, 2012
In a sheet I enter the following:
... in A1 a year (say 2012)
... in A2 a month, formatting as "MMM" (JAN, FEB, MAR etc.)
How to automatically get in column A (say from A3) all the dates of the month entered, formatting as "D/M/YYYY" (e.g. 1/1/2012, 2/1/2012/ 3/1/2012, etc.)?
View 3 Replies
View Related
May 1, 2006
I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.
=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday
to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
View 11 Replies
View Related
Jul 1, 2009
I have a spreadsheet that has columns of monthly values for three years of financial data and where the values for the latest month are added to the last column. Months that have not been completed will have a zero value (e.g. Jul-09).
Jan-09
Feb-09
Mar-09
Apr-09........
View 9 Replies
View Related
Jan 21, 2010
I have a spreadsheet for monthly supplies. In row 1 is Jan – Dec and in the row 2 below are empty cells where there will be a total for that month’s purchases. I want a conditional format formula to automatically bold and highlight the current month’s total and month name.
Also, when I enter February totals next month and that number is input into February’s total, I want that month and total to bold and highlight BUT I also want the previous month’s bold and highlight to vanish at the same time. Is this possible?
View 9 Replies
View Related
Jul 19, 2006
Is there a way to make the attached worksheet automatically shade out all the Saturdays & Sundays in any given month everytime you change the Month/Year cell at the top of the worksheet, as example? I've tried using the weekday/Weekend formula, but can't quite get it right.
View 2 Replies
View Related
Feb 16, 2012
I am trying to add a dynamic date rang to the heading of a table. I can find the earliest and latest dates, and want to concatenate them in the heading...
January 1 2012 to January 31 2012
The day and year (and the other bits) are easy, but is there an easy way to convert 1 to January, without going through a vlookup.
View 14 Replies
View Related
Feb 4, 2009
I have a database that I export to excel every month. The export process is built in the database software (ACT!2009). The export opens Excel with the standard Book1.xls file name. All the field columns will be the same every month.
Goal:
I need to format the spreadsheet to make it more readable and have been assigned the task of:
1 - Inserting a blank row between each row that contains data and filling in with color.
2 - Resizing the blank row to make it look like a "thick" border.
3 - Auto adjusting the columns to correct size.
4 - The last column contains comments and needs to be wrapped text.
5 - All of this needs to fit on 1 sheet (landscape).
Issues:
1 - Each month there will be a different number of rows.
2 - I know I can create a macro to do this but the macro that I would be creating will be in a saved template or spreadsheet. How could I use a that recorded macro in a spreadsheet that is called Book1.xls?
I have attached 2 spreadsheets. One called Book1.xls which is the raw data after exported and the 2nd spreadsheet called Formatted which is the end result that I am looking for.
View 2 Replies
View Related
Oct 24, 2011
My data sheet has a Month column, and those months are simply numbers one to twelve. They are formatted as plain old numbers. The data is coming from somewhere outside Excel.
How the numbers look in the data sheet doesn't matter. But I use this data to generate pivots, wherein the months are the columns. I want it to say Jan, Feb, etc. across the top of the pivot.
I realize that I could just loop through the raw data and convert the numbers to text. However, I want the pivot table to recognize this data as dates, so that I can do date-related stuff, such as the date grouping described on p. 85 of Jelen's Pivot Table Data Crunching book.
If I go into the raw data and try to convert the cells to the mmm format, they all convert to January.
I'm aware that formatting doesn't translate from raw data to pivot table anyhow. But it's not clear to me how I can use the date field grouping functions without somehow showing Excel that these are dates. Or is the pivot table smart enough to realize that just by the names, even if they are formatted as text?
View 6 Replies
View Related
Jul 28, 2008
I have a range of dates from 2003 to 2012. I formatted them to the 'Mar-01' option, but when I want to pivot on the month, Excel still reads them as the date - example 3/25/2008, 3/28/2008...and so my pivot table has multiple columns for all of the dates present in that month.
How do I truly format my dates so that excel reads them as the month only so that I can then pivot and show 12 columns (months) per year?
View 9 Replies
View Related
Jan 29, 2010
I'm after a formula this time ... i've searched the board and can't find what i need.
a cell shows 2009 December
and i'd like a formula to covert this to 31st December 2009 .... i.e. for any cell i'd like to know last day of month... and month and year ..
View 9 Replies
View Related
