MS Excel 2010 Tutorial: Employee Sales Performance Report, Analysis & Evaluation - PART 1

http://www.excelfornoobs.com
http://www.subjectmoney.com
http://www.subjectmoney.com/articledi...

What we have built here is a Microsoft Excel template that can be used to analyze the performance of sales representatives with a focus on restaurant servers (waiters and waitresses). In this example we are analyzing the quarterly performance of the restaurant as a whole, and each sales unit (the servers). We will also take a deeper look into each month that the quarterly sales report consists of: January, February and March. Our goal in analyzing this sales report is to improve the sales performance of our servers.

---

Closed Caption:

well we've built here is a microsoft
excel template that can be used to
analyze the performance of cells
representatives with the focus on
restaurant servers in this example we
are analyzing the quarterly performance
of the restaurant as a whole and each
cell's unit the cells units are the
servers we will also take a deeper look
into each month that the quarterly sales
report consists of january-february and
March go on analyzing the cells report
is to improve the sales performance of
the restaurant service as you can see in
this sheet titled quarterly report it
contains a quarterly report which has a
table that summarizes the servers and
the restaurant sales performance in this
sheet we have also included excel bar
charts to provide visuals this excel
sales performance template consists of
four separate sheets the quarterly
report and reports for each month within
the quarter if you take a look at the
tabs on the bottom you can see the each
month has a separate sheet with its own
cells report again in this case we are
analyzing a yearly quarter quarter one
which consists of three months
january-february and march we are
analyzing the performance of the quarter
and the performance of each month
searching for improvements that could be
made by the servers to improve their
sales performance
surely there are other factors
contributing to your takeaway from
restaurant cells that are not within the
control the servers but we're going to
analyze their performance compared to
each other and compared to the average
as you will see we will find issues that
are surely partially within the control
servers improving on these issues we
find in our analysis will increase the
sales of each server the cells of the
restaurant and the net income for all
parties involved in this business
we're going to start this off by
explaining the table that shows the
monthly sales report even though there
are three different months we're going
to focus on january cells report after
explaining january sales report you will
have an understanding of the sales
report for all months included since the
template used
for each month is the same the only
things that are different are the
numbers that are plugged in for each
month for example a server has different
sales for january than he did in
February we have duplicated this
template for each month of the quarter
and then plug in the respective cells
per item per employee and all other
information that must be plugged in
as you can see we have the names of the
employees categories of the item sold
such as appetizers large entrees
sandwiches alcohol desserts and so on
and Colin q you can see that we have
total sales this shows total sales for
each server and is the sum of each item
sales amount in column are we have the
total labor hours per server in column s
we have each employee's average sales
per labor hour and in column t we have
each employee's sales as a percentage of
total restaurant sales next to each
category of items sold we have a
percentage of total sales for each
employee the very bottom row row 10
shows the restaurant totals just as each
row shows how each employee is
performing row 10 shows how the
restaurant is performing as a whole
these totals are the restaurants totals
for example column c row 10 shows the
total sales for appetizers for that
month and sell q10 shows the restaurants
total sales the restaurants total sales
are the sum of all server sells the
restaurants total sales are what we will
refer to when calculating each
employee's cells as a percentage of
total sales the only numbers that we
have to manually enter into this report
of the sales numbers for each item sold
per month and the hours worked by each
employee each month all other cells such
as total sales percentage of sales and
the entire row representing the
restaurants totals is automatically
calculated by math formulas that we
entered into each cell each math formula
refers to the manually entered numbers
for example cclc 10 is a formula we use
some formula
in Excel adding up cells c5 through c9
which adds all servers appetizer sales
together to arrive at the total
appetizer sales for the restaurant
this is done for every category in this
sales report large entrees alcohol
everything else
now look at the total sales column for
each server we use the sum formula to
find their total sales in this cell we
use some formulas adding up the sales of
each category to find their total sales
then wrote n we summed up the total
sales for each server to find the total
cells for the restaurant and column s we
calculated sales / labor hour of each
employee but by dividing their total
sales by their total labor hours for
each month to understand the cells
important even more depth take a look at
column tea and column t we calculated
each service total sales as a percentage
of the restaurants total sales we did
this by dividing each server sales by
the restaurants total sales this figure
can be used to compare each server some
service sell more than other servers by
looking at this we can see which server
holds the most weight as far as cells
are concerned
ok so now that you have an understanding
of the monthly service sales performance
report let's move on to the quarterly
report which is a summary of the
employees and the restaurants
performance of the quarter the quarter
report has own tab which has a table
similar to the monthly reports we
previously explained it shows the total
monthly sales for each server and also
the total quarterly sales just as in the
month of $MONTH report row 10 is
designated for the restaurants and all
figures are derived from the sales of
all the servers you can see that every
cell in the quarterly report is an
equation and not manually entered and
information the formulas entered are
referring to the month reports which are
on separate tabs for example we got t
James quarterly sales total by adding up
t James total monthly sales from
january-february and more
the goal is to have as little manually
entered information as possible
this minimizes mistakes since many cells
are formulas that refer to other cells
one simple manually entered in mistake
will change the numbers for many
different cells making a report and
accurate therefore is important to
double-check all manually enter and data
and also to ensure that all formulas are
correct
now let's take a look at the charts the
excel Jabbar chart titled restaurant
total is simple it shows the restaurants
total monthly sales for each month of
the quarter the other bar chart that we
have created shows each employee's sales
performance per month all compared to
each other this chart is derived by the
information and the quarterly employee
report table
this chart shows each employee's sales
performance per month
the reason that we have added this chart
is because it gives us a picture to look
at instead of numbers this chart
highlights each employee's performance
and any trends that we may have to
analyze charts in the highlight
information in a way that looking at
number simply cannot do we can I am very
watch part 2 of this video