Excel-Profit _ Loss by stariya


									LESSON PLAN
Profit and Loss Statement

   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

  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


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


      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

PRESENTATION: (Teacher Activities)

   1. Introduce vocabulary
          a. Profit
          b. Loss
          c. Income
          d. Expense
          e. Sales
          f. Bad debt
          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
   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
   23. Give final assessment

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.


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
       or moving ahead.
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)


   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.


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.

                                   Profit and Loss Rubric

Student Name: __________________________________________________________

                       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).
   Content            answer        demonstrate         elaborate.
                questions about         basic
                     subject.        concepts.
                 Spreadsheet        Spreadsheet       Spreadsheet         Spreadsheet
                   does not           contains           contains        formulas are all
                 contain any           correct        formulas, but          correct.
                   formulas.        numbers, but         they are
                                   not all contain      incorrect.
                Spreadsheet is     Spreadsheet is      Spreadsheet         Spreadsheet
   Format         missing six or   missing two-six       format is           meets all
                more formatting    requirements.       professional         formatting
                 requirements.                          but not to        requirements.
                                                       discussed in
                     Basic          Assessment          Assessment         Assessment
                  assessment          question        questions and      questions are all
                 questions are     answers reflect     spreadsheet      correct. Student
                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.


Company A

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

      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%

         Total      202200   -187100     -92.53%


 Item Name            Year 1 Year 2   Difference

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

         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%


To top