Microsoft Office Tutorials and References

In Depth Information

**Case Problems**

5. In the range C11:E14, enter the following expense ﬁ gures (expand the column

widths as necessary to show the text and data values):

2013
2012
2011

Salaries and Wages
1,632 1,481 1,392

Sales and Marketing
2,631 2,112 1,840

Administrative
521 410 375

Research and Development
501 404 281

6. In the nonadjacent range C18:E18;C20:E20;C24:E24, enter the following values for

Other Income, Income Taxes, and Shares, pressing the Enter or Tab key to navigate

from cell to cell in the selected range:

2013
2012
2011

Other Income
341 302 239

Income Taxes
1,225 1,008 821

Shares
3,621 3,001 2,844

7. In the range C8:E8, enter a formula to calculate the gross margin for each year,

where the gross margin is equal to the net sales minus the cost of sales.

8. In the range C15:E15, enter the SUM function to calculate the total operating

expenses for each year, where the total operating expenses equal the sum of the four

expense categories.

9. In the range C17:E17, enter a formula to calculate the operating income for each

year, where operating income is equal to the gross margin minus the total operating

expenses.

10. In the range C19:E19, enter a formula to calculate the pretax income for each year,

where pretax income is equal to the operating income plus other income.

11. In the range C22:E22, enter a formula to calculate the company’s net income for

each year, where net income is equal to the pretax income minus income taxes.

12. In the range C25:E25, enter a formula to calculate the earnings per share for each

year, where earnings per share is equal to the net income divided by the number

of shares.

13. Use the spelling checker to correct and replace any spelling errors in the worksheet.

Ignore the spelling of “Altac.”

14. In cell A18, use Edit mode to capitalize the word “income.”

15. Increase the width of column A to 18 characters and increase the width of column B

to 25 characters. Autoﬁ t the height of row 1.

16. Rename the Sheet1 worksheet as
Income Statement
, rename the Sheet2 worksheet

as
Documentation
and move it to the beginning of the workbook, and then delete

the Sheet3 worksheet.

17. In the Documentation worksheet, enter the following text and values:

Cell A1:
Altac Bicycles

Cell A3:
Author
Cell B3: your name

Cell A4:
Date
Cell B4: the current date

Cell A5:
Purpose
Cell B5:
Income statement for Altac Bicycles for 2011

through 2013

18. Save the workbook, preview the workbook and make sure each worksheet in

portrait orientation ﬁ ts on one page in the printout, and then print the entire workbook.

Close the workbook, and then submit the ﬁ nished workbook and printouts to your

instructor.