Monthly To Quarterly Cash Flows
May 13, 2014I am building a Discounted Cash Flow.
I have monthly cash flows rolling for 10 years, which I need to sum up into quarterly cash flows.
ADVERTISEMENT
Offset Function For Cash Flows In Different Years
Sep 5, 2007I have got three period like this
Period: 2010 2015 2018
Cash: 100 200 300
I would like to use a function (i am thinking offset will do the work) to put these cashflows on a timeline i.e. 2010,2011,2012 etc...
The three periods described above will be changeable, so the cashflows will adjust accordingly...
Payback & Discounted Formulas For Uneven Cash Flows
Nov 7, 2006formula in excel to calculate payback and discounted payback periods with uneven cash flows.
EX.
Yr CF
0 ($2,000)
1 160
2 200
3 350
4 395
5 432
6 440
7 442
8 444
Quarterly Data To Monthly
Jan 27, 2014I am trying to convert quarterly data to monthly
The quarterly data columns go: Mar-14, Jun-14 etc
The monthly data columns goes Jan-14, Feb-14 etc
I am trying to use a vlookup and match formula but as you will see in the attached file it is only working for those months that are labelled in both data e.g. March 14, June 14
Is there a formula that will pick up for example that January and February numbers should be drawn from the March 14 quarter, April and May from June quarter etc?
Monthly And Quarterly Averages From Another Sheet
Apr 15, 2014I need a formula that will provide monthly and quaterly averages from another worksheet, but only include the months that have occurred. I have the formula to obtain both the averages and quarter totals, but it is counting all months w/in the quarterly range before the months have occurred.
View 5 Replies View RelatedManipulate Date Ranges For Monthly And Quarterly Reports
Feb 4, 2009I am currently trying to manipulate date ranges for monthly and quarterly reports, and am having trouble doing this. I have attached a file with an example of what I would essentially need.
I would need the "Nbr of Projects", "Nbr of Days worked", "Nbr of International Projects" and "Nbr of Local Projects" cells filled in under each respective report, based on the data in the top left.
If a project falls in two months, such as "18.12.2008 to 15.01.2009", this would be treated as 1 project for December and 1 project for January. Also, I would need only "NETWORKDAYS" included in the solution, so 11 days in this example.
How To Add 3 Horizontal Monthly Values And Create Quarterly Values
Oct 24, 2013I have a data series running horizontally, which gives me monthly values e.g.
Jan 10 - Feb 12 - Mar 11 etc. Imagine the months are in row 2 beginning from column 2, and the values are in column 3.
I would like a formula which adds the three values, and in row 6 column two gives me the total for the quarter (10 + 12 + 11) and displays 33.
The next quarter value, I would like in row 6 column 3, etc.
Convert Quarterly Cashflow Into Monthly Cashflow
Sep 10, 2012I was wondering if there is an easy way to convert a cashflow present quarterly into a cashflow presented monthly? I would like to divide the quarterly figure by 3 and insert each third into one month on the monthly cashflow.
In Cell H2 I have 01-Apr-12 and in cell H870 I have 30,000. I would like this to go into cells H900, H901 and H902 as 10,000 in each. This process could then be copied along.
Producing Table Of Monthly Values Based On Monthly Growth Rate And Yearly Total
Mar 6, 2013I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
Link for excel file is here: [URL] ...........
Pulling Data Into Two Columns Labeled “Monthly” & “Non-Monthly”
Aug 3, 2009I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”
The Monthly data is obtained using the following formula:....
How To Create Cash Up Workbook
Apr 15, 2014I am new to macros on excel but I am trying to create a cash up workbook. This is what I want it to do:
First Sheet should be a summary of the month with each day. From there I want it to generate 4 worksheets for every day's cash up. (which should be Daily Cash up, Credit Card Sales, Cheque Sales & Account Sales).
The Daily Cash up Sheet must pull info from the other 3 and must allow me to input cash values manually. But the cc sales, cheques sales and account sales must be pulled from the other sheets.
After completion of cash up there should be a button on the Daily cash up sheet to say Finalise, where when it is pressed it summarises all totals on the first sheet. So when I open the workbook every morning, there should be a button on the first sheet to create a new 'cash up'.
Then there should also be a button on the first worksheet for month end, so that when you press month end it finalises the month and generates a new work book for the next month.
Petty Cash Spreadsheet
Jan 4, 2009i am trying to set up a spreadsheet for petty cash so will have a description and an amount on the left of screen and numerous columns on the right for the transactions to be split between ...... Is there any way for me to tell it that if A2=fuel truck then the amount in column B2 needs to also be in column G2?
View 9 Replies View RelatedBuilding A Cash Register
Mar 6, 2007I want to build a cash register from excel.
I want to put many buttons, but lets say one on A1 that says $2 dollar item and name and lets say this $2 doillar item button is pushed(clicked on), it will add $2 to lets say the total say A15.
Now I can't even get a Macro to add $2 dollars to A15(or any macros to run at all, anybody have code I can copy, what I understand is you create macro name(give it letter, so yo can press letter and Ctrl to start) then start recording, then you do key strokes or mouse? Then press stop recording and you have macro?).
To get buttons to be viewed I have to go to Tools - Macros - script editor, and drag and drop(button on coding) or double click on button tool bar then File - View in Browser - . I get button but can't get it to do anything.
How do you put say A1 in code to A1=A1+5 or make a memory location out of A1. Now you can go to(while A1 is selected cell) "Insert" then "Name" then "Define" and give A1 a "Name". I can't get this name to work in coding(but I can get the name to work in cells like "Name" +5, will add cell 1 and 5, if you do it in different cell than A1).
Break Even Calculations In Cash Flow Row
Jan 20, 2014I have a row which shows date by month and then a row which shows cash flow. I would like a formula to calculate the break even point in the cash flow row and return the month which this occurs. I would also like to show the number of units required to break even.
View 2 Replies View RelatedSplit Amount Into Different Cash Denomination
Mar 25, 2011The amount is $1256,
Cash Denomination will be $500, $100, $50,$10,$5 and $1. I need to split the amount into the number of pieces of each domination to present to the bank when i need to withdraw salary for the workshop workers.
500100501051
1256221011
Discounted Cash Flow Worksheet?
Jan 26, 2013I am looking for a discounted cash flow worksheet where I can set the period of time to a certain number of years, set the terminal value at the end of that period of time, as well as having the ability to set the discount rate. When all three things are set, I would like to see the annual cash flows as well as the present value amount.
View 1 Replies View RelatedDebug Selection & Cash Register
Jun 7, 2009I'm working on a sheet where I can specify sales by selection of month (Sheetname= ZoekenMaand).
Everything works fine for the months "januari" and "februari" (yes, it's in dutch ) but from "maart" it doesn't seem to work any more.
VBA Code To Open A Cash Drawer!!!
Feb 16, 2010I have Created an Excel Spreadsheet to work as a EPOS system. The only Problem i am having finishing it off is getting the cash Drawer to open at the end of a sale.
I have my Cash Drawer connected via a Rj 45 in the Back of my Receipt Printer. The Printer i have is a Star Tsp-600. I have Found out that the Open Drawer Codes are 7 or 27,7,10,50,7. I am not entirly sure what they mean though. The code that i have at the moment doesnt work it brings an error "53" file not found. This is my code
Filter For CASH NOMS And Keep Everything Thats In The Criteria
Sep 24, 2009i have rec data in which I need a macro that will filter for CASH NOMS and keep everything thats in the criteria but delete everthing else in cash Noms
criteria is named range "Criteria" (I5:K10)
So from this
Sheet1 ABCDEFGHIJKL4 Criteria 5RecTeamAmountRef RecTeamRef 6CASH NOMSAMP1132.36CAETRN CASH NOMS*AMP*.AIMS 7CASH NOMSAMP215.36.AIMS546.3 *WTRN96 8CASH NOMSINTEREST915.12WTRN96 *AMPTRH 9CASH NOMSFA5-65.36AMPTRH *.WSTFGH 10CASH NOMSFA6-9.01CBWSSG *WTWHAT 11CASH NOMSCA-9.01CBWSSG 12CASH NOMSDES-9.01MSFAST 13CASH LIFEUUTU-9.01CBWSSG 14CASH LIFEAMP1-9.01CBWSSG 15CASH LIFEAMP232.12.WSTFGH 16CASH LIFEAMP315975.36CBWSSG 17CASH LIFEAMP4-9.01OPTMOD 18CASH NOMSFA1-9.01CASH WTWHAT Excel tables to the web >> Excel Jeanie HTML 4 .........................
Cash Flow Distribution Modeling
Nov 24, 2004I am trying to model the distribution of cash flows from a real estate investment. This is a bit complicated, but basically A1 through A37 are a series of cash flows after payment of debt service from month 0 (closing) to month 36. The real estate project has two investors, the Institutional Investor and the Developer. In this case, the Institutional Investor contributes 90% of the required project equity and the developer contributes the remaining 10%. The cash flow is distributed according to a set of parameters specifying cash flow percentage allocations and IRR targets, collectively known as a "cash flow waterfall"." As with all for-sale residential development deals, the early cash flows are negative and then turn positive as unit sales occur. The order of distribution of the cash flow remaining after debt service is paid is as follows (the waterfall):
1. Both investors receive the return of their invested capital from cash flow distributions.
1. Hurdle 1 (after the return of equity to both investors), the Institutional Investor receives 90% of the monthly cash flows and the Developer 10%, proportional to their respective overall equity contributions, until both investors achieve a 12% IRR (all IRR's to be calculated from deal inception/first dollar invested).
2. Hurdle 2, (after Hurdle 1 is satisfied), the Institutional Investor receives 60% of the monthly cash flows until the Institutional Investor achieves an 18% IRR.
3. The Final Split (after satisfying Hurdle 2) allocates monthly cash flow 50% to each investor.
On an overall project basis, the Developer should realize a disproportionate share of the cash flow after the second hurdle is hit. This is what is known as the "promote." The most recent 36 month (37 including Closing) projected cash flows are as follows:
($1,315,567) - Closing
($113,697) - Month 1
($178,397)
($168,667)
($150,365)
($36,506)
$16,662
$120,283
$107,443
$107,443
I realize this is complicated, but this structure is quite common to real estate, private equity, venture capital and hedge fund investments (along with most any joint venture deals with multiple classes of equity participants). The canned software that is out there that performs this type of anlysis is both expensive and rigid, and doesn't typically enable the required sensitivity analysis in structuring a deal.
Automate Quarterly Calculation
Jul 29, 2009how do i get excel to automate quarterly calculation? in column A1 I have Year and column B1 i have months (1-12) i have data in column D1 - H124 - I would like to run a macro where it will pick up Jan, Feb, march (1,2,3) for each year in column A and do a quarterly average right now i manually use =(D35*31+D36*28+D37*31)/90 but i would like to avoid human errors can i get excel to read if column B is 1,2, and 3 run quarterly average? and would excel know that in Feb there is only 28days except leap years?
Cash Register With Keyboard Numerical Keypad
Aug 22, 2007With reference to Cash Register example that I downloaded from Roy Cox; is it possible to link the numerical keypad that is created in the file to the PC Keypad.
I realise I would need to use keypress functions but am unaware of the ascii for these keys. Also; how do I limit that the keypad writes it to the label? Otherwise, would I need to create a new userform instead of what was created by Roy.
Combine Quarterly Bars Get Messed Up?
Feb 5, 2014Each quarter should have the bars standing next to each other, i.e. Q1 2013 should stand right beside Q1 2012. Between these pairs of quarter there should be a small space and the the next pair, Q2 2013 and Q2 2012, should be. So how do I do this?
When I use data without stacking several info in the same bar, this isnt a problem but when I have this "aaa, bbb,ccc,ddd" in every bar, this gets messed up.
Quarterly Interest Calculating Formula
Oct 21, 2009I use Excel 2007 , I created Interest calculator , on Daily basis , to caluculate interest , compounded quarterly.
But I want to make it compact , as d one I created is long enough.
A3 = Principal Amount
B3 = Date of Investment
C3 = Interest as on Date
D3 = Number of Days , amount Invested {comes out of formula set}
E3 = Rate of Interest
Now in F3 I want the Interest amount , compunded quarterly.
Some times NUMBER OF INVESTED CAN BE LESS THAN 90 DAYS TOO...then what ?
Quarterly Goal Pacing Calculation
Dec 8, 2012I am very new to excel and my formulas dont add up.
I need to calculate based on my quarterly goal and my quarter to date actual sales what my percentage pacing is.
Now the way i did it is =B5/A5
Do i need to add the Quarter date calculations in there because its not giving me the accurate percentage.
Goal Actual Pacing
Projection Spread Sheet For Cash Flow Purposes
Jun 2, 2009I am trying to put together a projection spread sheet for a client for cash flow purposes. I have it laid out but I cannot wrap my head around the formula and I've tried a few. Here it is.
A1: Client Name B1: Invoice Date C1: Projected Due Date ( I have 45 days added to B1) D1: Invoice Amount E1: Jan F1: Feb G1: March H1: April I1: May J1: Juneetc...
I am trying to forcast out the invoice collection date (C1). So basically in the month of June column I am trying to say if C1 falls between June 1 & June 30th then it should equal D1 (the amount due in that month). So if the invoice has a due date (C1) that falls in that particular month than I am forecasting the cash flow in the month it's collected.
Summarize Quarterly Data In The Summary Table?
Aug 7, 2014summarize quarterly data in the summary table taking into consideration the months and the district. see the attachment for a more clear picture.
View 4 Replies View RelatedLookup Quarterly Periods Against Multiple Dates?
Feb 26, 2014My lookup formula is not hitting the correct period. How can this be adjusted without changing any of the "GRAY" data.
View 2 Replies View RelatedCalculating Quarterly Averages From Weekly Data?
Jan 20, 2014generating a formula that takes the weekly values in a quarter and uses them to generate a quarterly average. Quarters are split up as follows.
Q#1: 09-11 (First week of September-last week of November)
(Used to generate price for January)
Q#2: 12-02 (First week-Last week)
Q#3: 03-05 (First week-Last week)
Q#4: 06-08 (First week-Last week)
I've attached an excel sheet with some dummy data.
Calculating Quarterly And Yearly Totals From Dates?
Jan 5, 2012I have a column that contains dates for an event. I would like to tally quarterly and yearly totals for these dates. What formula can I use to accomplish this?
View 1 Replies View Related