Case Problem 3
Data File needed for this Case Problem: M-Fresh.xlsx
M-Fresh Water Company M-Fresh Water Company is a small independent water com-
pany in Miami, Oklahoma, that provides water to commercial customers throughout
the region, delivering the water supply through pipelines, on-demand storage tanks, and
bottles. Customers of M-Fresh Water range from government of?ces to nonpro?t organi-
zations to commercial retail shops and markets. Town regulations tax the latter group of
commercial customers based on their usage, whereas nonpro?t and government of?ces
are not taxed. Furthermore, M-Fresh Water will occasionally waive a water bill based on
its charitable-giving policy. Dawes Cado is in charge of the billing system that must take
into account these business rules and assure accurate and on-time billing, which occurs
quarterly. Complete the following:
1. Open the M-Fresh workbook located in the Excel7\Case3 folder included with your
Data Files, save the workbook as Water Bill in the same folder, and then, in the
Documentation worksheet, enter your name and the date.
2. In the Quarterly Data worksheet, create an Excel table for the range A1:F73, and
name the table WaterData. Remove the ?lter arrows. Format the data in the Gal
Used column with the Comma Style number format and no decimal places.
3. Add a column named Gal Billed to the table, and then enter a formula to calculate
the number of gallons billed based on the following rules: If a customer’s bill is
waived (Bill Waived column) or the number of gallons used is less than 25,000 (Gal
Used column), display 0 in Gal Billed column; otherwise, display the value from the
Gal Used column in the Gal Billed column.
4. Add a column named Water Bill to the table, and then enter a formula to calcu-
late the water bill based on the following rules: The billing rate varies based on
the type of customer (Cust Type column). The billing rate is $3, $2, or $1.50 per
thousand gallons billed, depending on the customer type (see the lookup table in
cells B2:C5 of the Billing Rate worksheet). For example, a commercial customer
using 75,000 gallons has a water bill of $225 (75 X $3), whereas a government cus-
tomer using 100,000 gallons pays $150 (100 X $1.50). A commercial customer using
15,000 gallons has a water bill of $0 (see Gal Used rule in question 3).
5. Add a column named Tax to the table, and then enter a formula to calculate the tax
based on the following rule: If a customer pays tax (Taxable column), then multiply
the Water Bill by 3.5 percent; otherwise, the tax is 0. The tax rate is stored in cell T1.
6. Add a column named Total Bill to the table, and then enter a formula to calculate
the bill amount by adding the Water Bill column and the Tax column.
7. Improve the formatting of the number ?elds in the WaterData table.
8. Make a copy of the Quarterly Data worksheet, rename the copied worksheet as
Q2-Q8, and then add a Totals row to the Excel table displaying totals for the Gal
Used, Gal Billed, Tax, and Total Bill columns.
9. Make another copy of the Quarterly Data worksheet, rename the copied worksheet
as Q9, and then use conditional formatting to highlight the top 15 percent of the
Total Bill amounts with a yellow background color. Filter the table so that only the
top 15 percent are displayed. Sort the ?ltered table by largest to smallest.
10. Insert a new worksheet, rename the worksheet as Billing Summary, and then create
the report shown in Figure 7-43, using COUNTIF, AVERAGEIF, and SUMIF functions
to prepare the report.
11. Make another copy of the Quarterly Data worksheet, and then rename the copied
worksheet as Q11. Add a custom ?lter to the table to display only those organiza-
tions that have “church” or “center” as part of the customer name.
12. Make another copy of the Quarterly Data worksheet, and then rename the copied
worksheet as Q12. Use the lookup table in cells E2:H3 of the Billing Rate worksheet
and the HLOOKUP function to modify the formula to calculate the Water Bill col-
umn (column H).
13. Save and close the workbook. Submit the ?nished workbook to your instructor,
either in printed or electronic form, as requested.
|Due By (Pacific Time)||10/27/2013 01:00 pm|
out of 1971 reviews
out of 766 reviews
out of 1164 reviews
out of 721 reviews
out of 1600 reviews
out of 770 reviews
out of 766 reviews
out of 680 reviews