Allen Furniture is a manufacturer of hand-crafted furniture. At the start of January, Allen employs 20...

Transcribed Image Text:

Allen Furniture is a manufacturer of hand-crafted furniture. At the start of January, Allen employs 20 trained craftspeople. They have forecasted their labor needs over the next 12 months as shown in the following table. Each trained craftsperson provides 200 labor- hours per month and is paid a wage of $3,000 per month. Hiring new craftspeople requires advertising, interviewing, and then training at a cost of $2,500 per hire. New hires are called apprentices for their first month. Apprentices spend their first month observing and learning. They are paid $2,200 for the month, but provide no labor. In their second month, apprentices are reclassified as trained craftspeople. The union contract allows for firing a craftsperson at the beginning of a month, but $1,500 must be given in severance. pay. Moreover, at most 10 percent of the trained craftspeople can be fired in any month. Allen would like to start next year with at least 25 trained craftspeople. Click here for the Excel Data File Build and solve a linear programming spreadsheet model to maximize the profit over all 12 months. a. How many apprentices should be hired and how many craftspeople should be fired in each month to meet the labor requirements for this next year at the minimum possible cost? b. Determine the total cost. Complete this question by entering your answers in the tabs below. Required A Required B How many apprentices should be hired and how many craftspeople should be fired in each month to meet the labor requirements for this next year at the minimum Note: Leave no cells blank. Enter zero (0) whenever required. January February March April May June July August September October November December Apprentices hired Craftspeople fired Required A Required B > Allen Furniture is a manufacturer of hand-crafted furniture. At the start of January, Allen employs 20 trained craftspeople. They have forecasted their labor needs over the next 12 months as shown in the following table. Each trained craftsperson provides 200 labor- hours per month and is paid a wage of $3,000 per month. Hiring new craftspeople requires advertising, interviewing, and then training at a cost of $2,500 per hire. New hires are called apprentices for their first month. Apprentices spend their first month observing and learning. They are paid $2,200 for the month, but provide no labor. In their second month, apprentices are reclassified as trained craftspeople. The union contract allows for firing a craftsperson at the beginning of a month, but $1,500 must be given in severance pay. Moreover, at most 10 percent of the trained craftspeople can be fired in any month. Allen would like to start next year with at least 25 trained craftspeople. Click here for the Excel Data File Build and solve a linear programming spreadsheet model to maximize the profit over all 12 months. a. How many apprentices should be hired and how many craftspeople should be fired in each month to meet the labor requirements for this next year at the minimum possible cost? b. Determine the total cost. Complete this question by entering your answers in the tabs below. Required A Required B Determine the total cost. Total cost < Required A Required B A1 1 2 4 6 7 8 9 10 11 Home 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Paste Insert X Draw Arial В I U✔ A Craftsman Wage per month! Apprentice Wage per month Hiring Cost Severance Pay Labor Hours/Craftsman/Month Starting Trained Craftsmen Maximum Layoffs Page Layout Apprentices Hired Craftsmen Fired Required Labor Hours x✔ fx Craftsman Wage per month B C $3,000 $2,200 $2,500 $1,500 200 20 10% Jan ✓ 10 3400 Feb Formulas 4000 au AA D Mar V 4200 E Apr 4200 Data F May 3000 Review G Jun 2800 View H Jul 3000 Automate 2 Wrap Text Merge & Center Aug 4000 2 Sep Developer 4500 K Oct 5000 General Tell me $%9 L Nov 5200 M Dec 4800 89 N Allen Furniture is a manufacturer of hand-crafted furniture. At the start of January, Allen employs 20 trained craftspeople. They have forecasted their labor needs over the next 12 months as shown in the following table. Each trained craftsperson provides 200 labor- hours per month and is paid a wage of $3,000 per month. Hiring new craftspeople requires advertising, interviewing, and then training at a cost of $2,500 per hire. New hires are called apprentices for their first month. Apprentices spend their first month observing and learning. They are paid $2,200 for the month, but provide no labor. In their second month, apprentices are reclassified as trained craftspeople. The union contract allows for firing a craftsperson at the beginning of a month, but $1,500 must be given in severance. pay. Moreover, at most 10 percent of the trained craftspeople can be fired in any month. Allen would like to start next year with at least 25 trained craftspeople. Click here for the Excel Data File Build and solve a linear programming spreadsheet model to maximize the profit over all 12 months. a. How many apprentices should be hired and how many craftspeople should be fired in each month to meet the labor requirements for this next year at the minimum possible cost? b. Determine the total cost. Complete this question by entering your answers in the tabs below. Required A Required B How many apprentices should be hired and how many craftspeople should be fired in each month to meet the labor requirements for this next year at the minimum Note: Leave no cells blank. Enter zero (0) whenever required. January February March April May June July August September October November December Apprentices hired Craftspeople fired Required A Required B > Allen Furniture is a manufacturer of hand-crafted furniture. At the start of January, Allen employs 20 trained craftspeople. They have forecasted their labor needs over the next 12 months as shown in the following table. Each trained craftsperson provides 200 labor- hours per month and is paid a wage of $3,000 per month. Hiring new craftspeople requires advertising, interviewing, and then training at a cost of $2,500 per hire. New hires are called apprentices for their first month. Apprentices spend their first month observing and learning. They are paid $2,200 for the month, but provide no labor. In their second month, apprentices are reclassified as trained craftspeople. The union contract allows for firing a craftsperson at the beginning of a month, but $1,500 must be given in severance pay. Moreover, at most 10 percent of the trained craftspeople can be fired in any month. Allen would like to start next year with at least 25 trained craftspeople. Click here for the Excel Data File Build and solve a linear programming spreadsheet model to maximize the profit over all 12 months. a. How many apprentices should be hired and how many craftspeople should be fired in each month to meet the labor requirements for this next year at the minimum possible cost? b. Determine the total cost. Complete this question by entering your answers in the tabs below. Required A Required B Determine the total cost. Total cost < Required A Required B A1 1 2 4 6 7 8 9 10 11 Home 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 Paste Insert X Draw Arial В I U✔ A Craftsman Wage per month! Apprentice Wage per month Hiring Cost Severance Pay Labor Hours/Craftsman/Month Starting Trained Craftsmen Maximum Layoffs Page Layout Apprentices Hired Craftsmen Fired Required Labor Hours x✔ fx Craftsman Wage per month B C $3,000 $2,200 $2,500 $1,500 200 20 10% Jan ✓ 10 3400 Feb Formulas 4000 au AA D Mar V 4200 E Apr 4200 Data F May 3000 Review G Jun 2800 View H Jul 3000 Automate 2 Wrap Text Merge & Center Aug 4000 2 Sep Developer 4500 K Oct 5000 General Tell me $%9 L Nov 5200 M Dec 4800 89 N

Answer rating: 100% (QA)

Well first set up the problem and then provide the solution This problem can be solved using a linear programming approach in a spreadsheet but Ill de... View the full answer