Can your spreadsheet handle inventory holding costs

Assignment Help Business Management
Reference no: EM13792487

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.

Reference no: EM13792487

Questions Cloud

What is the root of the conflict : Describe a conflict you are currently involved in. This conflict can be from any aspect of your life - What is the root of the conflict and what have you done well throughout the conflict and what should you have done differently?
Case study on the four functions of management : Students will read the case study that focuses on the four functions of management: planning, organizing, leading and controlling (P-O-L-C). You have been hired as a consultant to help Carl Thomas and his family to solve the problems with his bu..
Basic of rights available to women : Opponents and proponents of abortion have never had a chance to vote on its legality.
Evaluation of product research : Evaluation of Product Research
Can your spreadsheet handle inventory holding costs : 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.
The problems of the sarbanes-oxley ac : Examine the extent to which the whistleblower would be protected under the Sarbanes-Oxley Act. Justify your response.
Most recent presidential election : Research the most recent presidential election using the University online library resources and the Internet and write a paper on the topic. Include the following in your paper:
Assignment on human freedom and the news : Human Freedom and the News
How should management at happy valley hospital retain staff : How should the management at Happy Valley Hospital retain their existing staff members?

Reviews

Write a Review

 

Business Management Questions & Answers

  Determine the consequences of the risks

Determine the consequences of the risks are the potential consequences worth the risks?

  Match each strategic tendency with at least one country

Match each strategic tendency with at least one country or culture. Briefly explain why you feel each strategic predisposition appropriately matches the country or culture you selected.

  Job of facilitating learning on the jobsite

Safety and health managers can do a better job of facilitating learning on the jobsite if they understand certain principles of learning. According to the National Safety Council, those conducting training should have what characteristics?

  Explain how lincoln''s gettysburg address can be used

Explain how Lincoln's Gettysburg Address can be used to help us understand the principles of public leadership.

  Llustrate what advertising agency was attempting to create

The winning team comprised two staff members who put on a wet t-shirt display on an interstate overpass with television helicopter crews capturing and Showing footage on the local news. In the AIDA model, illustrate what the advertising agency was..

  Management - relevant market survey

What is a market survey? What factors determine the relevant market for a survey? Why is the definition of the relevant market so important?

  A joint venture is an attractive way

A joint venture is an attractive way for a company to enter a new industry when which one?

  Regulate the two tests that you consider the most important

Appraise the types of employee testing that companies may need that are discussed in the text. Regulate the two tests that you consider the most important

  Important information about management information system

Write six closed questions that cover the subject of decision-making style for the manager described in the scenario.

  Business moral ethics

Business moral ethics

  Analyze the development of trade ethics

Find out at least two reasons for studying trade ethics and analyze the development of trade ethics since the 1960s and postulate illustrate what issues will be at the heart of trade ethics ten (10) years from now. Explain your rationale. (150-200..

  Employee vs contractorwhat do you believe are the benefits

employee vs contractorwhat do you believe are the benefits to being an employee of a company vs. a contractor? which

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd