Create an income statement as per above image, under these assumptions the company's income is $32,300. 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 NUMBERSI*** 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. 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. Type All Answers on your Excel Spreadsheet that you will submit. 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. Revenue $ 100,000 20,000 Units Sold 5.00 Price/Unit 1.50 Cost/Unit Cost of Goods Sold Gross Profit 30,000 70,000 70,000 Gross Profit Operating Expenses Advertising Salaries Rent Gas/Electric Net Income Before Taxes 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
