Analysis Tables For Modeling
May 1, 2007
I have an excel question on how to create a table that will be able to compare actual results with our estimated results.
Basically, the column I have in blue in the attached excel file contains our estimates. Once the actual numbers come out, we will replace those numbers with actual numbers.
I would like to create a table that will automatically show the magnitude of the impact of the actual numbers vs our results -- basically how much each item added or subtracted from EPS.
The items I would like to show the impact of are:
(1) The tax rate -- how much the actual tax rate impacted EPS vs. our estimate. For example - if it was a lower tax rate there may be a $0.03 benefit.
(2) interest expense line - more/less interest..what the impact of that is.
(3) non-recurring items
(4) Share count - a lower share count or a higher share count than we anticipated - what the effect on EPS is.
For each item though, every other item should be held constant. So if looking at tax rate -- then use the actual interest expense and the actual shares.. and if looking at shares...compare that to our estimate but use the actual tax rate reported and the actual interest expense....
There should then be a sum total of these items +/- impact to EPS.
View 9 Replies
ADVERTISEMENT
Mar 20, 2009
I have created a Pivot table as below:
Milk 1 Pint Whole
JAN FEB MARCH APRIL etc
Shop 1 10 12 14 20
Shop 2 8 7 6 2
Shop 3 10 10 10 10
Shop 4 20 30 40 50
Although i can manually look at the sales figures and work out if they are buying less or more products i would like a a more automatic way of finding out if customers sales are going down on the up or staying constant. Does anymore have any ideas on how best to acheive this.
View 7 Replies
View Related
May 24, 2014
I need to create some trend analysis for Vehicle sales. I have attached a file with sample data.
There are few problems i am facing like description has all the vehicle details which are not in any particular order, even if some how i manage to separate these then too many variables are not letting me come to any conclusion for example make, model, mileage, year of registration color of vehicle, auction sold, sale price, cap (bench mark for pricing).
what i need to produce is which auction sells certain vehicles best or which color of certain model sells best.
[URL]
View 1 Replies
View Related
Nov 24, 2004
I 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.
View 9 Replies
View Related
Mar 14, 2008
In my efforts to improve worksheet readability across the board at my work I am researching spreadsheet standards. I have found the Spreadsheet standards review board http://www.ssrb.org/index.html that has a massive document containing valuable standards to adhere to for financial modeling. Since I learn best by example, I am wondering what standards any of you guys are using and if possible post a sample spreadsheet containing those standards.
View 3 Replies
View Related
Nov 22, 2006
in excel, is there a place called data analysis under tools? im an exchange student in sweden andthe work that teacher gave me is half swedish and half english.. im looking for histogram there.
View 3 Replies
View Related
Jul 1, 2013
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
View 2 Replies
View Related
Aug 7, 2009
I need to write a program that will take numbers from various sheets, do a function on them (specifically a statistical analysis), and print that number to a cell on one sheet. I've tried to have all of the numbers go to one cell and have the analysis done on them, but after so many sheets, I get a memory error. I don't necessarily need all of the numbers to be saved, just the result is what I need.
View 14 Replies
View Related
May 11, 2009
how to tackle a piece of work that I really don't want to do.
We have extracted some summary information on one of our products. The workbook has 10 sheets, one for each of our 10 main sales channels. Each sheet has the same layout of information on. There are 7 tables on each sheet, and each one cuts the data in a different way (eg one summarises by age of purchaser (in age bands), another by demographic group etc). Each table then has the same 14 columns of key data (eg number of sales, average order value).
I've been asked to analyse this information "for anything interesting". At the moment, the only way I can think to do this is to print out the 10 sheets, sit down with a highlighter, and try to visually identify trends and anomalies. The idea fills me with dread, and I suspect will be quite inefficient.
I've asked whether I can get the data in a pivot table or some other format that might be more conducive to analysis, and have been told no. (There will easily be more than 100,000 lines in the original data (we're using Excel 2003)). To be honest, I'm not sure that I'd really be that much better off, even if it were in a pivot table.
View 9 Replies
View Related
Aug 20, 2009
to figure out a breakeven analysis.
Here is the situation. I bought 100 shares of stock at $40 each for total investment of $4000. The stock price is now $26 a share so the investment is down $1,400. I want to figure out the number of additional shares I need to buy at $26 so that when the price goes to $26.01 i turn a profit.
View 9 Replies
View Related
Dec 17, 2012
My boss wants me to take the holiday info from SAGE for 80 employees and create a record on excel. He wants to know what holidays each employee has taken and is due to take throughout the year. As each employee works a different amount of hours and a different shift pattern, SAGE records their holiday entitlement in hours rather than days. I have attached an example of one employees details and if come up with an excel document containing similar information for 80 individuals - all starting on different dates and all having a different amount of holiday entitlement. He wants to be able to look at each employees record for the year and see not only holiday data but sick days too. I don't know where to start with this - I've thought about creating a workbook with 80 pages and create a 12 month calendar for each individual with days off marked on it?
View 4 Replies
View Related
Aug 13, 2008
What's the technique for opening a .pdf file and copying its contents into Excel for subsequent analysis by VBA?
On my Mac version of Excel there doesn't seem a facility to import it or even copy and paste it. Is this something that's available in Windows versions of XL?
View 10 Replies
View Related
Feb 10, 2012
I need to summarise a batch of data in this format....
YearPeriodAccountCustomerPart NumberSalespersonChannelValue2011Jan1SteveA1KylieHome1502011Feb2BillA2KylieExport1802011
Mar3FredA3KylieHome2002011Apr4JohnA4KylieExport1002011May5AndyA5KylieHome85
And need to put it in the following format...
Salesperson - KylieJanFebMarAprMaySteve150Bill180Fred200John100Andy85
I know I can use a series of pivot tables but would like to keep the size of the file to a minimum.
Is a DSum the way to go or can I use a Sum if and use an and function with the sum if?
formula if so to saveme a bucketload of trial and error attempts...
View 2 Replies
View Related
Sep 25, 2012
I have a problem that I am finding impossible to find a solution to myself. I want to take the maximum value on a graph (in a column) that has the 2 values next to it (above and below) within 5% of that value. If they are not within 5% I want it to look for the second largest value with the same conditions and so on until the conditions are met.
If it makes it simpler, the final point can be ignored as there are only values above it in the column.
It is in column H, which goes from H3:H51
View 5 Replies
View Related
Feb 23, 2007
I found this code in the posts to automatically install the Analysis ToolPak at Open.
Private Sub Workbook_Open()
AddIns("Analysis ToolPak").Installed = True
End Sub
I tried this in the workbook module but it doesn't seem to work. Is this code correct or have I done something incorrectly?
Also, are there any drawbacks to an automatic install of an add in like this and if it runs the code every time the workbook opens, is there any performance issues (maybe only opens a bit slower)?
View 9 Replies
View Related
Jul 5, 2008
I have been putting together workbook for my golf analysis.
Against each hole (1-18) I enter in one row which club I used from the tee which could be any of the following: D,3w,5w,1,2,3,4,5,6,7,8,9,w,s.
I also have a row that says whether I have hit the fiarway or not. If hit fairway = 1 if missed = 0.
What I want to be able to do is show the % of times I hit the fairway with each club. This would seem ida for the if function but I'm not quite sure how it would work because the result row is going to need to look at every hole to see if the club used was d,5w,3 etc etc and 'if' so then it will need to look at the row which says 'hit fairway or not' i.e. 1 or 0. This is where I get lost and am not sure if I do need the 'if' function or something else? Perhaps I need additional rows?
View 9 Replies
View Related
Sep 4, 2009
If I wanted to calculate weighted average of a bunch of salespeople, is there a way to not include some values, such as ones below a certain value? I'm trying to so something like, what would my weighted average be if I didn't include my bottom 2 salespeople or my top 2 salespeople.
View 9 Replies
View Related
Oct 5, 2006
I need to analyse trends between a range of cells. The idea is to display an image of a triangle pointing up if the percentage between the current month and the previous is bigger, a triangle pointing down if the percentage has decreased or a rectangle if both months are the same. I've tried to build a userform to ask the user to input the range and then analyze the data...with no luck at all.
View 9 Replies
View Related
Apr 2, 2007
I need to analyse data content in cells. For example, I need to analyse a list of post codes. I need to get the following info:
Max Length
Min Length
Data Types
Data Formats
Null Count
Most frequently used value
Unique record count
View 6 Replies
View Related
May 3, 2007
i want to run data analisys(Rank and percentile) on each row in my sheet and put the results on a new sheet
(the reuslts are 4 rows ) My problem is some how the command of the run analisysWon't run the command
kStop = . Cells(1, Columns.Count).End(xlToLeft).Column
Sheets.Add
ActiveSheet.Name = "Analisys_Results"
Sheets(" Total time data").Select
Var = 4
Var1 = 8
For ILoop = 1 To kStop
If (ILoop > 1) Then
Var = Var + 4
Var1 = Var + 4
End If.....................
View 4 Replies
View Related
Sep 29, 2007
I am trying to do some analysis on montly bank account data. To do this I need to take the bank statement information for the month and put it into a table that shows every day of the month (see attached example).
I am currently doing this manually but I'm sure there must be a smarter way of doing this.
View 3 Replies
View Related
May 8, 2014
Data Analysis . I have 7 tables gathered in one spreadsheet but in seperate sheets. Tables have the same columns but different data baceause they come from different locations. Now I would like to create pivot table to combine data from all 7 tables. I read a little and I got to know that relationships is needed between the tables but I can't create it because I don't have unique data in the table. Usually tables consist repeating names, locations, some numerical data.
For example every table consists worker's name. I create pivot table from one of 7th starting from worker's name in row field and put some data in value field. Then I add another workers from another table. I can do it but when I put in row field I have workers from second table under those from first one. And of course data are badly calculated. I would like to have all workers in row field with proper data. It means one column with all workers from all 7 tables an then some their data
I know I can copy all of them into one big table and then create pivot table but this is solution what I wanted avoid. I know it will work but these data can change from time to time and it will be very hard to maintain spreadsheet. When data will change I will have to copy manually .Is there any solution to create such a pivot table from multiple tables sources but with the same column structures?
View 1 Replies
View Related
Jul 8, 2014
For my thesis I collected data of football players. I have the data in an excel file, but it is unstructured. Due to the nature of the dataset, I don't want to sort it out by hand. The result should be that the dataset is ready for statistical analysis. I have three categories, namely; Player_Performance, Player_Profile and Transfer_History. I will discuss them now.
Player Performance
In this excel file the performance data of the player is gathered. As you can see, the player ID and the player name are a row above the performance data in different competitions. This data should be on the same row. Player ID, Name and then the performance data. How can I achieve this?
Player Profile
The player profile file is the easiest. I want to delete the double information. Some players are 2 times in the file. I want to delete every row which doesn't start with a player id. I also want to remove player id's which arent followed by player information.
Transfer History
The transfer history is the same as player performance. In the first row you will find the player id and name, and beneath are the different transfers. I want to put every transfer in a row of their own. The row should start with player id, then name and then the transfer. So it is possible that one player id will be on more rows.
In the attachment I put the files as they are and three files of how it should look like.
Player_Performance_SMALL.xlsx
Player_Performance14_EXAMPLE.xlsx
Player_Profile_EXAMPLE.xlsx
Player_Profile_SMALL.xlsx
Transfer_History_EXAMPLE.xlsx
View 1 Replies
View Related
Apr 3, 2007
Hi there, I would really appreciate the help with being able to enter a code and having Excel find a match. I have attached a simple example of what I aiming for.
View 7 Replies
View Related
Nov 23, 2009
I have to calculate the following: PREV = VND * NSF * GROWTH. But there can more than one choice for the values:
-VND: analysis of the month value; if this is empty then analysis the average; if the average is blank then returns “No data”
-NSF: analysis of the month value; if this is empty then analysis the average; if the average is blank then the value is 1
-GROWTH: analysis of the month value; if this is empty then analysis the average; if the average is blank then the value is 1
You can find in the excel file attached the formulas that are possible to exist.
View 2 Replies
View Related
Feb 11, 2010
I need to extract for each individual dive: Onset/start of dive (when the whale has completed its surface interval and begins its descent)
Rate of Descent (meters/second)
Onset/start of bottom phase (the whale has stopped descended and begins the "foraging" phase of the dive...not necessary the deepest point reached)
Number of lunges (you can see these if you plot the data. They are lunges at depth indicative of feeding behavior)
End of bottom phase (begins to ascend)
Rate of Ascent
End of Dive (surface intervals begins)
I've attached a brief sampling of the data in case you're curious.
Over the years I have identified these points through visual inspection of Excel charts. It's very easy, but extremely tedious and not at all efficient. I am interested in expediting this whole process and am more than willing to learn programming (i.e. visual basic or the like)...but only IF it is the best approach for this application.
I have attached two dives worth of data, so we can see what the data looks like. Each data set can contain from 1 to 8 hours worth of data.
View 14 Replies
View Related
Nov 20, 2009
I have student demographic data, then I have to track how each student did on each question, but each question also corresponds to a reading standard and I need to pull two pieces of info from that. How they scored on the assessment (# of answers correct), and if they missed a certain standard twice or more (There are 4 instances of StandardA in the test and they had wrong answers on 2 or more of them). I have the solution to the first part. I entered the results of each question as a 1 for correct and 2 for incorrect then did a countif and only counted the 1s. I am stumped on getting an analysis of how each student did on the indicators. Since the indicators can occur more than once, generating a pviot table doesn't work in this instance, it'll count of 1s and 2s for StandardA, StandardA2, StandardA3, etc. and not the counts of 1s and 2 for all instances of StandardA.
Example:
What I want:
For student Bob - Number of times he answered incorrectly StandardA, StandardB, StandardC, StandardD, etc.............
View 3 Replies
View Related
Mar 25, 2012
We were working on a set of stock returns using histogram option from the analysis tools. We have not been able to figure out why we have produced two different sets of frequencies over same set of data and one predefined bin range.
Only a few classes present different counts. Can this be due to two different versions of excel were used?
View 1 Replies
View Related
Oct 31, 2012
I have a spreadsheet with two columns, in column A i have text and in column B i have time format (01/01/1900 13:30:00) im trying to pull out information that if in column A says London to sum up the hours in column B is there analysis that would calculate this for me.
Area
Hours
Newcastle
37:30
Newcastle
37:30
[Code] ..........
View 9 Replies
View Related
Jul 13, 2013
Does Excel can be used to automate the preparation of the Quad Analysis?
View 2 Replies
View Related