Case Study R&J Trucking excel

MIS$-3210$: $-$ Case Study $\backslash$#$1($R$\backslash$&J Trucking$)$

Applies to:

$-$ Unit $2($Excel Skills$)$

$-$ Weeks $3,4,5,6($Formulas $\backslash$& Functions, Excel Tables, Pivot Tables$)$

Concepts $\backslash$& Skills Reinforced

Spreadsheet $($Some or all may be applicable$)$

$-$ BADIR

$-$ Formulas $\backslash$& Functions

$-$ Absolute v$.$ Relative Addressing

$-$ FlashFill

$-$ Conditional Formatting

$-$ VLookup

$-$ Filtering $\backslash$& Sorting $($Tables$)$

$-$ Summary Statistics $($Tables$)$

$-$ Logical Functions $($SUMIF$,$ COUNTIF$)$

$-$ Pivot Tables

$-$ Slicers $($Pivot Tables$)$

Other Skills

$-$ Written communication

Completing the activity:

$-$ Download and save the data source: CaseStudy$\backslash$#$1-$R$\backslash$&JTrucking$-$Starter.xlsx $($Excel format$)$

$-$ Review the following background information on the case study including the specific research questions you need to address. $($See below.$)$

$-$ Using concepts and skills applied in this unit, conduct the necessary analysis to address all the research questions provided in the case study information on the following pages.

$-$ Organize and submit your results to the Case Study $\backslash$#$1$ Assignment Folder according to the guidelines under "Work to Submit" at the end of this document.

General Description:

In this activity, you will be conducting analysis of HR $($Human Resources$)$ data from a hypothetical trucking company $($Reginald $\backslash$& Jones Trucking$)$ that has been extracted from their payroll and time reporting systems. You will then use this dataset to answer a set of practical questions based on a descriptive scenario that could happen in the real world.

The provided dataset includes the following columns of information:

$-$ EmpID $($Employee ID$)$

$-$ Employee Name $($Lname$,$ Fname$)$

$-$ BirthDate $($Employee$\text{'}$s date$-$of$-$birth$)$

$-$ Loc $($Physical location of their employment within Reginald's network of hubs$)$

$-$ PayCat $($hourly$,$ professional, part$-$time, etc.$)-$ See explanation table in dataset

$-$ Dept $($Driver$,$ Maintenance, Administration, Sales, Other$)$

$-$ Salary $($Stated Salary, but can vary based on hours worked$)$

$-$ Gender $($M$/$F$)$

$-$ SchHrs $($Hours scheduled in the most recent $2-$week period$)$

$-$ PostHrs $($Actual Hours posted in that same $2-$week period$)$

Scenario and Context:

You work for the HR department at Reginald $\backslash$& Jones Trucking $("$R$\backslash$&J$")$ and have been put on an internal project team that has been organized to look at a variety of issues related to work hours and compensation across the entire organization. Among those issues, the executive leadership team believes that an across$-$the$-$board pay increase is merited for all employees, but they want to make sure this pay increase is implemented properly, fairly, and meets certain financial and timeline objectives.

Business Objectives $\backslash$& Issues:

$-$ A total of $\backslash (\backslash$$ $315,000\backslash )$ has been budgeted in the upcoming fiscal year to fund the pay raise proposal. All employees will receive a pay increase as a percentage of their current pay and a function of their pay category $($PayCat$).$

$-$ R$\backslash$&J has three $(3)$ different pay categories and all employees within a given category will receive the same percentage increase with a minimum of $\backslash (2\mathrm{.}5\backslash \%\backslash )$ and a maximum of $\backslash (5\mathrm{.}0\backslash \%\backslash )$ for any given Pay Category.

$-$ To remain competitive in pay nationally within their driver pool $($PayCat$=2),$ a minimum pay raise of $\backslash (4\mathrm{.}5\backslash \%\backslash )$ has been set for PayCat $2.$ Similarly, PayCat$=3($of which the maintenance team is part of$)$ will receive at least a $\backslash (4\mathrm{.}0\backslash \%\backslash )$ raise. The initial proposal for the administrative team $($PayCat$=1)$ is $\backslash (2\mathrm{.}9\backslash \%\backslash ),$ but it is hoped that percentages for some of the pay categories can go higher but still remain within the $\backslash (\backslash$$ $315,000\backslash )$ overall budget for pay increase. Your goal is to maximize the pay raise percentages without exceeding the $\backslash (\backslash$mathbf$\{\backslash$$ $315,000\}\backslash )$ pool of extended funds. $($Note: Make sure that you are using the PayCat value for each employee and not their Department when calculating the pay raise percentages.$)$

$-$ The executive committee wants to announce the increase at least $30$ days before the new fiscal year begins $($which is about $70-90$ days from now$).$