EXERCISE 3: CREATING AN INCOME STATEMENT Return for a minute to the very first exercise--the one where you created the equation "PROFIT = REVENUE- EXPENSES." That equation forms the basis for one of the most widely used financial statements in accounting called the Income Statement. The Income Statement consists of three parts: 1. Revenue - Cost of Goods Sold (COGS) = Gross Profit 2. Gross Profit - Operating Expenses = Net Income Before Taxes 3. Net Income Before Taxes - Taxes = Net Income (The Bottom Line) In this exercise you will create a simple Income Statement step-by-step. Revenue $ 100,000 $ 30,000 $ 70,000 20,000 Units Sold 5.00 Price/Unit 1.50 Cost/Unit Cost of Goods Sold Gross Profit $ $ S 70,000 Gross Profit Operating Expenses Advertising Salaries Rent Gas/Electric Net Income Before Taxes $ $ S $ $ 5,000 20,000 6,000 1,000 38,000 Net Income Before Taxes Taxes Net Income $ $ $ 38,000 5,700 32,300 15% Tax Rate Go to the "Excel Student Spreadsheets BUS 1011" and click on the tab/worksheet labeled "Income Statement Inserting Rows: Insert an additional Row in the Income Statement between "Salaries" and "Rent." Call it "Transportation Costs" and value it at $4,000. Under these assumptions the company's Net Income should have decreased to $28,900. Using this version of the Income Statement as a template, manipulate the entries to calculate Net Income for each of the following questions. ***AFTER EACH QUESTION, KEEP WORKING WITH THE NEW NUMBERS, DO NOT GO BACK TO THE ORIGINAL NUMBERS!*** What would the company's net income (bottom line) be if: 1. Taxes could be reduced to 2%? 2. The company pressured suppliers for lower prices (cost/unit) from $1.50/unit to $1.30/unit? 3. An advertising campaign was created that raised the amount spent on advertising from $5,000 to $8,000 but that campaign resulted in sales revenue rising from 20,000 to 30,000 units? 4. The company moved and restructured-moving to South Carolina where rent is one third of what it currently is, and offered an incentive program to eliminate a number of highly paid middle managers, which would lower salaries from 20,000 to 13,000? Lastly, you can use an Income Statement to calculate widely used financial ratios. One of the most popular is Basic Earnings Per Share, which is Net Income divided by the number of shares outstanding. 10 5. Assume that the company has 14,000 shares outstanding. Using the formula Basic Earnings Per Share = Net Income / Number of Share Outstanding calculate EPS. Go to the "Excel Student Spreadsheets BUS 1011" and click on the tab/worksheet labeled "Income Statement." . . . Change the Column width for Column A to "30," and for Columns B, C, D to "15." If set in inches, change column width for Column A to "2.64" and for Columns B, C, D to "1.32." Going down Column A, type the words: "Revenue" in Cell A3, "Cost of Goods Sold" in A5, etc. as shown on the sample spreadsheet. Be sure to skip Rows 1, 2, 7 and 15 as shown. Also be sure to boldface the headings in Rows 6, 14 and 18 as shown on the sample spreadsheet. Next, type in the numbers in Column C as shown, and the related labels (Units Sold, etc.) in Column D. In the Number section in the toolbar, set 20,000 as "Number", 5 and 1.5 as "Accounting", and 15 as "Percentage." . 9 . . . Now type in Cell B3 the following formula to calculate the Revenue: "=C3 C4" (as Revenue = Units Sold Price/Unit). Next, calculate the cost of Goods Sold: COGS = Units Sold * Cost/Unit, so type "-C3*C5" in Cell Bs. From here we can get the Gross Profit: Gross Profit = Revenue - COGS, so in Cell B6, type "-B3-B5." Cell B8 is the same as Cell B6, so make Cell B8"-36" Now type in the values of the Operating Expenses in Column B as shown (e.g., Advertsing = $5000, etc.) We can calculate the Net Income Before Taxes by extracting the sum of the Operating Expenses from the Gross Profit: in Cell B14, type "-B8-SUM(B10:313)" Make B16 = B14, as both lines are the Net Income Before Taxes. Let's calculate the Taxes next: Taxes = Net Income Before Taxes * Tax Rate, so type in Cell B17: "-B16C17" And finally, calculate the Net Income: Net Income = Net Income Before Taxes - Taxes, so type in Cell B18"=B16-B17
