Income Statement (P&L)


 

Home
Site Map and Search
Past Students
What's New/What's Due?
General Guidelines
Assignments for CIS 100
Calendar- CIS100
CIS 100 Syllabus
Hints for Assignments
Student Learning Outcomes
Word Processing
Internet & Email
Spreadsheets
Access DB
PowerPoint
Portfolio
Up
Budget Spreadsheet
Campus/Major Analysis
Income Statement (P&L)
Store Invoice
Carpet Store Invoice
Simplified Income Tax
Car Loan Payback
Payroll Sheet (Independent)
Hints for Spreadsheets
Vacation Time
Tutorials
Printing Hints
Reference


Search this site or the web powered by FreeFind

Site search Web search

Student Learning Outcomes:

After completing the P&L spreadsheet the student should be able to:

bulletExplain the components of an "Income" or "Profit & Loss" Statement and how they are related to businesses activity and to each other,
bulletCalculate percentages and percentage differences.
bullet

Select and utilize the following spreadsheet skills:

bulletExplain the Value of spreadsheet is ability to have numbers recalculated quickly and accurately so alternatives can be tried and best solution implemented
bulletAutoSum, subtraction, division, multiplication, IF function
bullet

Adjust column width

bulletInsert Rows
bulletFormat Row, Column, Cell (font and alignment)
bulletSheet Formatting $ of 1st and Last row, comma others, no cents on amounts over $100
bulletSeries: Jan, Feb, …; Mon, Tue,…;1Qtr 2006 ...
bulletPrint with & w/o gridlines, row&column headings, formula/values
bulletDrag formula down columns, across rows (note undesired effects)
bulletPie Chart, Explode Pie, Patterns in slices
bulletSelecting noncontiguous cells for charting (hold ctrl while clicking non touch value and label cells)
 Procedure

Assessment

 Check list

 Hints

Naming Cells

IF function

VLOOKUP

Print Hints

Grading Checklist

Procedure:

The  Profit and Loss or P&L statement is the most important business statement for both business owner(s)/managers and investors.

bulletTake the "warm up quiz"
bulletCreate the sheet below (2003)
bulletFormat it properly per the checklist
bulletCreate a pie chart of the components of Revenue, which are:
bulletcost of goods sold
bulleteach individual non-operating expenses
bullettaxes
bulletnet income after tax
bulletUpload the sheet to your briefcase
bulletSubmit assignment by emailing your briefcase location
bulletPrint the sheet for your portfolio (remember to preview and then print from the preview screen)
See Print Hints for how to print
bulletMark sure all cells are properly formatted and header is correct and compete
bulletUse Page Setup, Page tab to select landscape orientation if needed
bulletPrint the sheet
bulletSwitch to formula view (Ctrl + ~)
bulletAdjust column widths if needed so all formulas are completely visible.
bulletUse Page Setup, Page tab to select landscape orientation if needed
bulletUse Page Setup, Sheet tab to turn on Row and Column labels and Gridlines
bulletHighlight just the numbers and Set Print area
bulletPrint Preview
bulletPrint
bulletReset everything
bulletSet print area clear print area
bulletFile menu, Page Setup, Sheet tab,  uncheck Row and Column labels and Gridlines   

Try it yourself, but if you need help follow the step by step procedure

  1. Type the ALL Labels in Column A and Row 1 
  2. Enter the Revenue values in cell B2 and Cost of Goods Sold value in cell B3.
    bulletRevenue is ALL the money that business
    gets from customers.
    bulletCost of Good Sold is money a business
    MUST spend to obtain the goods & services
    it needs to produce what it sells
    like     salaries, materials, rent, electricity
  3. Create the appropriate formula for Gross Profit (cell B4). 
    It's the difference between     Revenue  and Cost of Goods Sold
  4. Enter the 3 Non-Operating Expense values in cells B5, B6 and B7
    Discretionary (aka NonOperating) Expenses
    are funds the business chooses to spend
    believing they will increase profit
    by more than the expense. 
    These expenses are discretionary,
    meaning they are not required to produce the product or service
    (like cost of goods expenditures are).
  5. Computer the Total Discretionary (aka NonOperating) Expense (cell B8)
  6. Compute  Net Income which is Gross Profit minus Total Discretionary (aka NonOperating) Expense  cell B9). 
  7. Calculate Taxes (cell B10) which depends on Net Income.  IF Net income is less than $25,000 none are paid, if over $25,000 then 23% of the amount that is over $25,000.
    Note     IF function  required since IF Income is less than $25,000 there are no taxes but IF over, taxes are on income that exceed $25,000 (a formula with a subtract and a multiply
  8. Calculate Net Income after Tax (cell B11).
  9. Enter Number of Shares Outstanding value in cell B12 and the formula for Earnings Per Share in cell B13.
  10. Enter the 2nd Qtr Revenue values in cell C2 and  the Cost of Goods Sold values in cell C3
  11. Copy cells B4 to B13 into C4 to C13.
  12. Create the correct Difference formula (number is positive if things improved) in Cell D2.
    Then drag it down to the entire column
  13. Create the correct Percentage Difference (the difference divided by the original amount) formula in Cell E2.  Drag it down the column.
  14. Make sure everything is formatted correctly. 
    Run the Spell checker.
  15. Create a good page heading with your name, the date and a creative page title.
  16. Preview and then print.
  17. Switch to formula view. 
    Add Row and Column Labels.  Preview and Print
 

A

B

C

D

E

1

   1Qtr 2007 2Qtr 2007 Difference

%
Difference

2

 Revenue $600,000 $800,000 formula formula
3 Cost of Goods Sold 444,400 444,400 copied from D2 copied from E2
4 Gross Profit
a.k.a. Gross Margin or Gross Income		 formula copied from B4 copied from D2 copied from E2
5 Interest 25,000 33,000 copied from D2 copied from E2
6 Advertising 15,000 20,000 copied from D2 copied from E2
7 Christmas Party 5,000 10,000 copied from D2 copied from E2
8 Total Non-Operating expenses formula copied from B8 copied from D2 copied from E2
9 Net Income  formula copied from B9 copied from D2 copied from E2
10 Taxes (23% of income over $25,000) formula copied from B10 copied from D2 copied from E2
11 Net Income After Tax formula copied from B11 copied from D2 copied from E2
12 Number of Shares Outstanding 5,000 5,000 copied from D2 copied from E2
13 Earnings Per Share formula copied from B12 copied from D2 copied from E2
 

 

 

 

 

 

 

 

 

Assessment

 
Advanced
Proficient
Partially Proficient
Non-Proficient
A
B
C
F
Follows all the Cardinal, formatting, naming, printing and charting rules on the Checklist
Sheets is labeled and ordered. Placement of data shows planning and facilitates easy reading & comprehension. 		Follows all the cardinal rules on the Checklist.  Violates 1 or 2 formatting, printing or rules. Follows all the cardinal rules on the Checklist.  Violates 3 or 4 formatting, printing or rules. Chart missing. Only formatted values or formula sheet submitted.  One cardinal spreadsheet rule or 5 or more formatting or charting rules ignored.

[ Home ] [ Up ] [ Budget Spreadsheet ] [ Campus/Major Analysis ] [ Income Statement (P&L) ] [ Store Invoice ] [ Carpet Store Invoice ] [ Simplified Income Tax ] [ Car Loan Payback ] [ Payroll Sheet (Independent) ] [ Hints for Spreadsheets ] [ Vacation Time ][ Site Map and Search ] [ Past Students ] [ What's  New/What's Due? ] [ General Guidelines ] [ Assignments for CIS 100 ] [ Calendar- CIS100 ] [ CIS 100 Syllabus ] [ Hints for Assignments ] [ Student Learning Outcomes ] [ Word Processing ] [ Internet & Email ] [ Spreadsheets ] [ Access DB ] [ PowerPoint ] [ Portfolio ]

This page was created by   and was last updated on 09/15/07

Page Name: CIS100 Income Statement (P&L) Spreadsheet Lab in UCC CIS100 Introduction to Computer Applications
URL:            http://faculty.ucc.edu/business-greenbaum/Intro/C100SS_IncomeStmt.htm

Disclaimer:  Information on this site represents the thoughts and opinions expressed by the author and not that of Union County College.  The author takes full responsibility for the information presented.  By using the information contained herein, the user willingly assumes all risks in connection with such use.  Neither the author nor Union County College shall be held liable or responsible for content, errors, and/or omissions in information herein or information contained on any Web Page to which it is linked.  Furthermore, neither the author nor Union County College shall be liable for any special, consequential, or exemplary damages resulting, in whole or in part, from any user's use of, or reliance on, this material or material set forth on any Web Page to which it is linked. 
Copyright:      ã Maureen Greenbaum Sumware in NJ  2001, '02, '03, '04, '05, '06, '07, 2008.  All rights reserved. AddThis Social Bookmark Button