business analysis

profilekin2tu
Assignment3spreadsheet.xlsx
Home>Business & Finance homework help>Economics homework help>business analysis

Sheet1

Assignment 3 Template
KPI Region Calculation
Revenue Per Employee Region Revenue Divided by/ The number of full time employees Equals Revenue Per Employee Instructions: Use the data from the Assignment 3 Data Sheet to fill in the numbers in the yellow highlighted boxes. The KPIs and YOY will automatically be calculated. The KPI formulas have been pre-formatted and are listed under the legend for your reference
Canada LEGEND
Year 1 8 Divided by 9,000 Equals 0.0008888889 1.       Revenue per Employee = Revenue/The Number of Full Time Employees
Year 2 10 Divided by 10,000 Equals 0.001 2.       Net Profit =Sales Revenue-Total Costs
YOY % Equals (auto calculation) 12.5% 3.       NPS Score = % of Promotors- % of Detractors
Northern States 4.       Staff Advocacy Score= % of Employee Advocates- % of Employee Detractors
Year 1 10 Divided by 12,800 Equals 0.00078125 5.       Relative Market Share = Organization's % of Market Share/The Largest Competitors % of Market Share
Year 2 11 Divided by 13,000 Equals 0.0008461538
YOY % Equals (auto calculation) 8.3%
Middle States
Year 1 11 Divided by 14,000 Equals 0.0007857143
Year 2 9 Divided by 12,000 Equals 0.00075
YOY % Equals (auto calculation) -4.5%
Southern States
Year 1 15 Divided by 12,500 Equals 0.0012
Year 2 18.2 Divided by 14,000 Equals 0.0013
YOY % Equals (auto calculation) 8.3%
Net Profit Region Sales Revenue Minus (-) Total Costs Equals Net Profit
Canada
Year 1 8 Minus 7.5 Equals 0.5
Year 2 10 Minus 9.5 Equals 0.5
YOY % Equals (auto calculation) 0.0%
Northern States
Year 1 10 Minus 9 Equals 1
Year 2 11 Minus 10.5 Equals 0.5
YOY % Equals (auto calculation) -50.0%
Middle States
Year 1 11 Minus 10 Equals 1
Year 2 9 Minus 8.5 Equals 0.5
YOY % Equals (auto calculation) -50.0%
Southern States
Year 1 15 Minus 14 Equals 1
Year 2 18.2 Minus 17.2 Equals 1
YOY % Equals (auto calculation) 0.0%
NPS Score Region % of promotors Minus (-) % of detractors Equals NPS Score
Canada
Year 1 92 Minus 7 Equals 85
Year 2 88 Minus 5 Equals 83
YOY Equals (auto calculation) -2
Northern States
Year 1 82 Minus 7 Equals 75
Year 2 84 Minus 8 Equals 76
YOY Equals (auto calculation) 1
Middle States
Year 1 84 Minus 5 Equals 79
Year 2 79 Minus 20 Equals 59
YOY Equals (auto calculation) -20
Southern States
Year 1 90 Minus 6 Equals 84
Year 2 91 Minus 7 Equals 84
YOY Equals (auto calculation) 0
Staff Advocacy Score Region Percentage of Advocates Minus Percentage of Detractors Staff Advocacy Score
Canada
Year 1 97 Minus 7 Equals 90
Year 2 95 Minus 5 Equals 90
YOY Equals (auto calculation) 0
Northern States
Year 1 83 Minus 14 Equals 69
Year 2 84 Minus 12 Equals 72
YOY Equals (auto calculation) 3
Middle States
Year 1 79 Minus 6 Equals 73
Year 2 70 Minus 19 Equals 51
YOY Equals (auto calculation) -22
Southern States
Year 1 88 Minus 9 Equals 79
Year 2 91 Minus 8 Equals 83
YOY Equals (auto calculation) 4
Relative Market Share Region Organization's Market Share Divided by (/) Organization's Largest Competitor's Market Share Relative Market Share
Canada
Year 1 10 Divided by 30 Equals 0.3333333333
Year 2 12 Divided by 30 Equals 0.4
YOY Equals (auto calculation) 0.0666666667
Northern States
Year 1 23 Divided by 27 Equals 0.8518518519
Year 2 24 Divided by 25 Equals 0.96
YOY Equals (auto calculation) 0.1081481481
Middle States
Year 1 24 Divided by 31 Equals 0.7741935484
Year 2 18 Divided by 30 Equals 0.6
YOY Equals (auto calculation) -0.1741935484
Southern States
Year 1 30 Divided by 22 Equals 1.3636363636
Year 2 34 Divided by 15 Equals 2.2666666667
YOY Equals (auto calculation) 0.903030303