Historical Cash Flow statement
What are the pros/cons with creating a historical CF statement using the historical balance sheet and income statements to calculate instead of plugging in the numbers directly from the financials?

What is considered best practice for historical analysis of the CF statement?

Thanks.

Re: Insurance financial modeling
[quote="Theferrariboy":t1efjuct]During the course on CapEx depreciation, the video got stuck somehow. I couldn't watch it on my computer. Could you therefore please post the formulas for: - CapEx depreciation (CF rows 41-46) - PPE & Intangibles (BS row 23) - Depreciation & Amortiz... [quote="Theferrariboy":t1efjuct]During the course on CapEx depreciation, the video got stuck somehow. I couldn't watch it on my computer. Could you therefore please post the formulas for:
- CapEx depreciation (CF rows 41-46)
- PPE & Intangibles (BS row 23)
- Depreciation & Amortization (IS row 23)
- Existing Depreciation & Amortization (IS row 54)

Thank you very much in advance![/quote:t1efjuct]

Hi,

We apologize for the technical difficulties you've been experiencing with the videos. If the problem persists, please let us know and we can work with you to troubleshoot the issue.

As for the cells you mentioned:

- For CapEx depreciation, you need to select all of the cells, and enter =[b:t1efjuct]-TRANSPOSE(G28:K28)[/b:t1efjuct] as an array (CTRL + SHIFT + ENTER). That's column D.
For row 41: In 2010 (column G), it's =[b:t1efjuct]$D41/$E$41*.5[/b:t1efjuct]
In 2011 (column H), =[b:t1efjuct]MIN($D41/$E$41,$D41-SUM($G41:G41))[/b:t1efjuct]
You can CTRL + R from here to expand out to the right through column K.

Then copy the four cells (G41 through J41) and paste them starting in H42 before reformatting them.
Then copy the three cells, staggering to the right (H42 to J42), and paste them starting in I43.
Repeat until you end with just one cell in K45.

Finally, copy the sums in row 46 over to the right.

- PPE & Intangibles on the BS for 2009 is just 0, and after doing the above, we can fill in 2010 (cell G23) as =[b:t1efjuct]F23-CF!G28-G10[/b:t1efjuct], to be copied over as usual.

- D&A on IS for 2010 will be =[b:t1efjuct]G$8*G54+CF!G46[/b:t1efjuct], to be copied over as usual.

- Existing D&A on the IS will be just 0 as an input for 2010 (G54). All the future years in this section are just copied over.

Hope that helps! We hope you are able to resolve the playback issues soon. Read More

Re: Text to Row
Assuming that you do want to flip your data into a "table", i.e. transpose a single column of data to multiple rows, where each row is a different record like a database, follow these steps. Assuming your data (as you inputted in your initial question) is all in column A and starts in A1 ... Assuming that you do want to flip your data into a "table", i.e. transpose a single column of data to multiple rows, where each row is a different record like a database, follow these steps.

Assuming your data (as you inputted in your initial question) is all in column A and starts in A1 and assuming that Operator, Address and Phone are repeated with no extraneous rows (i.e. 3 rows per record):

STEP 1: Remove the titles (Operator, Address and Phone)
Go to B1 and type in: =RIGHT(A1,LEN(A1)-FIND(": ",A1)-1)
This will simulate a "text to columns" via formula since we don't like manual text to columns
Or you could have done a text to columns based on : delimiter and then use trim function on the actual data to remove the extra space in front
Copy this down the entire column B until your data in column A ends
If you have our WST macros installed, go to B1 and hit CTRL+SHIFT+ALT+D

STEP 2: Enter in 1, 2, 3 repeatedly in column C (needed for Filter shortly)
In cell C1, type in 1; in cell C2, type in 2; in cell C3, type in 3
In cell C4, enter in formula: =C1 and then copy C4 down until your data in column B ends
You should have 1,2,3 repeatedly in column C
If you have our WST macros installed, go to C4 and hit CTRL+SHIFT+ALT+D

STEP 3: do transpose array
Select cells D1:F1 (three cells across the first row of data)
Type in =TRANSPOSE(B1:B3) and hit CTRL+SHIFT+ENTER
The last part is important, as transpose must be an array else it won't work correctly
You formula is now: {=TRANSPOSE(B1:B3)}
The { } indicates an array

STEP 4: copy down the transpose
Select cells D1:F1 and copy down until your data in column C ends
If you have our WST macros installed, select D1:F1 and hit CTRL+SHIFT+ALT+D

STEP 5: clear out unwanted cells
At this point, you ONLY want the entries in which column C has a 1 in it
Thus, Select your entire data set from column C to F, so with your sample data, that's C1:F9
Hit ALT+D+F+F (any version of Excel for Windows) or ALT+A+T (Excel 2007 and above)
Go to C1 and open the drop-down (ALT+DownArrow) and select just 1 (uncheck Select All and then check 1)
Only the correct transposed values are visible now

Step 6: Copy paste values
Select C1:F9 (your entire data set basically) and Copy (Ctrl+C)
Go to a blank worksheet or anywhere on the side and paste (Enter or Ctrl+V)
It should paste automatically as values (else transpose function will reference wrong cells)
If it didn't paste correctly, then do a Paste Special (ALT+E+S+V)
In either case, the rows with "2" and "3" in column C do not appear.
This is because when you copy/paste from an filtered data set, it does not copy the hidden (non-visible cells)

This is relatively straightforward; if you have different number of rows for each record, i.e. some have Phone, some don't or some have Website, some don't and it's not consistent, then it's more challenging. You would skip STEP 1 and at the end, add a bunch of offset match functions to properly parse the data into the right column headings, and then use STEP 1 to clean out the headers. Incidentally, all of these techniques we just covered are thoroughly reviewed in our Advanced Excel for Data Analysis course: http://www.wallst-training.com/self-stu ... tech-excel

If you can master those topics we cover in that course, you'll be able to tackle difficult stuff like this.
Hope that was helpful. Read More