Question

The Do-it-Quick Company has agreed to supply its best customer with three widgets during each of the next 3 weeks; even through producing them will require some overtime work. The relevant production data are as follows: The cost per unit produced with overtime for each week is $100 more than for regular time. The cost of storage is $200 per unit for each week it is stored. There is already an inventory of two widgets in hand currently, but the company does not want to retain any widgets in inventory after the 3 weeks. Management wants to know how many units should be produced in each week to minimize the total cost of meeting the delivery schedule. Formulate and solve for a spreadsheet model for this problem

Answer 1

The student's question requires creating a spreadsheet model to find the optimal number of widgets to produce each week to minimize costs, taking into account overtime production and storage expenses. A basic cost analysis reveals that the regular production cost per widget is $5 and overtime cost is $105 per widget, with existing inventory considered in the model.

Step-by-step explanation:

The question presented involves creating a spreadsheet model to determine the number of units a company should produce each week to minimize the total cost of production and storage, while meeting delivery requirements.

Since the company wants to avoid retaining any inventory after the three weeks, and the cost of overtime production is $100 more per unit while storage costs $200 per unit per week, the goal is to find the optimal balance between production and inventory costs.

We can approach this by first calculating the cost of producing widgets each week under regular and overtime conditions.

If workers earn $10 per hour and can produce two widgets per hour, it means the regular cost of production per widget is $5.

Taking into account that overtime production is $100 more, we can say that producing a widget on overtime costs $105.

Knowing that the company starts with an inventory of two widgets and that there is a constant demand for three widgets per week, we can create a model that accounts for production, storage, and delivery.

We can set up different production scenarios for the three weeks, and for each scenario, calculate the total cost by considering both the production and storage costs.

The model can then be solved using spreadsheet software to find the minimum total cost and thus determine the optimal production schedule.