Microsoft Office Tutorials and References
In Depth Information
Case Problem 1
Data File needed for this Case Problem: Altac1.xls
Altac Bicycles Deborah York is a financial consultant for Altac Bicycles, an online seller
of bicycles and bicycle equipment. She has entered some financial information in an
Excel workbook, which she needs for a report she is writing. She has asked that you enter
the remaining data and some formulas to complete the workbook. To complete this task:
Use the skills you have
learned to create a
financial data for a
company that sells
bicycles and bicycle
1. Open the Altac1 workbook located in the Tutorial.01\Cases folder included with
your Data Files, and then save the workbook as Altac2 in the same folder.
2. Insert two rows at the top of the Sheet1 worksheet, and then enter the following text
on two separate lines within cell A1:
3. Insert five rows below the Expenses label in row 9, and then enter the following labels
and data in the appropriate cells in columns B, C, D, and E, beginning on row 10:
Sales and Marketing
Research and Development
Total Operating Expenses
4. Increase the width of column A to 18 characters and the width of column B to 25
characters. Decrease the height of row 1 to 30.
5. Rename Sheet1 “Financial Data”.
6. Using the AutoSum feature, calculate the total operating expenses for each year.
7. For each year, enter formulas that calculate the following values:
• gross margin, which is the difference between the net sales and the cost of sales
• operating income, which is the difference between the gross margin and the total
• pre-tax income, which is the sum of the operating income and the other income
• net income, which is the difference between the pre-tax income and the income taxes
8. Move the contents of range G4:K9 to range A22:E27. ( Note : The worksheet window
will automatically scroll as you move the selection down and to the left.)
9. For each year, enter a formula that calculates the net income per share, which is the
net income divided by the number of shares.
10. Switch to Sheet2, and then enter the following text in the cells indicated:
• Cell A1: Altac Bicycles
• Cell A3: Date:
• Cell B3: current date
• Cell A4: Created By:
• Cell B4: your name
• Cell A5: Purpose:
• Cell B5: Financial data for Altac Bicycles for 2006, 2005, and 2004
11. Increase the width of column A to 18 characters and the width of column B to 15
characters. Rename the sheet as “Documentation” and then move the sheet to the
first position in the workbook.
12. Delete any empty worksheets, and then check the spelling in the workbook. Correct
any errors found.
13. Save the changes you have made to the workbook, print the contents of the entire
workbook in portrait orientation, and then close the workbook.