Microsoft Office Tutorials and References
In Depth Information
In the Lab
2. Enter the worksheet titles: The Bean Bag Game Company in cell A1, and Quarterly
Income Statement in cell A2. Apply the Title cell style to both cells. Change the font sizes in
cells A1 and A2 to 24 and 16 respectively. One at a time, merge and center cells A1 and A2 across
columns A through C. Change the background color of cells A1 and A2 to Orange, Accent 3 (column 7,
row 1 on the Fill Color palette). Change the font color to White, Background 1 (column 1, row 1
on the Font Color palette). Add a thick border to the range A1:A2.
3. Enter the row titles in columns A and
B as shown in Figure 4–70. Change
the font size of the row titles in
column A to 12–point and change
the font color to Orange, Accent 3.
Add the data shown in Table 4–8 in
column C. Use the dollar sign ($) and
comma (,) format symbols to format
the numbers in column C as you
4. Use the Create from Selection button
on the Formulas tab on the Ribbon to
assign the row titles in column B in the
range B4:B21 to the adjacent cells in
column C. Use these names to enter the following formulas in column C:
a. Total Revenue (cell C6) = Units Sold * Price per Unit
(or =C4 * C5)
b. Total Fixed Expenses (cell C12) = SUM(C8:C11)
c. Total Material Cost (cell C15) = Units Sold * Material Cost per Unit (or =C4 * C14)
d. Total Manufacturing Cost (cell C17) = Units Sold * Manufacturing Cost per Unit (or =C4 * C16)
e. Total Variable Expenses (cell C18) = Total Material Cost + Total Manufacturing Cost (or =C15 + C17)
f. Total Expenses (cell C20) = Total Fixed Expenses + Total Variable Expense (or =C12 + C18)
g. Operating Income (cell C21) = Total Revenue – Total Expenses (or =C6 – C20)
5. If necessary, use the Format Painter button on the Home tab on the Ribbon to assign the
Currency style format in cell C8 to the unformatted dollar amounts in column C.
6. Add a thick orange bottom border to the ranges B5:C5, B11:C11, and B17:C17 as shown in
7. Use the concepts and techniques presented in this project to add the data table to the range E1:H21
a. Add the data table titles and format them as shown in Figure 4–70.
b. Create the series in column E from 40,000 to 120,000 in increments of 5,000, beginning in
c. Enter the formula =c6 in cell F4. Enter the formula =c20 in cell G4. Enter the formula =c21
in cell H4. If necessary, adjust the column widths.
d. Use the Data Table command in the What–If Analysis gallery on the Data tab on the Ribbon
to deﬁ ne the range E4:H21 as a one–input data table. Use cell C4 (Units Sold) as the column
e. Use the Format Cells command on the shortcut menu to format the range F5:H21 to the
Comma style format with no decimal places and negative numbers in red with parentheses.
Add a medium outline border and light vertical borders to the range E1:H21.
Table 4–8 Annual Income Data
Price per Unit
Salary and Beneﬁ ts
Material Cost per Unit
Manufacturing Cost per Unit