C
ov
e
r
s
MR2
01
2
Cr
eat
i
ngRepor
t
si
n
MANAGEMENTREPORT
ERI
I
I
H
o
wt
o
C
r
e
a
t
e
C
a
s
h
F
l
o
wS
t
a
t
e
m
e
n
t
W
i
t
h
T
h
r
e
e
V
a
r
i
a
t
i
o
n
s
a
n
d
O
p
t
i
o
n
a
l
L
i
n
k
t
o
E
x
c
e
l
byj
anl
enoi
rhar
r
i
gancpa
Table of Contents
Table of Contents ...............................................................................................................................................2 Getting Started ....................................................................................................................................................3 First Things First ...............................................................................................................................................3 Using this Cashflow Manual ..............................................................................................................................4 Strategy—the 3 Step Process ...........................................................................................................................6 Chapter 1 Internal Cash Flow ..........................................................................................................................10 Lesson 1 Balance Sheet Ties to the Cash Flow .............................................................................................10 Lesson 2 Start With the Row Definition for the Balance Sheet .......................................................................13 Lesson 3 How to Adjust the Row and Create the Column ..............................................................................15 Lesson 4 How to Specify Beginning and Ending Balances .............................................................................19 Lesson 5 Finalize the Cash Flow—Other Tweaks ..........................................................................................22 Chapter 2 Excel Cash Flow ..............................................................................................................................28 The Finished Excel Cash Flow........................................................................................................................28 Lesson 1 Start with the Internal Cash Flow .....................................................................................................30 Lesson 2 How to Link to Excel ........................................................................................................................33 Lesson 3 How to Adjust the Row without Going Out of Balance ....................................................................41 Chapter 3 Three Variations of the Internal Cash Flow ..................................................................................46 Trend Cashflow ...............................................................................................................................................46 Rolling 3 Cashflow ..........................................................................................................................................50 Quarter End Cashflow .....................................................................................................................................53 Chapter 4 Three Variations of the Excel Cash Flow ......................................................................................55 Trend Cashflow with Links to Excel ................................................................................................................55 Rolling 3 Cashflow with Links to Excel ............................................................................................................60 Quarter End Cashflow with Links to Excel ......................................................................................................64 Chapter 5 Optional—Help Completing the Excel Spreadsheet ....................................................................67 Use this report to help complete the Excel spreadsheet .................................................................................67 Chapter 6 Cash Flow Consolidations .............................................................................................................70 Internal Cash Flow Consolidations ..................................................................................................................70 Excel Cash Flow Consolidations .....................................................................................................................71 Chapter 7 Final Thoughts ................................................................................................................................87 Final thoughts..................................................................................................................................................87 About the Author ..............................................................................................................................................90 Jan Harrigan CPA ● [email protected] ● FRxBuzz.com ● 512-425-0600 .....................................................90 Page 2
Chapter 1 Internal Cash Flow
Cash flow ties to balance sheet
Page 12
Chapter 2 Excel Cash Flow
The finished cash flow using links to Excel
There are noncash items from the P&L, links to Excel to split fixed assets as well as long term debt,
and a single row for intangibles.
Page 29
Chapter 3 Three Variations of the Internal Cash Flow
The finished trend cash flow
Once the initial cash flow is done, this report takes all of about 5 minutes.
Page 49
Chapter 3 Three Variations of the Internal Cash Flow
Finished rolling 3 cashflow
Page 52
Chapter 3 Three Variations of the Internal Cash Flow
Quarter End Cashflow
This Quarter End cash flow sums the last 3 months.
The finished report
Note that this is not a Quarter To Date cashflow. It generates the numbers for the last 3 months, so
it's meant to be run at quarter end.
Page 53
Chapter 4 Three Variations of the Excel Cash Flow
Finished cashflow trend with links to Excel
And Ending cash per GL proves accuracy.
Page 59
Chapter 4 Three Variations of the Excel Cash Flow
Rolling 3 Cashflow with Links to Excel
The second variation on the Excel cashflow is the Rolling 3.
The finished Rolling 3 and YTD with links to Excel
Page 60
Chapter 4 Three Variations of the Excel Cash Flow
Quarter End Cashflow with Links to Excel
The finished report
Page 64
Chapter 5 Optional—Help Completing the Excel Spreadsheet
Sample page showing my
callouts and highlights—Jan
Column definition
Also named this CF Excel Support.
Create the report definition
Might want to use very small left and right margins as well.
Page 68
Chapter 6 Cash Flow Consolidations
Excel Cash Flow Consolidations
This isn't nearly as easy as a consolidation of the internal cash flow. But it's not bad. This chapter
presumes you've walked through Chapter 2 Excel Cash Flow—it has lots of background
information—and this chapter modifies and builds on Chapter 2's building blocks. Here I'll consolidate
two companies, and I'll link to two different Excel tabs for FASB information.
The separate link method of linking to Excel is used when you want to link to multiple Excel
workbooks or worksheets to pull data for more than one company.
Consolidated is a total of US and Canada companies
The consolidation doesn't pull from Excel. It simply foots the US and Canada companies that pull
from Excel. Company IDs are FW and FWC.
Page 71