Let's build on the last ample, let's assume that the company has three store location. Queen, Brooklyn and one from which enly went was shown above Copying how Start by copying hows and 2 to Rows and 6. To do so, select Row 1 from the extremelete-hand, outer cell and drag down to Row 2. Right click to Copy Select Row and right click again to Paste Select Row 6 by dicking in the extremelet-hand, outer cell Copy the comment in Row to lines and Type the numbers in B7 and 18 as well as and as shown in the image below. DO NOT pe the numbers incoln Al Add a thColumn titled "Store Location as shown below. Profit Revenue Expenses $ 30,000 $ 100,000 $ 70,000 Return for a minute to the very first exercise-thece where you created the equation "PROFIT RIVENUE EXPENSES.That equation forms the basis for one of the most widely used financial statements in counting 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 $ 20,000 Bold 5.00 PM 1.50 Cast 10.000 Cost of Goods sold Grow Profit $ $ 5 70,000 Gros Prod Operating per Advertising $ 5.000 20.000 Profit Revenue Expenses 30,000 $ 100,000 $ 70,000 50,000 $ 160,000 $ 110,000 25,000 $ 90,000 $ 65,000 Store Location Queens Brooklyn Bronx $ 1.000 $ S $ Gavec Het income Before Net income before 5 000 100 15 Tale The income 3 $ 105,000 $ 350,000 $ 245,000 Sums $ 35,000 $ 116,667 $ 81,667 Averages Calculating Sums To calculate the "Sum of Profits," type-SUMA ARY" In Cell A10 Calculating Average To calculate the "Average of Profits, type "AVERAGE AART" in Cell All Select and then Copy Celis A10 and All and Paste them into Ces 10, 11 and Coil . Add the headings in Cels 10 and Dll as shown 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.CD to 15. set in inches, change columns with for Column A to *2.64" and for Columns Dt 132. Going down Column A type the words: Revenue in Cell A), "Cost of Goods Solfin AS, etc. as shown on the sample spreadsheet Be sure to skip Rowi 1, 2, 7 and 15 as shown. Also be sure to boditace the headings in Rom 6,14 and on the sample spreadsheet - Nettve is the numbers in Column Cas shown, and the related labels Units Sold, etc) in Column in the Number section in the toolbar, set 20.000 sumber", 5 and 15 as "Accounting, and 15 a "Percentage . S. Assume that the company has 14,000 shares outstanding. Using the formula Base Earnings Per Share Net Income / Number of Share Outstanding calculate EPS Now type in Cell 3 the following formula to calculate the Revenue: C39C4" (as Revenue Un Sold Print Next, calculate the cost of Goods Sold:COGS- Un soldCount, so types in Cell From here we can get the Gross Proft: Gross Profit Revenge COGS, so in Celltype--as Cell is the same as Cell M, so make 86 Now type in the values of the Operating Expenses in Column B as shown to Advertsing $5.000, ete We can calculate the Net Income Before Trees by extracting the sum of the Operating Expenses from the Gross Protein Cell B14, type--SUMER 10-18 Make 816-814, both lines are the Net Income before Tres Les calculate the next Tes Net Income Before Tames Tax Rate, so type in Cell B17 -816*017 And finally, Caleate the Net Income: Net Income Net Income Before TT, to type in Cell 818-816 817 As shown in the sample spreadsheet under these assumptions the company's income is $12,100. Inserting Rows ertan aditional Row in the income Statement between series and entCallie Transportation Cost and it at 4,000. Under these sumption the company's Net Income should have decreased to $21.300 Using of the income Statement as a template, manipulate the entire to calculate Net Incerne for each the following som ***AFTER LACH QUESTION, KEEP WRONG WITH THE NEW NUMBERS, DO NOT GO BACK TO THE ORIGINAL NUMBERS What would the company's net income (bottom line) bei 1. Taxes could be reduced to 27 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 popularis Basic Earnings Per Share, which is Net Income divided by the number of shares outstanding 10 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 $ $ $ 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 Gas/Electric Net Income Before Taxes $ $ 1,000 38,000 Net Income Before Taxes Taxes $ $ $ 38,000 5,700 32,300 15% Tax Rate Net Income 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 AS, 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." . . . . 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 B5. 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 "=B6". 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: "=B16*C17" And finally, calculate the Net Income: Net Income = Net Income Before Taxes - Taxes, so type in Cell B18 "=B16-B17" . . As shown in the sample spreadsheet, under these assumptions the company's income is $32,300. 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? 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 I 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. Let's build on the last ample, let's assume that the company has three store location. Queen, Brooklyn and one from which enly went was shown above Copying how Start by copying hows and 2 to Rows and 6. To do so, select Row 1 from the extremelete-hand, outer cell and drag down to Row 2. Right click to Copy Select Row and right click again to Paste Select Row 6 by dicking in the extremelet-hand, outer cell Copy the comment in Row to lines and Type the numbers in B7 and 18 as well as and as shown in the image below. DO NOT pe the numbers incoln Al Add a thColumn titled "Store Location as shown below. Profit Revenue Expenses $ 30,000 $ 100,000 $ 70,000 Return for a minute to the very first exercise-thece where you created the equation "PROFIT RIVENUE EXPENSES.That equation forms the basis for one of the most widely used financial statements in counting 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 $ 20,000 Bold 5.00 PM 1.50 Cast 10.000 Cost of Goods sold Grow Profit $ $ 5 70,000 Gros Prod Operating per Advertising $ 5.000 20.000 Profit Revenue Expenses 30,000 $ 100,000 $ 70,000 50,000 $ 160,000 $ 110,000 25,000 $ 90,000 $ 65,000 Store Location Queens Brooklyn Bronx $ 1.000 $ S $ Gavec Het income Before Net income before 5 000 100 15 Tale The income 3 $ 105,000 $ 350,000 $ 245,000 Sums $ 35,000 $ 116,667 $ 81,667 Averages Calculating Sums To calculate the "Sum of Profits," type-SUMA ARY" In Cell A10 Calculating Average To calculate the "Average of Profits, type "AVERAGE AART" in Cell All Select and then Copy Celis A10 and All and Paste them into Ces 10, 11 and Coil . Add the headings in Cels 10 and Dll as shown 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.CD to 15. set in inches, change columns with for Column A to *2.64" and for Columns Dt 132. Going down Column A type the words: Revenue in Cell A), "Cost of Goods Solfin AS, etc. as shown on the sample spreadsheet Be sure to skip Rowi 1, 2, 7 and 15 as shown. Also be sure to boditace the headings in Rom 6,14 and on the sample spreadsheet - Nettve is the numbers in Column Cas shown, and the related labels Units Sold, etc) in Column in the Number section in the toolbar, set 20.000 sumber", 5 and 15 as "Accounting, and 15 a "Percentage . S. Assume that the company has 14,000 shares outstanding. Using the formula Base Earnings Per Share Net Income / Number of Share Outstanding calculate EPS Now type in Cell 3 the following formula to calculate the Revenue: C39C4" (as Revenue Un Sold Print Next, calculate the cost of Goods Sold:COGS- Un soldCount, so types in Cell From here we can get the Gross Proft: Gross Profit Revenge COGS, so in Celltype--as Cell is the same as Cell M, so make 86 Now type in the values of the Operating Expenses in Column B as shown to Advertsing $5.000, ete We can calculate the Net Income Before Trees by extracting the sum of the Operating Expenses from the Gross Protein Cell B14, type--SUMER 10-18 Make 816-814, both lines are the Net Income before Tres Les calculate the next Tes Net Income Before Tames Tax Rate, so type in Cell B17 -816*017 And finally, Caleate the Net Income: Net Income Net Income Before TT, to type in Cell 818-816 817 As shown in the sample spreadsheet under these assumptions the company's income is $12,100. Inserting Rows ertan aditional Row in the income Statement between series and entCallie Transportation Cost and it at 4,000. Under these sumption the company's Net Income should have decreased to $21.300 Using of the income Statement as a template, manipulate the entire to calculate Net Incerne for each the following som ***AFTER LACH QUESTION, KEEP WRONG WITH THE NEW NUMBERS, DO NOT GO BACK TO THE ORIGINAL NUMBERS What would the company's net income (bottom line) bei 1. Taxes could be reduced to 27 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 popularis Basic Earnings Per Share, which is Net Income divided by the number of shares outstanding 10 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 $ $ $ 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 Gas/Electric Net Income Before Taxes $ $ 1,000 38,000 Net Income Before Taxes Taxes $ $ $ 38,000 5,700 32,300 15% Tax Rate Net Income 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 AS, 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." . . . . 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 B5. 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 "=B6". 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: "=B16*C17" And finally, calculate the Net Income: Net Income = Net Income Before Taxes - Taxes, so type in Cell B18 "=B16-B17" . . As shown in the sample spreadsheet, under these assumptions the company's income is $32,300. 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? 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 I 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