Excel Income Statement Exercise
Excel Income Statement Exercise: Creating a multiple-page workbook and income statement model.
In this Excel exercise you will create a simple Excel worksheet that will model a simple income statement. In future exercises you will enhance the worksheet by adding additional features. If you are unfamiliar with the features of Microsoft Excel, you should complete a Spreadsheets tutorial before beginning this exercise.
To complete this exercise, you will create two Excel worksheets in an Excel workbook. After starting Excel you should create following table in Sheet 1 of the Excel workbook. Make sure that enter numbers in the second column and use the Excel formatting tools to make your table look like the example below. Put the word ‘Assumptions’ in cell A1. Percentage values should be entered as decimal fractions. For example, enter 1.15 for the 115% and then format the cell to display 115%. Use this approach for Sales Growth Rate, Gross Margin, Operating Expense Growth Rate, and Income Tax Rate.
Assumption Table | | Unit Sales Year 1 | 200 | Unit Sales Price | $ 110 | Sales Growth Rate | 115% | Cost of Goods Sold as % of Price | 60% | Operating Expense Growth Rate | 105% | Income Tax Rate | 50% | …show more content…
After completing the above table, you will create a second table on Sheet 2 of the workbook.
Look at the lower left corner of your Excel window and you will see tabs for Sheet 1, Sheet 2, etc. Click on the ‘Sheet 2’ tab. In Sheet 2 you will create the following worksheet. You should create formulas to calculate Gross Profit, Income Before Tax, and Net Income. Later you will create other formulas. Use the Excel formatting tools to format the worksheet to look like the sample below. (Open the Number format menu by clicking on the lower right boxed arrow. Choose no decimals and format negative values in parentheses and in
red.) Income/Expense Category | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Revenue | 22,000 | | | | | Cost of Sales | 13,200 | | | | | | | | | | | Gross Profit | 8,800 | | | | | | | | | | | Operating Expenses | 10,000 | | | | | | | | | | | Income Before Tax | (1,200) | | | | | Taxes | (600) | | | | | | | | | | | Net Income/Loss | (600) | | | | | Once you have finished both sheets, save the workbook as “your last name” followed by “your first initial - I/S Project” and submit the file to the Income Statement Project Dropbox.
In this Excel exercise you will create a simple Excel worksheet that will model a simple income statement. In future exercises you will enhance the worksheet by adding additional features. If you are unfamiliar with the features of Microsoft Excel, you should complete a Spreadsheets tutorial before beginning this exercise.
To complete this exercise, you will create two Excel worksheets in an Excel workbook. After starting Excel you should create following table in Sheet 1 of the Excel workbook. Make sure that enter numbers in the second column and use the Excel formatting tools to make your table look like the example below. Put the word ‘Assumptions’ in cell A1. Percentage values should be entered as decimal fractions. For example, enter 1.15 for the 115% and then format the cell to display 115%. Use this approach for Sales Growth Rate, Gross Margin, Operating Expense Growth Rate, and Income Tax Rate.
Assumption Table | | Unit Sales Year 1 | 200 | Unit Sales Price | $ 110 | Sales Growth Rate | 115% | Cost of Goods Sold as % of Price | 60% | Operating Expense Growth Rate | 105% | Income Tax Rate | 50% | …show more content…
After completing the above table, you will create a second table on Sheet 2 of the workbook.
Look at the lower left corner of your Excel window and you will see tabs for Sheet 1, Sheet 2, etc. Click on the ‘Sheet 2’ tab. In Sheet 2 you will create the following worksheet. You should create formulas to calculate Gross Profit, Income Before Tax, and Net Income. Later you will create other formulas. Use the Excel formatting tools to format the worksheet to look like the sample below. (Open the Number format menu by clicking on the lower right boxed arrow. Choose no decimals and format negative values in parentheses and in
red.) Income/Expense Category | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Revenue | 22,000 | | | | | Cost of Sales | 13,200 | | | | | | | | | | | Gross Profit | 8,800 | | | | | | | | | | | Operating Expenses | 10,000 | | | | | | | | | | | Income Before Tax | (1,200) | | | | | Taxes | (600) | | | | | | | | | | | Net Income/Loss | (600) | | | | | Once you have finished both sheets, save the workbook as “your last name” followed by “your first initial - I/S Project” and submit the file to the Income Statement Project Dropbox.