INDEPENDENT CHALLENGE A
Many of your friends are purchasing homes, and you are thinking about taking the plunge yourself. 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 three houses so far that interest you; one is moderately priced, one is more expensive, and third is
still more expensive. You decide to create an Excel workbook to figure out the real cost of buying each
a. Find out the typical cost or percentage rate fees that are usually charged when buying a home
and taking out a mortgage.( Hint: If you 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, and then save a new, blank workbook to the drive and folder where you store your
Data Files as Home Purchase Fees.
c. Create labels for the “Fees” column and for the “Rate or Amount” column. Enter the information
on the fess you have researched (different from the example below).
d. Create labels and enter data for 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.
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 by creating formulas that add the total fees to the purchase price.
g. Enter a title for the worksheet in the header.
h. Enter your name in the header, preview the worksheet, and then print it on the single page.
i. Save your work, then close the file and exit Excel.INDEPENDENT CHALLENGE B
Fine Line Writing Instruments is a Chicago-based company that manufactures high quality pens and
markers. As the finance manager, one of your responsibilities is to analyze the monthly reports from
your five district sales offices. Your boss, Joanne Bennington, has just asked you to prepare a
quarterly sales report for an upcoming meeting. Because several top executives will be attending this
meeting, Joanne reminds you that the report must look professional.
a. Start Excel, and then save a new, blank workbook as Fine Line Writing Instruments to the
drive and folder where you store your Data files.
b. Plan a worksheet that shows the company’s sales during the first quarter. Make sure you plan to
The five sales districts are: Northwestern, Midwestern, Southeastern, Southern and
Assume that all pens are the same price (different from the example below).
Enter the number of pens sold (Units Sold) month-by-month for January, February, and
March as shown in the example below.
Calculations that show month-by-month the associated revenues (Sales) for each of the
five district sales offices.
Calculations that show for each district the three month cumulative total units sold, total
sales and district’s share of sales (Percent of Total Sales).
c. Build the worksheet by entering the titles and labels first, and then enter the numbers and
formulas. You can use the information in Table C-4 to get started.
d. Ask yourself the following questions about the organization of the worksheet: How can you
calculate the totals? What formulas can you copy to save time and keystrokes? Do any of these
formulas need to use an absolute reference?
e. Format labels and values, and change the alignment and column widths as necessary.
f. Add the data bars to the total sales column to highlight the fact that the Northeastern district
continues to outpace the other districts.
g. Add a column that calculates a 25% increase in sales dollars. Use an absolute reference in the
h. Enter the title for the worksheet and your name in the header, preview the worksheet, and then
print it in landscape orientation on the single page
i. Print the worksheet formulas in landscape orientation on the single page.