Income Statement Completing this activity will help you learn to: Determine total revenues, expenses, and net income for a period and compare results over time. Extract information from a data set using Excel formulas, functions, and/or pivot tables. Scenario: Company A is owned and operated by Elyse Traynor, who is a trainer and consultant on the use of specialized equipment. Elyse reports all her transactions for the year in a list, although they are in random order. She will use this data to prepare her monthly and annual income statements. Elyse uses the Fees Earned account to report income from each client consultation. She uses seven expense accounts to record her bills and costs. The Data worksheet contains a list of revenue and expense transactions for one year. Each row provides information about one transaction, including month incurred, customer or vendor involved, account impacted, and amount. Required: Open spreadsheet Complete one column on the Data worksheet: Amount on income statement. R1. Cell F2: Use an IF function to return the value in E2 as a positive number for a revenue transaction or as a negative number in parenthesis for an expense transaction. R2. Copy down the entries in cell F2 to all other rows with data. Steps to create a pivot table: S1. Select a cell in the data on the Data worksheet. S2. Select Insert/PivotTable. The data range will automatically be inserted. A "Create PivotTable" window should appear. S3. In the window, under "Choose where you want the PivotTable report to be placed," select "New Worksheet." A new worksheet should appear with the PivotTable. S4. A PivotTable Field List should pop up. Drag fields into rows, columns, filters, and values areas as needed. (If this gray work box does not appear, right click in the pivot table and select Field List.") Questions: Use the information on the Data worksheet and pivot tables, formulas, and/or functions to answer the following questions. Q1. What is the amount of net income that the company reported for the year? Q2. What is the amount of net income in the month that reported the second-highest net income amount during the year? $ Q3. What is the difference in revenue between the month with the highest total revenue and the month with the lowest total revenue? Q4. What percent is the Buford Gas bill in May in relation to total Utilities Expense in May? (Round the percentage to one decimal place, such as 8.6%.) % Q5. The income statement reports income from operations, which is determined by adding all amounts associated with the Fees Earned account. adding all amounts associated with the Fees Earned account and deducting from that total all amounts associated with expense accounts. adding all amounts associated with expense accounts and deducting from that total all amounts associated with the Fees Earned account. adding all amounts associated with expense accounts and adding to that total all amounts associated with the Fees Earned account. Month Customer/Vendor Jan Reynolds Account Fees Earned Amount 2,691 Dec Buford Gas Utilities Expense 1,466 Jan Freedman Supply Supplies Expense 918 Jan South Pekin Fees Earned 1,559 Apr Mileage Vehicle Expense 1,554 Feb Buford Gas Utilities Expense 1,646 Sep Arrow Maintenance Maintenance Expense 1,489 Feb Saben Landlords Rent Expense 1,800 Jun Crestfield Promotions Advertising Expense 2,091 Feb Carbon Chemicals Maintenance Expense 397 Sep Lemont Fees Earned 1,070 Nov Ewing Fees Earned 1,383 Nov Mary Carmichael Wages Expense 1,000 Oct Fix-It Fast Maintenance Expense 1,143 Apr Ashford Products Supplies Expense 1,450 Mar Collinsville Fees Earned 1,156 Apr Buford Gas Utilities Expense 1,710 Apr Mary Carmichael Wages Expense 1,000 Oct Auburn Fees Earned 2,696 Mar Cabery Fees Earned 2,725 Mar Buford Gas Utilities Expense 2,102 Nov Mary Carmichael Wages Expense 1,000 Dec McClure Fees Earned 2,195 Apr Breese Fees Earned 2,329 Jul Mileage Vehicle Expense 1,570 Mar Light-Up Electric Utilities Expense 1,768 Aug Ludlow Fees Earned 2,274 Nov Seatonville Fees Earned 2,875 Oct Ellsworth Fees Earned 3,198 Jun Normal Fees Earned 1,163 Jul Fairmont City Fees Earned 2,080 Aug Verity Products Supplies Expense 1,066 Jul Muddy Fees Earned 2,089 Feb Greenfield Fees Earned 2,151 Apr St. Jacob Fees Earned 2,415 Nov Colp Fees Earned 2,932 Nov Premium Insurance Insurance Expense 900 Aug Comcast Services Utilities Expense 1,475 Mar Comcast Services Utilities Expense 880 Aug Saben Landlords Rent Expense 1,800 May Verifone Phone Service Utilities Expense 2,042 Oct Buford Gas Utilities Expense 1,116 Jan Verifone Phone Service Utilities Expense 2,234 Jun County Water Dept. Utilities Expense 1,281 Jul Premium Insurance Insurance Expense 900 Oct Zeigler Fees Earned 2,413 Aug Verifone Phone Service Utilities Expense 2,179 Jul Saben Landlords Rent Expense 1,800 Aug Premium Insurance Insurance Expense 900 Nov Hecker Fees Earned 1,995 Oct Forest View Fees Earned 1,278 Jun Comcast Services Utilities Expense 183 Mar Hutsonville Fees Earned 3,179 Oct Park Forest Fees Earned 1,943 Nov Buford Gas Utilities Expense 725 May McCullom Lake Fees Earned 2,206 Mar Mary Carmichael Wages Expense 1,000 Jul Ashford Products Oct County Water Dept. Supplies Expense 1,139 Utilities Expense 798 Nov Mount Olive Fees Earned 2,987 Oct Villa Park Fees Earned 1,335 Dec Rainbow Plumbing Maintenance Expense 461 Jan Thompsonville Jul Kenney Fees Earned 2,970 Fees Earned 2,518 Dec Premium Insurance Insurance Expense 900 Aug Glasford Fees Earned 2,840 Dec Philo Fees Earned 2,019 Apr Countryside Fees Earned 1,287 Mar Sandoval Fees Earned 2,374 Dec Oakwood Fees Earned 2,564 May Mileage Vehicle Expense 628 Aug Lebanon Fees Earned 1,744 May Jan Comcast Services Summerfield Utilities Expense 1,124 Fees Earned 1,934 Sep Arrow Maintenance Maintenance Expense 1,897 Jul Crossville Fees Earned 3,183 Sep Mokena Fees Earned 3,068 Sep Light-Up Electric Utilities Expense 881 May Royal Lakes Fees Earned 2,462 Feb Comcast Services Utilities Expense 1,476 Dec Mary Carmichael Wages Expense 1,000 Aug Murrayville Fees Earned 2,979 Feb Elkville Fees Earned 1,376 Jan Carbon Chemicals Maintenance Expense 820 May Mazon Fees Earned 3,166 Jul Oct Saben Landlords County Water Dept. Rent Expense 1,800 Utilities Expense 1,131 Mar Rockwood Jul Okawville Fees Earned 1,034 Fees Earned 1,723 Oct Ashmore Fees Earned 3,051 Feb Central City Fees Earned 1,787 May Reliable Service Jun Buford Gas Maintenance Expense 2,160 Utilities Expense 2,102 Jul Mary Carmichael Wages Expense 1,000 Aug New Boston Fees Earned 1,288 Jul Orland Park Fees Earned 1.300 Feb Granite City Fees Earned 2,697 Jan Thayer Fees Earned 2,810 Sep Waynesville Fees Earned 3,099 Mar Palos Park Fees Earned 1,529 Jul Nauvoo Fees Earned 1,750 Feb Nov County Water Dept. North Henderson Utilities Expense 482 Fees Earned 2,528 Aug Warren Fees Earned 1,222 Jan Lake in the Hills Fees Earned 2,069 Aug Mar Ashford Products Fix-It Fast Mar Wayne Feb Woodhull Apr Ava Dec County Water Dept. Feb Clarke Supply Nov Comcast Services Supplies Expense 340 Maintenance Expense 2,001 Fees Earned 1,195 Fees Earned 2,037 Fees Earned 2,191 Utilities Expense 1,228 Supplies Expense 1,122 Utilities Expense 209 Apr Light-Up Electric Utilities Expense 1,864 Jan Jonesboro Fees Earned 3,200 Nov Naplate May Pontiac Fees Earned Fees Earned 1,503 1,937 Jan Lansing Fees Earned 1,413 May Freedman Supply Supplies Expense 1,027 May Pleasant Hill Fees Earned 2,400 Nov Clayton Fees Earned 2,648 Dec Verifone Phone Service Utilities Expense 1,438 Jun La Grange Fees Earned 2,230 Feb Tower Lakes Fees Earned 2,102 Dec Jan Aug Jun Mary Carmichael Jun Freedman Supply Mar Fix-It Fast Norris Longview Feb Rainbow Plumbing Sep Buford Gas May Schiller Park Kirkwood Nov Crestfield Promotions Oct Crestfield Promotions Feb County Water Dept. Mar Fairview Fees Earned Wages Expense Supplies Expense Maintenance Expense 1,000 938 1,413 Fees Earned 1,075 Fees Earned 2,995 Maintenance Expense 732 Utilities Expense Fees Earned 1,674 2,332 Fees Earned 1,722 Advertising Expense 1,191 Advertising Expense 1,328 Utilities Expense 1,497 1,594 Dec Sep May Oakdale Sep Milford Sublette Mary Carmichael Fees Earned 1,607 Fees Earned 1,420 Fees Earned Wages Expense 2,883 1,000 Dec Comcast Services Utilities Expense 1,288 Jul Coatsburg May Oct Mary Carmichael Dec Saben Landlords Jul Eddyville May Carterville Mar Saben Landlords Apr Carbon Chemicals Saben Landlords Sep Rock Falls Oct Butler Apr Savanna Rent Expense Fees Earned Fees Earned Fees Earned Rent Expense Wages Expense 1,000 1,800 1,135 1,004 2,680 1,800 Maintenance Expense 1,363 Rent Expense 1,800 2,907 1,137 2,207 Oct Mileage 1,648 2,773 2,012 Nov 1,874 Oct New Athens Oct Rainbow Plumbing Light-Up Electric Jan County Water Dept. Sep Steger Jun Jan Light-Up Electric Light-Up Electric Sep County Water Dept. Aug Litchfield May Rio Mar Lake Bluff Cerro Gordo Mar Jan Feb Apr Mileage East Gillespie Rainbow Plumbing Apr Verifone Phone Service Apr Chatsworth Nov Rainbow Plumbing May Glenwood Oct Johnsburg Feb Karnak Aug Mary Carmichael Aug Fix-It Fast Oct Old Ripley Feb luka Sep Saben Landlords Feb Mary Carmichael Jun Premium Insurance Jun Hodgkins Fees Earned Fees Earned Fees Earned Vehicle Expense Fees Earned Maintenance Expense Utilities Expense Utilities Expense Fees Earned Utilities Expense Fees Earned Fees Earned Fees Earned Fees Earned Vehicle Expense Fees Earned Maintenance Expense Utilities Expense Fees Earned Maintenance Expense Fees Earned 1,052 3,200 1,255 Utilities Expense 1,352 Utilities Expense 1,289 1,753 2,302 1,162 1,735 1,145 1,625 1,959 1,133 1,310 599 2,753 1,525 2,846 1,000 Maintenance Expense Fees Earned 845 1,163 2,107 1,800 1,000 Insurance Expense 900 Fees Earned 1,067 Fees Earned Fees Earned Wages Expense Fees Earned Rent Expense Wages Expense Jun Feb Bloomington Fees Earned Verifone Phone Service Utilities Expense Dec Arrow Maintenance Wilmington Sep Jun Reddick County Water Dept. Aug Jul Sims Aug May Apr Creve Coeur Premium Insurance La Harpe Jun Junction City Mileage Sep Apr Red Bud Jan Mill Creek Aug Crestfield Promotions Mar Pittsfield Oct Jun Aug May Millstadt Magnolia Buford Gas Aug Marengo Buford Gas County Water Dept. Long Creek May Nov Jun Momence Apr West Frankfort Aug Mary Carmichael Dec Crestfield Promotions Jul Comcast Services Sep Gorham Jan Eagarville Mar Calhoun Mar County Water Dept. Mary Carmichael County Water Dept. May Apr Sep Mar Comcast Services Cairo Nov Saben Landlords Sep Albers Apr Danforth Apr Premium Insurance Aug Jun Jun University Park Fix-It Fast Danvers Dec Zion Sep Freedman Supply Sep Crestfield Promotions Maintenance Expense Fees Earned Fees Earned Utilities Expense Fees Earned Fees Earned 1,431 136 1,000 2,023 1,112 1,280 1,181 2,106 Insurance Expense Fees Earned 900 1,978 2,865 1,136 3,046 Fees Earned Vehicle Expense Fees Earned Fees Earned Advertising Expense Fees Earned Fees Earned Fees Earned Utilities Expense Fees Earned Utilities Expense Utilities Expense Fees Earned Fees Earned Fees Earned Wages Expense Advertising Expense Utilities Expense Fees Earned Fees Earned Fees Earned Utilities Expense Wages Expense Utilities Expense Utilities Expense Fees Earned Rent Expense Fees Earned Fees Earned Insurance Expense Fees Earned Maintenance Expense Fees Earned Fees Earned Supplies Expense 2,349 2,191 1,678 1,891 3,005 1,158 2,151 157 1,392 3,164 3,164 1,163 1,000 2,185 135 2,102 1,173 3,063 1,193 1,000 659 460 3,022 1,800 1,543 1,209 900 2,121 1,036 2,938 2,885 267 Advertising Expense 1,555 Nov Aug Williamson Mileage Jul Crestfield Promotions Mary Carmichael Mar Atkinson Jan Feb Lostant Nov Verifone Phone Service Jan Premium Insurance Sep Premium Insurance Dec Midlothian Jan Buford Gas Jan Saben Landlords Fees Earned Vehicle Expense Advertising Expense Fees Earned Wages Expense Fees Earned Utilities Expense Insurance Expense Insurance Expense Fees Earned Utilities Expense Rent Expense Oct Verifone Phone Service Utilities Expense Aug May Sep Apr Apr Somonauk Mary Carmichael Norris City Mary Carmichael Comcast Services Nov Lomax Feb Jul Sep Nov Oct Feb May Apr Light-Up Electric Kinderhook Lexington Mason City Light-Up Electric Mileage Spring Bay Stronghurst Feb Mansfield Jun Carbon Chemicals Jul Westmont Sep Bartlett May Baylis Jun Jul Jan Mar Feb Sep Hanover Park Buford Gas New Douglas Mileage Chillicothe Verifone Phone Service Dec Argenta Jul Rainbow Plumbing Dec Verity Products Dec Mileage Jun Dolton Fees Earned Wages Expense Fees Earned Wages Expense Utilities Expense Fees Earned Utilities Expense Fees Earned Fees Earned Fees Earned Utilities Expense Vehicle Expense Fees Earned Fees Earned Fees Earned 1,245 1,083 653 1,702 1,000 1,771 604 900 900 2,627 1,964 1,800 1,432 1,436 1,000 2,379 1,000 1,432 3,107 2,104 2,640 1,148 1,588 1,658 1,187 2,235 2,323 3,177 Maintenance Expense 1,280 3,086 Fees Earned Fees Earned Fees Earned Fees Earned Utilities Expense Fees Earned Vehicle Expense Fees Earned Utilities Expense Fees Earned Maintenance Expense Supplies Expense Vehicle Expense Fees Earned Jun Verifone Phone Service Utilities Expense Oct Beardstown Apr Saben Landlords Fees Earned Rent Expense 1,451 2,956 1,092 1,786 1,862 561 2,738 809 2,343 1,185 1,653 925 2,794 519 3,156 1,800 Dec Odin Apr West Chicago Kankakee Liverpool Nov Jul Oct Mary Carmichael Jul Jun Mileage Potomac Fees Earned Fees Earned Fees Earned Fees Earned Wages Expense Fees Earned Vehicle Expense Mar Verifone Phone Service Utilities Expense Aug Jerome May Light-Up Electric Jul Mary Carmichael Mar Bulpitt Light-Up Electric Jul Sep Hopewell Aug Tamaroa Feb Gulfport Mar Crestfield Promotions Mary Carmichael Sep Oct Cedar Point Sep La Moille Jun Dec Nov Saben Landlords Pocahontas Lawrenceville Fees Earned Utilities Expense Wages Expense Fees Earned Utilities Expense Fees Earned Fees Earned Fees Earned Advertising Expense Wages Expense Fees Earned Fees Earned Rent Expense Fees Earned Fees Earned Apr Crestfield Promotions Advertising Expense Oct Premium Insurance Mar Mary Carmichael May Percy Jun Donovan Dec Nelson Jan Feb Mar Crestfield Promotions Mary Carmichael Premium Insurance Aug Rushville May Jan Dec Oct Kinsman Streator Light-Up Electric Comcast Services Apr Emden Oct Sep Verity Products Effingham Dec Glen Carbon Jan Arrow Maintenance May Crestfield Promotions Dec Dunlap Sandwich Jul Jan Mary Carmichael Insurance Expense Wages Expense Fees Earned Fees Earned Fees Earned Advertising Expense Wages Expense Insurance Expense Fees Earned Fees Earned Fees Earned Utilities Expense Utilities Expense Fees Earned Supplies Expense Fees Earned Fees Earned Maintenance Expense Advertising Expense Fees Earned Fees Earned Wages Expense 1,925 1,548 2,238 1,587 1,000 1,692 1,373 1,626 3,136 1,751 1,000 1,192 1,050 1,390 2,881 1,206 1,119 1,000 1,767 2,445 1,800 1,220 2,475 313 900 1,000 2,111 1,034 2,689 1,560 1,000 900 1,181 2,367 1,348 1,875 1,060 2,467 1,471 2,396 2,850 1,247 1,735 1,196 1,023 1,000 Apr Bull Valley Fees Earned Jun Keenes Homer Fees Earned Jan Nov Jun Jun Ashton Downers Grove Mary Carmichael Feb Premium Insurance Dec Mendon Jul Rainbow Plumbing May Batchtown Feb Astoria Feb Crestfield Promotions Oct Chatham Apr Nov Plainfield Wilmette May Reliable Service Verifone Phone Service Jul Dec Clear Lake Dec Peru Jun Jan Mary Carmichael St. Anne Nov Mileage Nov Office Depot Jan Comcast Services Mar Nov Aug Carbon Chemicals Arrow Maintenance Light-Up Electric Fees Earned Fees Earned Fees Earned Wages Expense Insurance Expense Fees Earned Maintenance Expense Fees Earned Fees Earned Advertising Expense Fees Earned Fees Earned Fees Earned Maintenance Expense Utilities Expense Fees Earned Fees Earned Fees Earned 1,561 2,987 2,947 2,354 1,749 1,000 900 3,147 2,070 1,386 2,278 785 1,163 1,066 2,077 1,188 1,786 2,385 2,034 Wages Expense 1,000 2,748 1,531 1,707 1,577 Vehicle Expense Supplies Expense Utilities Expense Maintenance Expense Maintenance Expense Utilities Expense 719 2,060 1,562