Creating Management Accounts using Excel #2 - Profit and Loss Statement

To start off the series I show you how to create the profit and loss statement using Excel, this is to be used as part of monthly management accounts.

Next Video: https://www.youtube.com/watch?v=EsJwS...

Create a bookkeeping spreadsheet using Microsoft Excel
http://youtu.be/LlWADbkGdac

30 Free Day Trial on KashFlow Accounting Software http://www.kashflow.com/?code=AFF2105084

Free Bookkeeping and Accounting Course
https://www.youtube.com/watch?v=IhYJb...

Try Crunch Accounting Software!
http://www.crunch.co.uk/?aid=samuel3a

Learn more at www.freebookkeepingaccounting.com

---

Closed Caption:

welcome back to this video in this video
I'm going to show you how to create a
P&L statements using Microsoft Excel
what we need to do is open a new sheet
on Microsoft Excel i'm going to Row 2
and just going from b2h i'm just going
to merge and center those cells and i'm
going to put in something like profit
and loss and i'm writing with or type
with one hand hence why it's low profit
and loss statement and then i'm going to
make that bold and underline it and then
I'm going to do hyphen and then the
business name so for this video output
be PFS neighbor my business BBFS limited
something like that something nice and
simple and I'm gonna do the same below
Merchant Center b2h and row three and
i'm going to put the date and this one
so it's June as the 16 from doing today
so let's do our management accounts from
a for the previous month the month of
may so the 31st a 5 2015 or we could put
May
$MONTH 2015 now your profit and loss
statements the the clue to what the
statement is is in the title just like a
lot of the statements and accounting it
gives you a breakdown of your net profit
your gross profit or your net loss or
gross loss for certain . and as these
are monthly management accounts it will
be for the previous month i'm going to
start let's say here b700 put sales make
that bold and then somewhere along here
let's put it in F let's put a pound sign
and that's sent that we have this pound
sign you can put a dollar sign all over
your currency symbol is now we're going
to lift our sales you may just have
sales and into the South figure for the
month we may want to break down sales
for the month into different categories
you may have cells in the North cells
from the South thousand east-west you
may have a couple of shops shop a shot
be he may sell different products you
may have cells product day sales product
B etc for this video list two products
because that's quite common so let's
just say sales product a and then just
below how do cells product B and then
let's do a third sales product see
we will then have a total sales and
these figures important this total cells
who there are some vague figures on this
report we want to stand out so let's
make our total sales bold and underline
and we can even use the spacebar once we
have underlined to make a nice line
across the screen like that we gonna be
forever then we have this line bringing
us to the the title now we wouldn't need
we will need to enter our sales figures
i'm going to show you how to do that if
you've got accounting stuff we can get
it from that if you're keeping some sort
of bookkeeping spreadsheets then you'll
get yourselves from that yourselves
would be recorded just from general
bookkeeping and that's where you'll get
the information from so let's say we had
500 pound well for sales of product day
a hundred of B and 350 of see now i'm
going to make all of these figures
centered and i'm also going to format
cells and go to currency two decimal
places and we don't need the symbol as
its titled with this inbox click ok I'm
going to changing slightly to make more
realistic says put 5 o-135 not going to
be dead on a hundred 357 953 5150 ok
something like that so our total sales
to enter our total sales we can do it
manually by hovering and highlighting
over our sales and the figure will be
given down here 1652 . 35 gives you the
total of everything that's highlighted
we can put a formula in we can put
equals click on the cell plus the next
cell plus next cell and and that will
give us the figure
also our next heading is going to be
cost of Sam's now your coastal cells is
the direct expense related to selling
the product so not things like overheads
no power rent rates fuel costs they are
generally not directly related to the
salver product if you're selling a
product the direct cost is the cost of
the product to you
so a direct costs in buying and
reselling the product soon cost of sales
once again we can categorize this or
just put in one lump sum and i'm going
to put in one lump sum i could put cost
of sales product a puppy
what products see I'm just gonna do one
some and because i'm going to do one
summits underline this like so
so let's put in one lump sum here and
let's say was 45 287 just to figure out
the top of my head now our next heading
and important figures are gross profit
you're gross profit is different from
the net your net profit
you're gross profit is your sales minus
your cost of sales so if you bought a
product at a wholesale price of five
pounds and solely for ten pounds
you're gross profit would be five pounds
it's the sale minus the cost of the sale
your net profit which will come to later
is your overall profit after all your
expenses so even though you may have
made five-pound profit on a sign of an
item you purchased it for five pounds
and solid 4 10 pounds she made a
five-pound profits there are other
expenses that will come off that profit
such as you may have driven your car
somewhere to sell the item or you may
have had to package the item to sell it
or sell some or pay some sort of fee to
sell it
you may also have electricity
you may have a computer and other things
that are involved in selling the item
and after those expenses that will give
you your net profit so the gross profit
is an important figure since the gross
profit is underlined and bold will do
lots of spaces again this is important
figure that we want to highlight and our
gross profit is simply the total cost of
our sales so equals 13 minus the cost of
sales and so so our gross profit is 119
. 48 let's make that bold let's make our
tools cells bold also okay our next
heading is I expenses or overheads
and here we're going to have things like
rent and power perhaps motor expenses
office expenses and there'll be lots of
our expenses too but for the purpose of
this video let's just keep things simple
and short sleeves for these figures so
once again we would get these figures
from our accounts that we keeping from
our bookkeeping duties rent
let's say that was 254 the month power
15 pound fifty motor expenses 35-pound
offers expenses saved 78 pounds where
reason
ok we will then have total expenses
make that bold and I'll total expenses
is the total for all our expenses once
again if we highlight that 379 37 or we
can do a formula as 2 equals some open a
bracket highlight the cells close the
bracket enter that's our total expenses
and then perhaps the most important
figure of the statement is our net
profit on that loss so let's make it
bold and underline it and that's bring
the line across and our net profit is
our gross profit minus our expenses
let's go equals gross profit minus total
expenses so we have a net profit of a
2011 and that is our profit loss
statement for the month now you could
have a column here I've left for him on
purpose here as we may have a column for
year today of the financial year to date
to say our financial year started to the
tax year in April we would have a
running total here of the profit for the
year or the sales cost of sales
overheads and that profit for
year-to-date whereas the first column be
in the month so you'd have figures in
here or you could have figures in here
that would reflect
the profit or loss today master
profit-and-loss statement in the next
video I'll show you how to manually
create the balance sheet
thank you for watching