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%|
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...