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
Report DMCA
TutorRating
pallavi

Chat Now!

out of 1971 reviews
More..
amosmm

Chat Now!

out of 766 reviews
More..
PhyzKyd

Chat Now!

out of 1164 reviews
More..
rajdeep77

Chat Now!

out of 721 reviews
More..
sctys

Chat Now!

out of 1600 reviews
More..
sharadgreen

Chat Now!

out of 770 reviews
More..
topnotcher

Chat Now!

out of 766 reviews
More..
XXXIAO

Chat Now!

out of 680 reviews
More..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy