Excel homework

Assignment:

 

You are the plant engineer and the plant manager has asked you to do an analysis of the utility bills and make a presentable report from the information. The data is required to evaluate cost saving improvements you are considering for the plant by improving energy and water usage:

 

ELECTRIC		Use	Use	Demand	Demand	TOTAL
		KWH	$	KW	$	$
JAN	2013	208,120	$6,565	798	$6,464	$13,029
FEB	2013	302,400	$10,121	819	$6,634	$16,755
MAR	2013	273,000	$10,374	756	$6,124	$16,498
APR	2013	193,200	$8,767	651	$5,273	$14,040
MAY	2013	291,100	$10,377	546	$4,423	$14,800
JUN	2013	391,100	$10,238	525	$4,262	$14,500
JUL	2013	296,100	$13,575	735	$7,445	$21,020
AUG	2013	407,400	$16,902	798	$8,091	$24,993
SEP	2013	420,000	$18,574	798	$8,025	$26,599
OCT	2013	348,600	$17,613	756	$7,634	$25,247
NOV	2013	199,500	$9,739	588	$4,663	$14,402
DEC	2013	239,400	$10,121	672	$5,269	$15,390
TOTALS		3,569,920	$142,966	8,442	$74,307	$217,273

 

 

(Note: the dollar information on this table is set to the dollar value, no cents)

 

-When you enter this table into your spreadsheet, you will need to enter formulas for the Total $ column (Yellow) and Totals row (Green).

 

NATURAL GAS
		MMBTU	Cost $	$/MMBTU
JAN	2013	189	$1,218.00	$6.44
FEB	2013	215	$1,297.00	$6.03
MAR	2013	71	$412.00	$5.80
APR	2013	18	$142.00	$7.89
MAY	2013	2	$22.00	$11.00
JUN	2013	4	$44.00	$11.00
JUL	2013	3	$44.00	$14.67
AUG	2013	3	$44.00	$14.67
SEP	2013	3	$44.00	$14.67
OCT	2013	58	$601.00	$10.36
NOV	2013	76	$820.00	$10.79
DEC	2013	84	$940.00	$11.19
TOTALS		726	$5,628.00	$7.75

 

(Note: the dollar information on this table is set to the cents value)

-When you enter this table into your spreadsheet, you will need to enter formulas for the $/MMBTU column (yellow) and Totals row (green).

 

			WATER	SEWAGE	TOTAL	WATER	SEWAGE
		Cubic	$	$	$	$/CGAL	$/CGAL
		Gallons
JAN	2013	374	$59.00	$64.00	$123.00	$0.16	$0.17
FEB	2013	432	$67.00	$73.00	$140.00	$0.16	$0.17
MAR	2013	640	$86.00	$106.00	$192.00	$0.13	$0.17
APR	2013	1,079	$144.00	$179.00	$323.00	$0.13	$0.17
MAY	2013	1,148	$154.00	$192.00	$346.00	$0.13	$0.17
JUN	2013	729	$98.00	$122.00	$220.00	$0.13	$0.17
JUL	2013	820	$110.00	$137.00	$247.00	$0.13	$0.17
AUG	2013	640	$68.00	$77.00	$145.00	$0.11	$0.12
SEP	2013	540	$78.00	$91.00	$169.00	$0.14	$0.17
OCT	2013	720	$98.00	$120.00	$218.00	$0.14	$0.17
NOV	2013	990	$129.00	$165.00	$294.00	$0.13	$0.17
DEC	2013	850	$113.00	$142.00	$255.00	$0.13	$0.17
TOTALS		8,962	$1,204.00	$1,468.00	$2,672.00	$0.13	$0.16

 

(Note: the dollar information on this table is set to the cents value)

 

-When you enter this table into your spreadsheet, you will need to enter formulas for the Total $ column (yellow), Water $/CGAL column (light blue), Sewage $/CGAL column (grey), and Totals row (green).

 

Enter the three tables above in an Excel spreadsheet, place them on the first worksheet and label the worksheet Bills. Create three additional worksheets, labeled Electric, Gas, and Water. On each of those worksheets,  link  the related information from the first worksheet to a table on the appropriately labeled worksheet and then create a chart to illustrate the information graphically. Make sure that your charts are labeled with a title and legends for the x and y-axis. Use three different types of charts (1 chart for each page). Your goal is to make the information look presentable.