Question;CIS 201;Open;the enclosed file in Excel.;Convert;the worksheet in the ?Main Worksheet? into a table.;Turn;the ?Totals? row on and have column A display the count and column E;display the total sum (30 pts).;Column;E represents annual usage of water. Water bills and water tax will be;computed annually as well.;Use;appropriate lookup tables from the ?Rates? worksheet to compute values for;columns: (30pts);?Water Bill?. Your formulas must reflect;the fact whether entities are or are not exempt from paying water bills.;Note that rates here are per gallon.;?Water;Tax?. Your formulas must reflect the fact whether entities are or are not;exempt from paying taxes. Note that rates here apply to the amount of;water bill.;Annual;usage is going to be paid in monthly installments as if it was a loan. The;loan amount is equal to the sum of water bill and water tax. ?Monthly;Payments? column will contain figures of monthly payments that will include;an interest rate based on the Customer Type.;For;commercial customers the annual rate is 5% and for all other customers there;is no interest.;For;all customers the term of financing is 12 months. (30pts);Create;an auxiliary table for the purpose of ?Monthly Payments? column. Not a;lookup table.;Use;absolute values in your formulas wherever possible and DO NOT enter;explicit numbers in formulas. (10pts);Create;a table but only from two columns: ?Taxable? and ?Gallons Used?. (15pts);Make;sure that in the bottom there is a Total Row.;For;the ?Taxable? column have count computed, and for the other column have;average computed.;Merge;the three cells to the left of the tabled columns (in the total row) and;type there: Number of Customers.;Similarly, merge the three cells to the right of the tabled columns and;type there: Average water bill.;Create;a Pivot Table out of the entire worksheet except the total row. Place it;in column J, row 3 on the same worksheet. In the pivot table?s rows have;Customer Types showing. For the pivot table?s values have the highest gallons;used and average water bill showing. (15pts);Total;points available=100).
Paper#40285 | Written in 18-Jul-2015Price : $42