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
enter them.
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
Figure 4–70.
7. Use the concepts and techniques presented in this project to add the data table to the range E1:H21
as follows:
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
cell E5.
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 defi ne the range E4:H21 as a one–input data table. Use cell C4 (Units Sold) as the column
input cell.
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
Title
Cell
Item
Units Sold
C4
55,000
Price per Unit
C5
$24.00
Administrative
C8
$131,394
Rent
C9
$52,000
Marketing
C10
$55,000
Salary and Benefi ts
C11
$247,000
Material Cost per Unit
C14
$8.00
Manufacturing Cost per Unit
C16
$7.00
Continued >
 
Search JabSto ::




Custom Search