# Project #15389 - excel project

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.

 Subject General Due By (Pacific Time) 10/27/2013 01:00 pm
TutorRating
pallavi

Chat Now!

out of 1971 reviews
amosmm

Chat Now!

out of 766 reviews
PhyzKyd

Chat Now!

out of 1164 reviews
rajdeep77

Chat Now!

out of 721 reviews
sctys

Chat Now!

out of 1600 reviews

Chat Now!

out of 770 reviews
topnotcher

Chat Now!

out of 766 reviews
XXXIAO

Chat Now!

out of 680 reviews