# Excel-Profit _ Loss by stariya

VIEWS: 38 PAGES: 8

• pg 1
LESSON PLAN
Accounting
Profit and Loss Statement

KEY CONCEPT:
 Properly place income and expense line items on a profit and loss statement and interpret the
data correctly for future use.
 In order to stay in business, a company must continue to make a profit
 High sales does not necessarily mean profit
 If a company is not making a profit, expenses need to be cut, which may include people
losing their jobs
 Continuous profit cannot be ignored – comparisons between years will help head off
possible future problems

PERFORMANCE OBJECTIVES:
1. Correctly place income and expense line items on a profit and loss statement
2. Interpret a profit and loss statement to verify if a company has made a profit or a loss
3. Properly format an Excel spreadsheet using formulas to compare two years of data
4. Effectively manipulate data in a profit and loss statement to project future outcomes
5. Effectively utilize data to determine where in the company income and expenses changes
should occur to positively affect profit

CTE Technical Standards: Accounting Services Pathway standard:
A1.4 Prepare, analyze, and interpret financial statements for various business entities.
Foundation Standards: CAHSEE Math
1.6 Calculate the percentage of increase and decrease of a quantity

MATERIALS:

1. Student Computers with Microsoft Excel
2. Printer
3. Beginning profit and loss numbers

MOTIVATION/OPENER:

   The ability to create and correctly interpret a profit and loss statement is a very marketable
skill that will earn students \$9-\$12 per hour to start
   Reflect on practical uses and value of a profit and loss statement to a business
   Knowledge of the financial stability of a company is important before accepting a job with
that particular company
   In owning a company, the proprietor must be able to see where all money is going
   Business owners and managers must be able to quickly see if money being spent on
expenses is justified

1
PRESENTATION: (Teacher Activities)

1. Introduce vocabulary
a. Profit
b. Loss
c. Income
d. Expense
e. Sales
g. Returns and allowances

2.  Discuss various uses of a profit and loss statement. What types of businesses use a P&L?
3.  Look at a sample completed P&L. Briefly discuss each line item and what is consists of.
4.  Review line items that total sections (Total income; total expenses; total profit or loss)
5.  Student Activity #1
6.  As the students create their profit and loss statement, the teacher will create it via the
projector and laptop/teacher workstation.
7. Assessment #1
8. Confirm at the end of the P&L that all students have the correct balances and that they have
achieved those balances via Excel formulas by a verbal and visual confirmation of each
student/screen.
9. Show completed, correct P&L (attached) for student comparison.
10. Take any questions, comments, fix issues and problems.
11. Assessment #2
12. Discuss the reasons why a company would want to compare the P&L from two years
13. Application #2
14. Specific numbers for year #2 will be displayed via projector in paragraph format:
a. Using the following information, create a P&L for the fiscal year 2000: Sales,
\$105,000; Bad Debt, \$2,000; Salaries, \$30,000; Auto expense, \$12,000; Equipment,
\$6,000; Supplies, \$2,300; Insurance, \$5,500; Utilities, \$12,000
15. Once students are finished entering the second year, confirm through visual and oral
communication that their spreadsheet formulas are correct.
16. Demonstrate via the overhead projector the formula to get the percent for each P&L line
item comparing year #1 to year #2.
17. Demonstrate only the first two line item.
18. Student Activity #3
19. Review entire lesson
20. Discuss how a business could evaluate this information and use it.
21. Given the scenario of a company with a loss, discuss which line item could be changed to
positively affect the bottom line. Begin with the general section (income or loss) and move
toward specific line item (equipment, insurance, salaries). Why are their certain line items
that cannot be considered (cannot be changed – fixed costs vs. variable costs)
22. As a group, edit the current spreadsheet to increase profits without jeopardizing necessary
expenses.
23. Give final assessment

2
APPLICATION: (Student Activities)

Student Activity #1
1. Have students open up Microsoft Excel
2. Review basics of moving throughout Excel and how to create formulas
3. As a class, have the students create a profit and loss statement from information presented
by the teacher.
Student Activity #2
1. Give the students line item numbers for a second year.
2. Have the students create a second P&L in the next available column in their current Excel
3. Students will create this second set of numbers on their own utilizing what they learned from
their creation of the first P&L.
Student Activity #3
1. Student will complete all comparison percentages for the remaining line items on their own.

3
ASSESSMENT/EVALUATION:

Assessment #1
1. While the students are creating the first practice P&L along with the teacher, walk to the
back of the room to view all student workstations. Confirm that they are all at the same spot
Assessment #2
1. When initial P&L is complete, have a round table discussion where students will
successfully answer specific questions regarding the creation of the P&L.
a. What is the purpose of a P&L?
b. What does the P&L show a business person?
c. Where in the P&L would I enter may total income from merchandise sales?
d. Where in a P&L would I enter employee salaries?
e. Where in a P&L would I enter (any expenses)?
f. How are discounts or coupon amounts handled in a P&L?
g. What is the formula in Excel to get total income?
h. How can I tell from a P&L if the company has made money or not?
i. What does a negative balance at the bottom of a P&L mean?
j. How can a businessperson change a negative balance to a positive balance?
Final Assessment
1) Create a profit and loss statement comparing income and expenses from 1999 to 2000 using
Microsoft Excel using the following values. Label each line item. Create a final column
showing the percent changes for each line item from the first year to the second year (1.6).
a. Company A: Using the following information, create a P&L for the fiscal year 1999:
Sales, \$1,205,000; Bad Debt, \$25,000; Salaries, \$330,000; Auto expense, \$112,000;
Equipment, \$56,000; Supplies, \$232,300; Insurance, \$15,500; Utilities, \$232,000
b. Company A: Using the following information, create a P&L for the fiscal year 2000:
Sales, \$1,105,000; Bad Debt, \$32,000; Salaries, \$430,000; Auto expense, \$242,000;
Equipment, \$24,000; Supplies, \$282,600; Insurance, \$16,500; Utilities, \$265,000
2) Based on the P&L results, is Company A making a profit or a loss in 1999? In 2000? (1.d)
3) If there is a year that contains a loss, how could you adjust one or more expenses to become
profitable without jeopardizing income? (5.1 & 5.3)

4
CLOSING:

1) Review the line items of a P&L. Discuss the test and answer questions that they may have
now that they have taken the final assessment.
2) Reiterate the need for written evidence of the financial status of a company.
3) Introduce the balance sheet and discuss the connection between the P&L and the balance
sheet. Balance sheet will be the next financial document discussed.

LESSON PLAN REFLECTION:

This lesson plan is very long and more detailed than will actually be necessary when it is actually
taught. The biggest factor will be making sure the students are following at a good pace and
understanding the material before moving on to the next step. Many students might “fake” their
understanding of Excel formulas, which will really hurt them in the long run. The focus throughout
the beginning of the lesson and the first two assessments will be to make sure they understand the
reason and consequences of formulas (if something changes, you want your totals to change
accordingly) and to make sure they are using formulas and not just using a calculator and entering
the total.

5
Profit and Loss Rubric

Student Name: __________________________________________________________

CRITERIA
Total
5                 10                 15                  20           Points
Student does         Student is       Student is at        Student
not have grasp     uncomfortable        ease with       demonstrates full
of information;    with content       content, but     knowledge (more
student cannot      and is able to        fails to       than required).
subject.        concepts.
does not           contains           contains        formulas are all
contain any           correct        formulas, but          correct.
Formulas
formulas.        numbers, but         they are
not all contain      incorrect.
formulas.
Format         missing six or   missing two-six       format is           meets all
more formatting    requirements.       professional         formatting
requirements.                          but not to        requirements.
specific
examples
discussed in
class.
Basic          Assessment          Assessment         Assessment
assessment          question        questions and      questions are all
not answered or        student          show basic            has full
are answered      understanding      understanding     understanding of
Understanding     incorrectly.      of line items     of material but     the line items,
Student does      but not of total   lack attention    totals, and future
not grasp the        values or        to details or     use of the profit
meaning          future use.        future use.           and loss
behind the                                                statement.
numbers.
TOTAL

6
FINAL
ASSESSMENT
P&L

Company A

1999      2000    Difference
Income:
Sales     1205000   1105000      91.70%
Less bad debt:       25000     32000     128.00%
Total Income       1180000   1073000      90.93%

Expenses:
Salaries      330000    430000     130.30%
Auto expense       112000    242000     216.07%
Equipment         56000     24000      42.86%
Supplies      232300    282600     121.65%
Insurance        15500     16500     106.45%
Utilities    232000    265000     114.22%
Total       977800   1260100     128.87%
Expenses

Total      202200   -187100     -92.53%
Profit/Loss

7
Student
Assessment
One

Item Name            Year 1 Year 2   Difference

Income:
Sales 12050 10500              87.14%
Less bad debt:  2500  2000              80.00%
Total Income 11800 10300                87.29%

Expenses:
Salaries     33000   30000       90.91%
Auto expense       11200   12000      107.14%
Equipment        5600    6000      107.14%
Supplies      2323    2300       99.01%
Insurance       5550    5500       99.10%
Utilities   13200   12000       90.91%
Total Expenses        70873   67800       95.66%

Total 47127 35200              74.69%
Profit/Loss

8

To top