Mr. Joel M.

Bawica| Computer Applications 2012

Skills Review
1. Create a complex formula.
a. Open the file EX B-2.xlsx from the drive and folder where you store your Data Files, then
save it as EX B-Baking Supply Company Inventory.
b. In cell B11, create a complex formula that calculates a 30% decrease in the total number
of cases of cake pans.
c. Use the fill handle to copy this formula into cell C11 through cell E11.
d. Save your work.
2. Insert a function.
a. Use the Sum list arrow to create a formula in cell B13 that averages the number of cases
of cake pans in each storage area.
b. Use the Insert Function button to create a formula in cell B14 that calculates the
maximum number of cases of cake pans in a storage area.
c. Use the Sum list arrow to create a formula in cell B15 that calculates the minimum
number of cases of cake pans in a storage area.
d. Save your work.
3. Type a function.
a. In cell C13, type a formula that includes a function to average the number of cases of pie
pans in each storage area. (Hint: Use Formula AutoComplete to enter the function.)
b. In cell C14, type a formula that includes a function to calculate the maximum number of
cases of pie pans in a storage area.
c. In cell C15, type a formula that includes a function to calculate the minimum number of
cases of pie pans in a storage area.
d. Save your work.

4. Copy and move cell entries.

a. Select the range B3:F3.
b. Copy the selection to the Clipboard.
c. Open the Clipboard task pane, then paste the selection into cell B17.
d. Close the Clipboard task pane, then select the range A4:A9.
e. Use the drag-and-drop method to copy the selection to cell A18. (Hint: The results should
fill the range A18:A23.)
f. Save your work.
5. Understand relative and absolute cell references.
a. Write a brief description of the difference between relative and absolute references.
b. List at least three situations in which you think a business might use an absolute
reference in its calculations. Examples
can include calculations for different types of worksheets, such as time cards, invoices, and
budgets.
6. Copy formulas with relative cell references.
a. Calculate the total in cell F4.
b. Use the Fill button to copy the formula in cell F4 down to cells F5:F8.
c. Select the range C13:C15.
d. Use the fill handle to copy these cells to the range D13:F15.
e. Save your work.
7. Copy formulas with absolute cell references.
a. In cell H1, enter the value 1.575.
b. In cell H4, create a formula that multiplies F4 and an absolute reference to cell H1.
c. Use the fill handle to copy the formula in cell H4 to cells H5 and H6.
d. Use the Copy and Paste buttons to copy the formula in cell H4 to cells H7 and H8.
e. Change the amount in cell H1 to 2.3.
f. Save your work.
8. Round a value with a function.

Page 1
 Mr. Joel M. Bawica| Computer Applications 2012

a. Click cell H4.

b. Edit this formula to include the ROUND function showing zero decimal places.
c. Use the fill handle to copy the formula in cell H4 to the range H5:H8.
d. Enter your name in cell A25, then compare your work to Figure B-23.
e. Save your work, preview the worksheet in Backstage view, then submit your work to your
instructor as directed.
f. Close the workbook, then exit Excel.

Independent Challenge 1
You are

of starting a small express oil change service center. Before you begin, you need to evaluate
what you think your monthly expenses will be. You’ve started a workbook, but need to
complete the entries and add formulas.
a. Open the file EX B-3.xlsx from the drive and folder where you store your Data Files, then
save it as EX B-Express Oil Change Expenses.
b. Make up your own expense data, and enter it in cells B4:B10. (Monthly sales are already
included in the worksheet.)
c. Create a formula in cell C4 that calculates the annual rent.
d. Copy the formula in cell C4 to the range C5:C10.
e. Move the label in cell A15 to cell A14.
f. Create formulas in cells B11 and C11 that total the monthly and annual expenses.
g. Create a formula in cell C13 that calculates annual sales.
h. Create a formula in cell B14 that determines whether you will make a profit or loss, then
copy the formula
into cell C14.
i. Copy the labels in cells B3:C3 to cells E3:F3.

Page 2
 Mr. Joel M. Bawica| Computer Applications 2012

j. Type Projected Increase in cell G1, then type .2 in cell H2.

k. Create a formula in cell E4 that calculates an increase in the monthly rent by the amount
in cell H2. You will be copying this formula to other cells, so you’ll need to use an absolute
reference.
l. Create a formula in cell F4 that calculates the increased annual rent expense based on the
calculation in cell E4.
m. Copy the formulas in cells E4:F4 into cells E5:F10 to calculate the remaining monthly and
annual expenses.
n. Create a formula in cell E11 that calculates the total monthly expenses, then copy that
formula to cell F11.
o. Copy the contents of cells B13:C13 into cells E13:F13.
p. Create formulas in cells E14 and F14 that calculate profit/loss based on the projected
increase in monthly and annual expenses.
q. Change the projected increase to .15, then compare your work to the sample in Figure B-
24.
r. Enter your name in a cell in the worksheet.
s. Save your work, preview the worksheet in Backstage view, submit your work to your
instructor as directed, close the workbook, and exit Excel.

Independent Challenge 2
The Dog Days Daycare Center is a small, growing pet care center that has hired you to
organize its accounting records using Excel. The owners want you to track the company’s

Page 3
 Mr. Joel M. Bawica| Computer Applications 2012

expenses. Before you were hired, one of the bookkeepers began entering last year’s
expenses in a workbook, but the analysis was never completed.
a. Start Excel, open the file EX B-4.xlsx from the drive and folder where you store your Data
Files, then save it as EX B-Dog Days Daycare Center Finances. The worksheet includes
labels for functions such as the average, maximum, and minimum amounts of each of the
expenses in the worksheet.
b. Think about what information would be important for the bookkeeping staff to know.
c. Using the SUM function, create formulas for each expense in the Total column and each
quarter in the Total row.
d. Create formulas for each expense and each quarter in the Average, Maximum, and
Minimum columns and rows using the method of your choice.
e. Save your work, then compare your worksheet to the sample shown in Figure B-25.

Advanced Challenge Exercise

■Create the label Expense categories in cell B19.
■In cell A19, create a formula using the COUNT function that determines the total number of
expense categories listed per quarter.
■Save the workbook.
f. Enter your name in cell A25.
g. Preview the worksheet, then submit your work to your instructor as directed.
h. Close the workbook and exit Excel.

Independent Challenge 3
As the accounting manager of a locally owned business, it is your responsibility to calculate
accrued sales tax payments on a monthly basis and then submit the payments to the state
government. You’ve decided to use an Excel workbook to make these
calculations.

Page 4
 Mr. Joel M. Bawica| Computer Applications 2012

a. Start Excel, then save a new, blank workbook to the drive and folder where you store
your Data Files as EX B-Sales Tax Calculations.
b. Decide on the layout for all columns and rows. The worksheet will contain data for six
stores, which you can name by store number, neighborhood, or another method of your
choice. For each store, you will calculate total sales tax based on the local sales tax rate.
You’ll also calculate total tax owed for all six stores.
c. Make up sales data for all six stores.
d. Enter the rate to be used to calculate the sales tax, using your own local rate.
e. Create formulas to calculate the sales tax owed for each store. If you don’t know the local
tax rate, use 6.5%.
f. Create a formula to total all the owed sales tax, then compare your work to the sample
shown in Figure B-26.

Advanced Challenge Exercise

Page 5
 Mr. Joel M. Bawica| Computer Applications 2012

■Use the ROUND function to eliminate any decimal places in the sales tax figures for each
store and the total due.
■Save the workbook.
g. Add your name to the header.
h. Save your work, preview the worksheet, and submit your work to your instructor as
directed.
i. Close the workbook and exit Excel.

Real Life Independent Challenge

Since your recent promotion at work, you have started thinking about purchasing a home.
As you begin the round of open houses and realtors’ listings, you notice that there are many
fees associated with buying a home. Some fees are based on a percentage of the purchase
price, and others are a flat fee; overall, they seem to represent a substantial amount above
the purchase prices you see listed. You’ve seen five houses so far that interest you; one is
easily affordable, and the remaining four
are all nice, but increasingly more expensive. Although you will be financing the home, the
bottom line is still important to you, so you decide to create an Excel workbook to figure out
the real cost of buying each one.
a. Find out the typical cost or percentage rate of at least three fees that are usually charged
when buying a home and taking out a mortgage. (Hint: If you have access to the Internet
you can research the topic of home buying on the Web, or you can ask friends about
standard rates or percentages for items such as title insurance, credit reports, and
inspection fees.)
b. Start Excel, then save a new, blank workbook to the drive and folder where you store
your Data Files as EX B-Home Purchase Costs.
c. Create labels and enter data for at least three homes. If you enter this information across
the columns in your worksheet, you should have one column for each house, with the
purchase price in the cell below each label. Be sure to enter a different purchase price for
each house.
d. Create labels for the Fees column and for an Amount or Rate column. Enter the
information for each of the fees you have researched.
e. In each house column, enter formulas that calculate the fee for each item. The formulas
(and use of absolute or relative referencing) will vary depending on whether the charges are
a flat fee or based on a percentage of the purchase price.
f. Total the fees for each house, then create formulas that add the total fees to the purchase
price. A sample of what your workbook might look like is shown in Figure B-27.
g. Enter a title for the worksheet in the header.
h. Enter your name in the header, save your work, preview the worksheet, then submit your
work to your instructor as directed.
i. Close the file and exit Excel.

Page 6
 Mr. Joel M. Bawica| Computer Applications 2012

Visual Workshop
Create the worksheet shown in Figure B-28 using the skills you learned in this unit. Save the
workbook as EX B-Expense Analysis to the drive and folder where you store your Data
Files. Enter your name in the header as shown, hide the gridlines, preview the worksheet,
and then submit your work to your instructor as directed.

Page 7