How many of each product should omega manufacturing produce

Question

1. Advertising Planning at Reboot, Inc.

Reboot, Inc. is a manufacturer of high quality boots. In preparation for the annual planning meeting you, the manager of the marketing department, have recently completed negotiations with the Executive Vice President for next year's advertising budget. The Executive Vice President agreed to a maximum total budget of $1,000,000 for the year provided that no more than $250,000 be spent in the first quarter.

Suppose you have created the following Excel spreadsheet (which is very similar to the advertising example used in class), and have used Solver to determine the distribution of those funds that will maximize the annual profit for Reboot.

You allow yourself some small feeling of pride as you distribute the following spreadsheet analysis to the other department managers at the annual meeting.

A	B	C	D	E	F	G
1	Reboot Inc. Advertising Plan
2
3	Parameters:
4	Unit Variable Cost	$60
5	Unit Price	$110
6	Fixed Overhead	$180,000
7	Seasonality	$1	0.8	0.7	1.4
8	Advertising Previous Q4	$200,000				Total
9						Advertising
10	Decision Variables:
11	Advertising	$250,000	$156,691	$197,899	$378,709	$983,299
12		<=				<=
13		$250,000				$1,000,000
14
15	Quarter	Q1	Q2	Q3	Q4	Total
16	Expected Units Sold	14,874	9,707	8,630	21,707	54,918
17		$1,636,098	$1,067,817	$949,314	$2,387,770	$6,040,999
18	Sales Revenue
19	Cost of Sales	$892,417	$582,446	$517,807	$1,302,420	$3,295,090
20	Gross Margin	$743,681	$485,371	$431,506	$1,085,350	$2,745,909
21		$250,000	$156,691	$197,899	$378,709	$983,299
22	Advertising Cost
23	Fixed Overhead	$180,000	$180,000	$180,000	$180,000	$720,000
24
25	Profit	$313,681	$148,680	$53,607	$526,641	$1,042,610

Almost immediately, you hear chuckling from two seats to your left. The Production Manager had zeroed in on the "Expected Units Sold" line and, after her initial shock, began to laugh. "This is some wild production schedule you've projected here! Looks like we'll have to make almost 15,000 pairs of boots in the first quarter, then we'll scale back to less than 10,000 in quarter two, less than 9000 in quarter 3, and then wrap up the year with almost 22,000! On the other hand, we could use inventory to keep production steady, but that would change your figures. Can your spreadsheet handle inventory holding costs?"

With a confident nod, you turn to your company issue, state-of-the-art notebook computer. Calling up your spreadsheet you say, "Let's see what happens."

Assignment:

Assume that the production staff cannot be varied from quarter to quarter, which limits production levels to range between a minimum of 13,000 and a maximum of 15,000 pair of boots each quarter. Assume you can not sell more boots than you have available (i.e., no backorders are allowed).

The cost of holding inventory must be considered. The company's accountants have calculated a quarterly inventory holding cost of $7 per pair of boots, which should be charged to the quarter in which the inventory is held, using the inventory level at the end of the quarter. Assume zero starting inventory.

Modify the original spreadsheet model (available for download on Canvas) to include these new considerations. (You will need to insert several new rows into the spreadsheet, add changing cells, add constraints, etc.) Use Solver to determine the best advertising and production levels for each quarter, given the existing constraints of:

Total Advertising ≤ $1,000,000

Q1 Advertising ≤ $250,000 as well as any new constraints you determine are necessary.

Do not include any IF, MAX, MIN, ROUND, or similar Excel functions (these cause problems with Solver).

2. Omega Manufacturing Production Planning Problem.

The Omega Manufacturing Company has discontinued the production of a certain unprofitable product line. This act created considerable excess production capacity. Management is considering devoting this excess capacity to one or more of three products, products 1, 2, and 3. The machine-hours required for each unit of the respective products, along with the available capacity of the machines, are summarized in the table below.

	Machine-Hours Required per Unit				Available Time
	Product 1	Product 2	Product 3		(machine hours per week)
Milling Machine	4	6	3		1820
Lathe	6	0	5		1440
Grinder	0	4	3		1420

The unit profit would be $530, $800, and $1250, respectively, on products 1, 2, and 3. How many of each product should Omega Manufacturing produce so as to maximize their profits? Build a linear programming spreadsheet model, and solve it using Solver.