Prescriptive Modelling using solver

profiledemmyemi
DYS545_course_project_Part_Two_B.xlsx
Home>Business & Finance homework help>Prescriptive Modelling using solver

Instructions

Using Prescriptive Analytics in Excel
Course Project: Part 2B
Instructions:
As the Human Resources Manager, you need to identify the most effective way to assign 6 new employees to 6 positions within the organization. You are given ratings for the employees based on a trial week rating from managers in the respective departments. The ratings are on a 1-10 scale with 10 being the best score. Each employee will be assigned to one unique position since all work happens simultaneously. Your goal is to maximize skill level as you assign the work tasks. Complete the model by adding necessary formulas and populate the constraints table. Create a solver model that prescribes best employee assignments based on skills.

MOD2 Binary

Employee Skill level by work task Employee Tasks Number of Tasks Skills level
Data Entry Inventory Report Writing Budgeting Training Journal Entries Data Entry Inventory Report Writing Budgeting Training Journal Entries
Employee 1 10 10 10 7 8 7 0 0
Employee 2 9 10 7 7 6 6 0 0
Employee 3 8 9 7 7 9 9 0 0
Employee 4 6 9 10 8 8 6 0 0
Employee 5 7 10 9 9 8 7 0 0
Employee 6 10 9 9 6 8 10 0 0
Total Assignment 0 0 0 0 0 0 0
Skills level Total
Constraints
As the Human Resources Manager, you need to identify the most effective way to assign 6 new employees to 6 positions within the organization. You are given ratings for the employees based on a trial week rating from managers in the respective departments. The ratings are on a 1-10 scale with 10 being the best score. Each employee will be assigned to one unique position since all work happens simultaneously. Your goal is to maximize skill level as you assign the work tasks.
Complete the model above by adding necessary formulas and populate the constraints table.
Create a solver model that prescribes best employee assignments based on skills.