QuickBooks and Excel Tutorial- Turning Your Financial Statements Into Meaningful Information

If you liked this video then you will definitely like my 3 hour recording called
"Nerd's Cash Flow Projections"
http://nerdenterprises.com/knowledge/2011/10/nerds-cash-flow-projections/

Another related full length class available in my Knowledge Store:

Forecasting your QuickBooks statements in MS Excel:
http://nerdenterprises.com/knowledge/2010/11/forecasting-your-quickbooks-statements-in-excel/

One of the most important aspects of running a business is planning. My wife asked me a while back when we happened to be getting in a bunch of new clients who all had financial disasters on their hands how they got into that situation in the first place? She was coming from a place where she watched me spend hours upon hours setting up budgets in excel and tracking everything daily in QuickBooks and looking at our financial information 6 ways until Sunday before making any moves. When I transitioned myself from a full time job into self employment I knew that I was able to live comfortably on what I was earning in salary. So it was a fairly simple formula for me. I just had to look at what I could charge in terms of an hourly rate and then see how many hours I needed to be billing consistently in order to make it work. I wanted to be sure it was an EASY target because I was so afraid of not making it -- not being able to pay my bills -- and in those days I was renting, didn't even have a mortgage.

So my answer to my wife was that not everyone is an accountant. In fact most people aren't, but many people have a great idea or an amazing skill and they have the same desire for real freedom that I have. So they set out and often start making money right out of the gate. As long as the money is coming in it doesn't concern them to have any real tracking of it. That is until the tax bill comes and then they see how much money they made and have to pay taxes on it, and now they're wondering where all the money went.

Bottom line is the first thing everyone should do when they start a business is consult with an accountant and a lawyer to find out everything they need to know. As far as projections go, they are critically important at the beginning and they become more important as you go. Once the business is going then you want to plan on setting aside money so you have capital to fund your new ideas and the general growth of the company. The only sure way to do this is to have some projections and to monitor them in comparison with the actual results every month.

This week's web cast will show you how using a simple tweak you can start to analyze your existing Profit and Loss statement in a way that many people don't, and should. Then I show you how you can take that a whole lot further once you export the QuickBooks report into excel.

For more information on my consulting and training services:
http://nerdenterprises.com
(866) 945-8070

---

Closed Caption:

hi Seth David hear from their
enterprises . com and I wanted to give
you another brief webcast to give you a
little bit of a taste of how you can get
some very powerful and valuable
information out of your financial
reports one in particular by simply
changing one little setting on your
profit and loss statement so let me get
right into that show you exactly what
I'm talking about
most of us are accustomed to going here
to reports company & Financial and
running the standard profit and loss
here and that gives us of course useful
information in terms of how the company
performed as far as income expenses go
over a set period of time now the
default is this month to date we're
going to want to change that too
let's just do this fiscal year to date
and what that gives us is the entire
year up until now and you can look at
this and of course it tells you here's
all the income you've earned in here all
here all the expenses you incurred in a
lot of us love to go right to the net
income and look how much did we make for
this . one thing that you can do that
will change this and make it so much
more valuable is a very simple little
tweak on this which is done by going
over here to where it says columns and
dropping down the total only and turning
that into the month when you click on
that as you'll see you'll now have
information laid out a month-by-month in
a column format for the entire year and
this is much more powerful in terms of
being able to analyze your company and
understand what went on with your
company looking right down at the bottom
here in January right away i can see i
did 2400 net then i can see in February
had a loss of a thousand and March had a
loss of 8,500 I want to look at that I
want to find out why did i lose so much
more money in march and $MONTH not make
a profit for that matter and so running
the report looking at in this format
gives me some immediate opportunities to
get insights into how my company
performed and why perform the way it did
over a given period of time and now in
the next section I'm going to show you
how you can begin to take this a whole
lot further by exporting this into excel
and taking a look at some of the things
you can do once this information is laid
out in Excel for you
so once we're ready to go a little
further with the analysis we can come
over here and click where it says export
and we make sure setting to the way we
like them the main things to me or no
space between the columns and setting
the head of the screen and excel is the
most important ones the other ones you
can play with and see what you like and
we click export again and it brings it
into excel for us now once we've got
this in place for the purposes of this
i'm going to actually eliminate all the
columns from august forward because I'm
going to sort of pretend that we're in
the year 2010 which is where i'm
recording this and the month of july so
that August forward would be projected
this would be the historical so let's
format this as a date
ctrl + 1 given the date format i go into
my date formatting choices double digits
that's great when I say 11 10 to just do
the first of every month as my headers
and this becomes important because a you
always want consistency when you're
doing any kind of financial modeling
like this and be when you get into more
complex financial models you may
actually want to perform operations
where you're looking up the the month to
compare information becomes very
important to make sure you get this in a
true date format so now I've got january
through december in here let me check
the formatting again we've got to do it
double digit date format and i recommend
just editing each one just to make sure
it's critically important that excel
recognizes these as dates now once we've
got that in place it's really easy to do
the next year because i can copy that
whole range over and then do a search
and replace fighting 2010 replacing it
with 2011 and of course actually get 12
replacements
ok and close now we're ready to do the
fun part first thing is we want to get
all our formulas extend it out so I'm
just going to do some copy and pasting
at the cost of goods sold my gross
profit
we're just looking for those subtotals
so you're looking for the thick black
lines there because that kind of let you
know where the subtotals are and the
rest of these are all formulas i'm also
going to very quickly because these
dotted lines annoy me come over here and
get rid of them
paste the formatting on the date so
that's consistent now we're ready to
start kind of doing the layout so when a
format these four input i'm just going
to put zeros in to fill the space for
now and I just my personal preference is
the comma formatting now we're going to
copy and paste this down just so we can
see where we need to start laying out
the projected numbers
then if I really want to just kind of
get a filler in there because i'm not
going to get into all how to write the
assumptions and so on you can take an
average of the last six months
copy that over so it becomes a rolling
average and you could roll that out
already you start to get an idea of what
your projections look like what the
assuming that there's no substantial
increase or decrease and sales are
expenses but this is a good way to get
started now what happens is then of
course you go back through this and you
start looking at your sources of income
depending on your business model you
might look at income by customer if you
have recurring business with the same
customers and you can start projecting
what you're going to be doing business
wise with them and so on and so forth
and what I encourage you to do if you
really want to learn how to take this
stuff a lot further is come over to my
website go to the Learning Center here
and i'm going to post the links wherever
I post this video so you can get right
in the first one is called forecasting
your quickbooks statements in Excel and
what this does is this takes what I've
just done about ten steps further and
shows you how to write the formulas how
to make assumptions and how to build out
the balance sheet amazon a simplified
statement of cash flows so that the
bottom line is when you're done you can
project out to the end of $MONTH 2011 or
the end of two years from now if you
want to and a rough estimate of what you
think your income expenses are going to
be and possibly more importantly how
much money you can expect to have in the
bank now of course it's never gonna be a
hundred percent accurate because things
beyond your awareness will happen
however you can give yourself a pretty
good idea as long as you're conservative
which means underestimate on the income
overestimate on the expenses and
hopefully if you've done a good job of
that any surprises will happen in your
favor and not against your favor and
that's right here for clients when
clients call us up and once we've got a
bookkeeper out we've confirmed that the
bookkeeping is accurate because of
course that's the key the bookkeeping
information has to be accurate in order
for this to be meaningful
once we're there then i will go in
myself with the clients and actually do
this kind of stuff for them the other
class i encourage you to take here in
our Learning Center's only $35
understanding your financial statements
what story or you're not
was telling you and that's going to take
you back to this report is going to show
you how to take a look at months like
march we were negative x 8500 and drill
into the numbers and and really kind of
audit the numbers and teach you how to
come up with a hypothesis about what
might explain what happened when you
look at the trends in the numbers here
and then it shows you how to confirm
whether or not you can support that
explanation and if not it shows you how
to find out Utley why it did happen if
it was if it was different than what you
would have expected to see
so really good class really valuable
information to people who took it live
with me we're really happy and so i
encourage you to visit us at nerd
enterprises . com and of course I always
welcome your comments and questions just
email me at set nerd enterprises . com
or nerd enterprises . com forward slash
blog and you can felt the ask us form
and that goes in an email to me and i
look forward to seeing you on the web