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. fill in the blank 1 of 1$

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 "Show 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? fill in the blank 1 of 1$

Q2. What is the amount of net income in the month that reported the second-highest net income amount during the year? fill in the blank 1 of 1$

Q3. What is the difference in revenue between the month with the highest total revenue and the month with the lowest total revenue? fill in the blank 1 of 1$

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%.) fill in the blank 1 of 1 %

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.