Graded Assignment 1: Dire Wolf Redux

You are working for Dire Wolf Industries (DWI), helping them run their Sales & Operations Planning (S&OP) program for a line of make to stock items. Specifically, you are running the aggregate planning model to help assist the Chief Operating Officer (COO) and the Vice President of Sales to agree upon the next 12 months plan.

The aggregate model is attached here forExcelandLibreOffice. The model provided hereis offered without any input data. That data is provided below, and you must enter it by hand into the spreadsheet. When solvingthis model, while the number of workers must be aninteger, the amounts of money, hours and items do not have to be integers. Thesolversettings can be seenhere.

Part 1

0.0/1.0 point (graded)

The model provided above is offered without any of the input data.The demand data for the model is provided below, and you must enter itinto the spreadsheet. The aggregate monthly demand of the item is as follows:

January	February	March	April
16602	15018	13614	10398

May	June	July	August
6054	3138	1758	4398

September	October	November	December
7662	9066	11922	18402

The remaining data for the model is provided below, and you must enter it into the spreadsheet. Please enter the input values with all the decimal values given below.

• Material cost ($/unit): 14.3517
• Inventory holding costs ($/unit/month): 3.0883
• Cost of stockouts ($/unit/month): 5.6317
• Cost for hiring and training new worker: 2725.0
• Cost for laying off an employee: 5450.0
• Labor hours required per item produced: 0.56
• Regular worker cost ($/month): 2616.0
• Overtime cost ($/hour): 18.4911
• Outsourcing cost ($/item): 32.8817
• Hours worked by employee per month: 175.84
• Max overtime (hours/month/employee): 12.32
• Starting inventory: 3000
• Ending inventory (min): 3000
• Starting backlog: 0
• Allowable ending backlog (max): 0
• Starting workforce: 24
• Ending workforce (min): 24
• Ending workforce (max): 24
• Base price ($/item): 38.3317

You want to develop an initial aggregate plan for the next 12 months. Use the initial data provided above and assume that there are no planned promotions or discounts over the next 12 months. Find the optimal plan over the next 12 months. Based on this optimal solution, answer the following questions.

What is the total annual profit?

Enter your answer in dollars, without currency symbol or thousands delimiters. For example, if your answer is $1,234 then type 1234 in the space.

unanswered

SaveSave your answer

Submit

You have used 0 of 2 attemptsSome problems have options such as save, reset, hints, or show answer. These options follow the Submit button.

Part 2

0.0/1.0 point (graded)

Using the status quo (Part 1) as starting point, the senior team has asked you to assess the impact of running a three-month promotion during the Summer with a 15% discount in June (month 6), a 25% discount in July (month 7), and a 15% discount in August (month 8). Use an elasticity value of 5, and solve optimally.

Will the total annual profit increase in this scenario, compared to the profit in Part 1?

Select an option Yes, it will increase No, it will remain the same No, it will decrease It cannot be determined unanswered

Solving optimally, what is the total annual profit with this promotion?

Enter your answer in dollars, without currency symbol or thousands delimiters.

unanswered

SaveSave your answer

Submit

You have used 0 of 2 attemptsSome problems have options such as save, reset, hints, or show answer. These options follow the Submit button.

Part 3

0.0/1.0 point (graded)

There are some concerns about the sustainability of the operations at the subcontractors. Using the status quo (Part 1) as starting point, the senior team has asked you to assess the impact of a policy that would eliminate all outsourcing of production. Solve optimally.

How will the total annual profit under this policy compare to the profit of the status quo (Part 1)? Select an option It will decrease more than 5% It will decrease about 3% It will decrease less than 1% It will increase It cannot be determined unanswered

Solving optimally, what is the total annual profit under this policy?

Enter your answer in dollars, without currency symbol or thousands delimiters.

unanswered

SaveSave your answer

Submit

You have used 0 of 2 attemptsSome problems have options such as save, reset, hints, or show answer. These options follow the Submit button.

Part 4

0.0/1.0 point (graded)

There is some new technology coming into the market that may allow Dire Wolf to produce the items faster than before. Using the status quo (Part 1) as starting point, you have been asked to assess the impact that such a technology could have if it could reduce the labor hours required per item produced by one third. (Hint: This means that the hours required to produce an item using the new technology would be two thirds of the hours required to produce an item in the status quo). Solve optimally.

Which of the following statements would be true considering the faster production scenario, compared to the status quo (Part 1)?

Select all the correct answers. Note that upon submission, if some points have been awarded, all options selected will be marked as green (even the wrong ones). If no points have been awarded, all options selected will be marked as red (even the right ones). A message indicating "correct", "partially correct" or "incorrect" will appear below the question. We know this can be confusing and are working with edX software engineers on this issue.

Total annual profit increases

Outsourced production decreases by two thirds

Outsourced production is no longer used

Number of employees fired decreases by one third

None of the above

unanswered

Solving optimally, what is the total annual profit in this scenario?

Enter your answer in dollars, without currency symbol or thousands delimiters. For example, if your answer is $1,234 then type 1234 in the space.

unanswered

SaveSave your answer

Submit

You have used 0 of 2 attemptsSome problems have options such as save, reset, hints, or show answer. These options follow the Submit button.

Part 5

0.0/1.0 point (graded)

There are some concerns about the limitations of Dire Wolf's warehousing capacity. Using the status quo (Part 1) as starting point, the senior team has asked you to assess the impact of limiting inventory to a maximum of 6000 items. (Hint: This means that at no point can the inventory levels exceed this limit). Solve optimally.

What is the total annual profit?

Enter your answer in dollars, without currency symbol or thousands delimiters. For example, if your answer is $1,234 then type 1234 in the space.

unanswered

Dire Wolf Industries		Annual Costs by Category					T	Base Demand	Price Discount (% decrease)
	Total Annual Cost	-$559,077		$892,056	Regular Labor Cost						1	16602	0%
	Total Annual Profit	$5,083,444		$39,053	Overtime Labor cost						2	15018	0%
	Avg Cost ($/unit)	-$4.74		$43,600	Cost of Hiring						3	13614	0%
	Avg Profit ($/unit)	$43.07		$87,200	Cost of Firing						4	10398	0%
				-$1,939,276	Cost of Inventory						5	6054	0%
				$1,584	Cost of Stockouts						6	3138	0%
				$255,699	Cost of Materials						7	1758	0%
				$61,007	Cost of Outsourcing						8	4398	0%
											9	7662	0%
	Inputs:										10	9066	0%
		$14.35	Material cost ($/unit)			175.84	Hours worked By employee per month				11	11922	0%
		$3.09	Inventory holding costs ($/unit/month)			12.32	Max overtime (hours/month/employee)				12	18402	0%
		$5.63	Cost of stockouts ($/unit/month)			3000	Starting inventory
		$2,725.00	Cost for hiring and training new worker			3000	Ending inventory (min)				Base Price	$38.33
		$5,450.00	Cost for laying off an employee			0	Starting backlog				Elasticity	-00	Keep this positive since discount is positive
		0.56	Labor hours required per item produced			0	Allowable ending backlog (max)
		$2,616.00	Regular worker cost ($/month)			24	Starting workforce
		$18.49	Overtime cost ($/hour)			24	Ending workforce (min)
		$32.88	Outsourcing cost ($/item)			24	Ending workforce (max)
	Time Period	Inventory (#)	Workforce	Employees Hired	Employees Fired	Overtime (hrs)	Backlogged (units)	Internal Production (units)	Outsourced Production (units)	Available		Monthly Demand Forecast (units)	Price	Maximum Production (units)	Maximum Overtime (hours)
	1	-11,335	40	16	0	440.0	0.00	2,240.0	27.0	5,267		16,602	$38	13,346	493
	2	-23,850	40	0	0	440.0	2.67	2,240.0	260.3	-8,832		15,018	$38	13,346	493
	3	-35,195	40	0	0	440.0	31.67	2,240.0	0.0	-21,581		13,614	$38	13,346	493
	4	-43,860	29	0	11	0.0	247.00	1,517.7	0.0	-33,462		10,398	$38	9,106	357
	5	-48,905	24	0	5	0.0	0.00	1,256.0	0.0	-42,851		6,054	$38	7,536	296
	6	-51,520	24	0	0	0.0	0.00	523.0	0.0	-48,382		3,138	$38	7,536	296
	7	-52,022	24	0	0	0.0	0.00	1,256.0	0.0	-50,264		1,758	$38	7,536	296
	8	-55,164	24	0	0	0.0	0.00	1,256.0	0.0	-50,766		4,398	$38	7,536	296
	9	-61,570	24	0	0	0.0	0.00	1,256.0	0.0	-53,908		7,662	$38	7,536	296
	10	-69,292	24	0	0	264.0	0.00	1,344.0	0.0	-60,226		9,066	$38	8,007	296
	11	-79,870	24	0	0	264.0	0.00	1,344.0	0.0	-67,948		11,922	$38	8,007	296
	12	-95,360	24	0	0	264.0	0.00	1,344.0	1,568.0	-76,958		18,402	$38	8,007	296
	Sum	-627,943	341	16	16	2,112	281	17,817	1,855			118,032