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 the performance appraisal methods and problems. Also explain the advantages and disadvantages of all the methods
What differentiates an employee from an independent contractor?
Explain when companies are likely to use a job costing system or a process costing system. Provideexamples.
What Factors Determine the Level Payment? What are the Definitions and Examples of How to Evaluate work? How to Organize and Create Competitive Payment Plan? How to Set a Payment for Managerial and Professional Jobs? Explain it comprehensive and provide examples What is the difference Between Payment Based Plans Competence and Traditional As one of the indicators of satisfaction in work is difficult to understand, because the views of the employees regarding direct rewards appears to be very subjective and is something typical in the industry. But basically with the presence of injustice in giving wages is a sources of employee dissatisfaction with compensation existing ones. Explain the strategies that must be done by corporate management so that there is no dispute related to wages, compensations and incentives and facilities provided by company employees Intended How indonesia's hr development strategyin the future globally and COMPETITIVEAdvantage? How to step in formulating a…
Distinguish between the locational determinants of labor climate and quality of life.
What factor distinguishes an employee from an independent contractor?
What are the Basic Concepts of Total Quality Management?
pen-and-paper performance management system
Which among the following refers to chase method? Stable output rates and workforce Worker levels and production output are stable The cost of adjusting output rates and/or workforce levels Tends to be the preferred strategy of many organizations, including labor unions.
Of the three factors affecting the control of labour costs, comment on which factor you believe has the greatest impact on your organization. Justify your response.
To what extent can firms be expected to extend their workplace responsibilities to employees in their supply chain?
Jack Simpson, contract negotiator for Nebula Airframe Company, is currently involved in bidding on a follow-up government contract. In gathering cost data from the first three units, which Nebula produced under a research and development contract, he found that the first unit took 2,000 labor hours, the second took 1,600 labor hours, and the third took 1,500 hours. Use Exhibit 4A.6. In a contract for three more units, how many labor hours should Simpson plan for? Note: Round your answer to the nearest whole number.