Please calculate the correct values for apprentices hired and craftspeople fired for each month, also calculate the total cost, thank you! **New Exercise Bank Content Qu. 07-07**Allen Furniture is a manufacturer of hand-crafted furniture. At the start of January, Allen employs 21 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 a new craftsperson requires advertising, interviewing, and then training, at a cost of $2,500 per hire. New hires are called apprentices. Apprentices spend their first month observing and learning. They are paid $2,000 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.**| | January | February | March | April | May | June | July | August | September | October | November | December ||--------------------|---------|----------|-------|------|-----|------|------|--------|-----------|---------|----------|----------|| Apprentices hired | 0 | 6 | 7 | 7 | 5 | 0 | 0 | 2 | 0 | 0 | 1 | 0 || Craftspeople fired | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | The spreadsheet displayed appears to be a workforce management planner for a craftsman business. Here's a detailed transcription and explanation of the contents:**Top Left Section: Costs and Staffing Details**1. Craftsman Wage per month: $3,0002. Apprentice Wage per month: $2,0003. Hiring Cost: $2,5004. Severance Pay: $1,5005. Labor Hours/Craftsman/Month: 2006. Starting Trained Craftsmen: 217. Maximum Layoffs: 10%**Table: Yearly Planner**- **Rows:** - "Apprentices Hired": This row is highlighted in yellow but lacks data. - "Craftsmen Fired": Directly below apprentices, this row indicates personnel changes but also lacks data.- **Columns (Months):** Each column represents a month from January to December.**Bottom Row: Required Labor Hours**- This row specifies labor hours needed for each month: - January: 3400 - February: 4000 - March: 4200 - April: 4200 - May: 3000 - June: 2800 - July: 3000 - August: 4000 - September: 4500 - October: 5000 - November: 5200 - December: 4800**Overview**The spreadsheet helps manage workforce levels by comparing labor required and adjusting hiring or layoffs accordingly. The calculations likely involve balancing costs with operational needs by monitoring wages, hiring costs, and severance pay.

Aggregate planning is a marketing activity that does an aggregate plan for the production process,…

Allen Furniture is a manufacturer of hand-crafted furniture. At the start of January, Allen employs 21 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,000 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.

Allen Furniture is a manufacturer of hand-crafted furniture. At the start of January, Allen employs 21 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,000 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.

Practical Management Science

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...

See similar textbooks

Similar questions

Explain about the business environment of laundromat business taking into account ethical issues and social responsibility performed by the business.
Define the term job shop?
pen-and-paper performance management system
What are some of the ethical issues of cost allocation?
Describe the staffing competencies needed for hiring team members in the back-end revenue cycle
In what way would you want your production to be described?
Find an example and illustrate improvements in labour efficiency using emerging technology.
How are a job shop used for work packages, hotlists and exemption reports?
How different businesses are using the technique Engineered estimate of cost to estimate cost of different areas?
Describe system in which highly skilled workers use simple, flexible tools to produce small quantities of customized goods.
State a company or organization that has Enterprise Resource Planning system and state how it impact or benefit the entire business process. In terms of : HUMAN RESOURCE, ACCOUNTING, MARKETING, AND SUPPLY