Assignment 1- KPI

profileabparsley
EmployeeSatisfactionPart1Student.xlsx
Home>Business & Finance homework help>Assignment 1- KPI

Raw Data

Name Pay Responsibilities Supervision Gender Department
Rudolph 2 1 1 Male Accounting
Olga 2 1 1 Female Accounting Instructions
Ernest 2 1 1 Male Accounting
Emily 2 1 1 Female Accounting The sheet labeled "Raw Data" lists 366 employees and their rating (1-5) of their satisfaction with their Pay, Responsibilities, and Supervision. A rating of 5 is the highest satisfaction.
Bobby 2 1 1 Male Accounting Raw Data also includes the Gender and Department for each employee.
Benjamin 2 1 1 Male Accounting
Beatrice 2 1 1 Female Accounting Insert a new column in E
Keith 2 1 1 Male Accounting Label this new column "Overall Satisfaction Rating"
Hilda 2 1 1 Female Accounting For each employee, compute the Overall Satisfaction Rating as the Average of Pay, Responsibilities, and Supervision.
Leslie 3 1 1 Male Accounting Format Overall Satisfaction Rating to one decimal place.
Curtis 3 1 1 Male Accounting
Alice 3 1 1 Female Accounting On a New sheet titled Results, create a Pivot Chart & Pivot Table
Sophie 3 1 1 Female Accounting Assign Gender to Columns, Department to rows, and Pay to Values. Change the value field setting from Sum to Average if necessary.
Sally 3 1 1 Female Accounting Sort the departments in descending order of satisfaction.
Melvin 3 1 1 Male Accounting Create a title for the chart, which includes your last name
Pearl 4 1 1 Female Accounting Be sure your chart includes a legend for male & female employees, change male color to blue and female to orange
Johnny 4 1 1 Male Accounting Be sure to include axis titles
Eunice 4 1 1 Female Accounting Format the vertical axis for a max of 5 and major tick marks at 1 and one decimal place.
Opal 2 1 2 Female Accounting
Julia 2 1 2 Female Accounting Create a new sheet titled "Graphs".
Jimmie 2 1 2 Male Accounting Copy & Paste as Picture your graph of Pay Satisfaction
Esther 2 1 2 Female Accounting
Albert 2 1 2 Male Accounting Alter your Pivot chart/table to display Responsibilities Satisfaction. Change titles as needed.
Mike 2 1 2 Male Accounting Paste this chart on the Graphs sheet
Marion 2 1 2 Male Accounting
Josephine 2 1 2 Female Accounting Alter your Pivot chart/table to display Supervision Satisfaction. Change titles as needed.
Ida 2 1 2 Female Accounting Paste this chart on the Graphs sheet
Gerald 2 1 2 Male Accounting
Caroline 2 1 2 Female Accounting Alter your Pivot chart/table to display Overall Satisfaction. Change titles as needed.
Alberta 2 1 2 Female Accounting Paste this chart on the Graphs sheet
Leroy 3 1 2 Male Accounting Leave Results sheet with the Pivot Table & Chart displaying the Overall Satisfaction.
Anita 3 1 2 Female Accounting
Mildred 4 1 2 Female Accounting
Beulah 4 1 2 Female Accounting
Ada 4 1 2 Female Accounting
Clayton 2 1 2 Male Accounting
Wayne 3 1 2 Male Accounting
Marie 4 1 2 Female Accounting
Lula 4 1 2 Female Accounting
Harriet 4 1 2 Female Accounting
Eugene 4 1 2 Male Accounting
Marilyn 2 2 1 Female Accounting
Wilbur 2 2 1 Male Accounting
Phyllis 2 2 1 Female Accounting
Nicholas 2 2 1 Male Accounting
Edna 2 2 1 Female Accounting
Doris 3 2 1 Female Accounting
Tony 3 2 1 Male Accounting
Rosemary 2 2 2 Female Accounting
Frances 2 2 2 Female Accounting
Milton 2 2 2 Male Accounting
Gladys 2 2 2 Female Accounting
Lola 3 2 2 Female Accounting
Bennie 3 2 2 Male Accounting
Audrey 3 2 2 Female Accounting
Arnold 3 2 2 Male Accounting
Mary 4 2 2 Female Accounting
Annie 2 2 2 Female Accounting
Ben 3 2 2 Male Accounting
Sidney 4 2 2 Male Accounting
Nathaniel 4 2 2 Male Accounting
Rosa 3 3 1 Female Accounting
Marian 4 3 1 Female Accounting
Christine 4 3 1 Female Accounting
Billy 4 3 1 Male Accounting
Anne 4 3 1 Female Accounting
Laverne 2 3 2 Female Accounting
Bruce 2 3 2 Male Accounting
Anthony 2 3 2 Male Accounting
William 4 3 2 Male Accounting
Warren 2 3 2 Male Accounting
Vera 3 3 2 Female Accounting
Steve 3 3 2 Male Accounting
Eleanor 3 3 2 Female Accounting
Paul 4 3 2 Male Accounting
Lloyd 4 3 2 Male Accounting
Kenneth 4 3 2 Male Accounting
Gloria 4 3 3 Female Accounting
Margaret 4 4 2 Female Accounting
Roger 3 4 3 Male Accounting
Robert 3 4 3 Male Accounting
James 4 4 3 Male Accounting
Marion 5 4 4 Male Accounting
Willie 4 2 2 Male Engineering
Billie 4 3 2 Male Engineering
Lucille 5 3 2 Female Engineering
Geneva 5 3 2 Female Engineering
Martha 4 3 3 Female Engineering
Evelyn 4 3 3 Female Engineering
Bob 4 3 3 Male Engineering
Dorothy 4 3 4 Female Engineering
Florence 4 3 4 Female Engineering
Stephen 5 4 2 Male Engineering
Rose 4 4 3 Female Engineering
Martin 4 4 3 Male Engineering
Lyle 4 4 3 Male Engineering
Wesley 5 4 3 Male Engineering
Wanda 5 4 3 Female Engineering
Allen 5 4 3 Male Engineering
Shirley 5 4 3 Female Engineering
Louise 5 4 3 Female Engineering
Bill 4 4 4 Male Engineering
Willis 5 4 4 Male Engineering
Nellie 5 4 4 Female Engineering
Herman 5 4 4 Male Engineering
Gordon 5 4 4 Male Engineering
Gertrude 5 4 4 Female Engineering
Virgil 5 4 4 Male Engineering
Nancy 5 4 5 Female Engineering
Lorraine 5 4 5 Female Engineering
Billie 5 4 5 Male Engineering
Madeline 5 4 5 Female Engineering
Flora 5 4 5 Female Engineering
Joyce 5 4 5 Female Engineering
Dolores 5 4 5 Female Engineering
Delores 5 4 5 Female Engineering
Samuel 4 5 2 Male Engineering
Patricia 4 5 3 Female Engineering
Daniel 4 5 3 Male Engineering
Viola 4 5 3 Female Engineering
Willie 5 5 3 Male Engineering
Lena 5 5 3 Female Engineering
Junior 5 5 3 Male Engineering
Fred 5 5 3 Male Engineering
Clyde 5 5 3 Male Engineering
Catherine 5 5 3 Female Engineering
Ruby 5 5 3 Female Engineering
Leo 5 5 3 Male Engineering
June 5 5 3 Female Engineering
Elsie 5 5 3 Female Engineering
Oscar 5 5 3 Male Engineering
Howard 5 5 3 Male Engineering
Alex 5 5 3 Male Engineering
Matthew 5 5 3 Male Engineering
Lewis 5 5 3 Male Engineering
Glenn 5 5 3 Male Engineering
Francis 5 5 3 Male Engineering
Alexander 5 5 3 Male Engineering
Joseph 4 5 4 Male Engineering
Edwin 4 5 4 Male Engineering
Alfred 4 5 4 Male Engineering
Rachel 4 5 4 Female Engineering
Irvin 4 5 4 Male Engineering
Geraldine 4 5 4 Female Engineering
Russell 4 5 4 Male Engineering
Mae 4 5 4 Female Engineering
Lee 4 5 4 Male Engineering
Jeanette 4 5 4 Female Engineering
Maria 5 5 5 Female Engineering
Jessie 5 5 5 Male Engineering
Constance 5 5 5 Female Engineering
Alma 5 5 5 Female Engineering
Maurice 5 5 5 Male Engineering
Eddie 5 5 5 Male Engineering
Marcella 5 5 5 Female Engineering
Johnnie 5 5 5 Male Engineering
Morris 5 5 5 Male Engineering
Jimmy 5 5 5 Male Engineering
Richard 5 5 5 Male Engineering
Katherine 5 5 5 Female Engineering
Jane 5 5 5 Female Engineering
Dale 5 5 5 Male Engineering
Jessie 5 5 5 Male Engineering
Clifton 4 1 4 Male HR
Dennis 4 1 3 Male HR
Clinton 4 1 3 Male HR
Emma 2 1 4 Female HR
Archie 3 1 4 Male HR
Joe 3 1 3 Male HR
Roy 4 1 4 Male HR
Jeanne 4 1 4 Female HR
Marshall 4 1 4 Male HR
Frank 4 1 4 Male HR
Clifford 4 1 4 Male HR
Stanley 2 2 2 Male HR
Pauline 2 1 4 Female HR
Verna 3 2 3 Female HR
Phillip 3 1 3 Male HR
Peter 3 2 4 Male HR
Marguerite 3 1 4 Female HR
Philip 3 2 2 Male HR
Lillian 3 1 3 Female HR
Jerome 3 2 4 Male HR
Vincent 4 2 3 Male HR
Mattie 4 1 3 Female HR
Mable 4 2 4 Female HR
Horace 4 2 3 Male HR
Virginia 4 1 4 Female HR
David 4 2 4 Male HR
Elaine 3 2 3 Female HR
Bertha 3 1 4 Female HR
Wallace 3 2 4 Male HR
Loretta 4 2 3 Female HR
Everett 4 1 4 Male HR
Grace 4 2 3 Female HR
Thelma 4 2 3 Female HR
Carl 4 1 3 Male HR
Douglas 4 2 4 Male HR
Marjorie 5 2 4 Female HR
George 5 2 3 Male HR
Jose 3 1 4 Male HR
Sam 3 2 3 Male HR
Cecil 3 2 4 Male HR
Genevieve 4 2 3 Female HR
Rebecca 4 1 3 Female HR
Carolyn 4 1 4 Female HR
Andrew 4 2 3 Male HR
Jerry 4 2 4 Male HR
Sarah 5 2 5 Female HR
Dewey 3 1 4 Male HR
Rita 4 1 3 Female HR
Guy 4 1 4 Male HR
Edgar 4 1 3 Male HR
Jacob 4 1 4 Male HR
Margie 3 1 3 Female HR
Janet 4 2 3 Female HR
Gene 4 2 3 Male HR
Dora 4 2 4 Female HR
Bette 4 2 3 Female HR
Carrie 4 2 4 Female HR
Julius 4 3 4 Male HR
Otis 4 2 4 Male HR
Oliver 4 2 4 Male HR
Daisy 4 2 4 Female HR
Salvatore 5 2 4 Male HR
Kathleen 4 2 3 Female HR
Earl 4 2 3 Male HR
Duane 3 2 2 Male Marketing
Alton 4 2 2 Male Marketing
Cora 4 2 2 Female Marketing
Lillie 3 2 2 Female Marketing
Irene 3 2 2 Female Marketing
Frederick 3 2 2 Male Marketing
Bernice 3 2 2 Female Marketing
Harry 4 2 2 Male Marketing
Calvin 4 2 2 Male Marketing
Fannie 4 2 2 Female Marketing
Vivian 3 2 3 Female Marketing
Violet 3 2 3 Female Marketing
Arthur 3 2 3 Male Marketing
Manuel 4 2 3 Male Marketing
Jay 4 2 3 Male Marketing
Ruth 4 2 3 Female Marketing
Juanita 4 2 3 Female Marketing
Homer 4 2 3 Male Marketing
Hazel 4 2 3 Female Marketing
Harold 4 2 3 Male Marketing
Gilbert 3 3 2 Male Marketing
Erma 3 3 2 Female Marketing
Ethel 4 3 2 Female Marketing
Ellen 4 3 2 Female Marketing
Jacqueline 4 3 2 Female Marketing
Leland 3 3 2 Male Marketing
Dan 3 3 2 Male Marketing
Alvin 3 3 2 Male Marketing
Norma 4 3 2 Female Marketing
Lawrence 4 3 2 Male Marketing
Michael 4 3 2 Male Marketing
Leonard 4 3 2 Male Marketing
Herbert 4 3 2 Male Marketing
Dean 4 3 2 Male Marketing
Lester 3 3 3 Male Marketing
Delbert 3 3 3 Male Marketing
Chester 3 3 3 Male Marketing
Walter 4 3 3 Male Marketing
Hugh 4 3 3 Male Marketing
Ella 4 3 3 Female Marketing
Edmund 4 3 3 Male Marketing
Charles 4 3 3 Male Marketing
Ann 4 3 3 Female Marketing
Vernon 4 3 3 Male Marketing
Myrtle 4 3 3 Female Marketing
Muriel 4 3 3 Female Marketing
Marvin 4 3 3 Male Marketing
Luther 5 3 3 Male Marketing
Wilma 4 4 2 Female Marketing
Sylvia 4 4 2 Female Marketing
Theresa 4 4 2 Female Marketing
Rosie 4 4 2 Female Marketing
Patrick 4 4 2 Male Marketing
Donald 4 4 2 Male Marketing
Kathryn 3 4 2 Female Marketing
Jesse 3 4 2 Male Marketing
Clarence 3 4 2 Male Marketing
Floyd 4 4 2 Male Marketing
Angelo 4 4 2 Male Marketing
Victor 4 4 2 Male Marketing
Minnie 4 4 2 Female Marketing
Laura 4 4 2 Female Marketing
Larry 4 4 2 Male Marketing
Hattie 5 4 2 Female Marketing
Ronald 4 4 3 Male Marketing
Georgia 4 4 3 Female Marketing
Franklin 4 4 3 Male Marketing
Ray 4 4 3 Male Marketing
Norman 4 4 3 Male Marketing
Joan 4 4 3 Female Marketing
Ervin 4 5 2 Male Marketing
Ralph 4 5 3 Male Marketing
Leona 4 5 4 Female Marketing
Charlotte 4 5 4 Female Marketing
Thomas 5 5 5 Male Marketing
Don 3 2 2 Male Supply Chain
Claude 4 2 2 Male Supply Chain
Willard 4 3 2 Male Supply Chain
Henry 4 3 2 Male Supply Chain
Blanche 4 3 2 Female Supply Chain
Nora 4 3 2 Female Supply Chain
Ivan 4 3 2 Male Supply Chain
Inez 4 3 2 Female Supply Chain
Carol 4 3 2 Female Supply Chain
Miriam 4 3 3 Female Supply Chain
Jennie 4 3 3 Female Supply Chain
Wilbert 4 3 4 Male Supply Chain
Hubert 4 3 4 Male Supply Chain
Velma 4 3 4 Female Supply Chain
Maxine 4 3 4 Female Supply Chain
Sara 5 3 4 Female Supply Chain
Eva 4 4 2 Female Supply Chain
Jean 4 4 2 Female Supply Chain
Claire 4 4 2 Female Supply Chain
Max 5 4 2 Male Supply Chain
Mamie 5 4 2 Female Supply Chain
Agnes 4 4 3 Female Supply Chain
Stella 4 4 3 Female Supply Chain
Clara 4 4 3 Female Supply Chain
Orville 5 4 3 Male Supply Chain
Merle 5 4 3 Male Supply Chain
Harvey 5 4 3 Male Supply Chain
Charlie 5 4 3 Male Supply Chain
Bonnie 5 4 3 Female Supply Chain
Donna 4 4 4 Female Supply Chain
Barbara 4 4 4 Female Supply Chain
Theodore 4 4 4 Male Supply Chain
Lois 4 4 4 Female Supply Chain
Emil 5 4 4 Male Supply Chain
Mabel 5 4 5 Female Supply Chain
Earnest 5 4 5 Male Supply Chain
Lucy 5 4 5 Female Supply Chain
Leon 5 4 5 Male Supply Chain
Roberta 4 5 2 Female Supply Chain
Lowell 5 5 2 Male Supply Chain
Ira 4 5 3 Male Supply Chain
Glen 4 5 3 Male Supply Chain
Jim 4 5 3 Male Supply Chain
Eileen 4 5 3 Female Supply Chain
Irving 5 5 3 Male Supply Chain
Elmer 5 5 3 Male Supply Chain
Bernard 5 5 3 Male Supply Chain
Anna 5 5 3 Female Supply Chain
Louis 5 5 3 Male Supply Chain
Betty 5 5 3 Female Supply Chain
Elizabeth 5 5 3 Female Supply Chain
Roland 4 5 4 Male Supply Chain
Edward 4 5 4 Male Supply Chain
Bessie 4 5 4 Female Supply Chain
John 4 5 4 Male Supply Chain
Raymond 4 5 4 Male Supply Chain
Tom 5 5 5 Male Supply Chain
Arlene 5 5 5 Female Supply Chain
Beverly 5 5 5 Female Supply Chain
Peggy 5 5 5 Female Supply Chain
Jack 5 5 5 Male Supply Chain
Edith 5 5 5 Female Supply Chain
Naomi 5 5 5 Female Supply Chain
Helen 5 5 5 Female Supply Chain

Instructions

Instructions Hint
The sheet labeled "Raw Data" lists 366 employees and their rating (1-5) of their satisfaction with their Pay, Responsibilities, and Supervision. A rating of 5 is the highest satisfaction.
Raw Data also includes the Gender and Department for each employee.
Insert a new column in E Insert
Label this new column "Overall Satisfaction Rating" Data entry
For each employee, compute the Overall Satisfaction Rating as the Average of Pay, Responsibilities, and Supervision. =average()
Format Overall Satisfaction Rating to one decimal place. Format
On a New sheet titled Results, create a Pivot Chart & Pivot Table Pivot Chart & Pivot Table
Assign Gender to Columns, Department to rows, and Pay to Values. Change the value field setting from Sum to Average if necessary. Field List
Sort the departments in descending order of satisfaction. Sort
Create a title for the chart, which includes your last name Chart Elements
Be sure your chart includes a legend for male & female employees, change male color to blue and female to orange Chart Elements
Be sure to include axis titles Chart Elements
Format the vertical axis for a max of 5 and major tick marks at 1 and one decimal place. Format Axis
Create a new sheet titled "Graphs".
Copy & Paste as Picture your graph of Pay Satisfaction Copy - Paste Special (Picture)
Alter your Pivot chart/table to display Responsibilities Satisfaction. Change titles as needed. Field List
Paste this chart on the Graphs sheet
Alter your Pivot chart/table to display Supervision Satisfaction. Change titles as needed.
Paste this chart on the Graphs sheet
Alter your Pivot chart/table to display Overall Satisfaction. Change titles as needed.
Paste this chart on the Graphs sheet
Leave Results sheet with the Pivot Table & Chart displaying the Overall Satisfaction.
Your Graphs Page should look as shown below.
Does there appear to be a difference in satisfaction level in Dept or Area for males vs females? Answer here?
Which department has the highest overall satisfaction level rating? Answer here?
If the Human Resources department is trying to raise their own department's overall satisfaction rating, which area should they address? Answer here?
Besides Engineering, which department is most satisfied with their supervisors? Answer here?