B) What Excel Function Is Useful for Calculating Revenue? Explain Why It Is Useful

Question 1:

A t-shirt company is planning a production run for an event where the attendance (and thus demand for t-shirts) is uncertain. The event planners have indicated that they think the attendance will be 500, 750 or 1000, with probabilities of 30%, 50% and 20% respectively. The company must pre-order the blank t-shirts (cost=$5 per shirt) and it can sell finished shirts for $12 apiece. Any finished shirts that cannot be sold at the event can be sold for $2 apiece to a used clothing vendor.

A) What Excel function is useful for calculating the expected value of demand for t-shirts? What is the expected demand?

B) What Excel function is useful for calculating revenue? Explain why it is useful.

C) What are the two possible cases for the amount of t-shirts that will be sold to the used clothing vendor? How would you calculate this amount in a spreadsheet model?

D) Suppose that blank t-shirts can only be ordered from the wholesale vendor in batches of 100? How many t-shirts should the company order?

E) Suppose now that blank t-shirts can only be ordered from the wholesale vendor in batches of 50? How many t-shirts should the company order?

Question 2:

Western Chassis produces high-quality polished steel and aluminum sheeting and two lines of industrial chassis for the rack mounting of Internet routers, modems, and other telecommunications equipment. The contribution margin (contribution toward profit) for steel sheeting is $0.40 per pound and for aluminum sheeting is $0.60 per pound. Western earns $12 contribution on the sale of a Standard chassis rack and $15 contribution on a deluxe chassis rack. During the next production cycle, Western can buy and use up to 25,800 pounds of raw unfinished steel either in sheeting or in chassis. Similarly, 20,400 pounds of aluminum are available. One standard chassis rack requires 16 pounds of steel and 8 pounds of aluminum. A Deluxe chassis rack requires 12 pounds of each metal. The output of metal sheeting is restricted only by the capacity of the polisher. For the next production cycle, the polisher can handle any mix of the two metals up to 4,000 pounds of metal sheeting. Chassis manufacture can be restricted by either metal stamping or assembly operations; no polishing is required. During the cycle no more than 2,500 total chassis can be stamped, and there will be 920 hours of assembly time available. The assembly time required is 24 minutes for the Standard chassis rack and 36 minutes for the Deluxe chassis rack. Finally, market conditions limit the number of Standard chassis racks sold to no more than 1,200 Standard and no more than 1,000 Deluxe. Any quantities of metal sheeting can be sold.

A) Find an optimal solution to Western's problem. What is the production plan, and what is the total revenue?

B) Obtain a sensitivity report for the solution reported in Part 1. Which constraints are binding?

C)What is the incremental contribution associated with adding an hour of assembly time? Over what range of increase is the marginal value valid?

D) What is the value of additional capacity on the polisher? How much increase and decrease in this capacity is possible before a change occurs in the optimal production schedule?

E) An advertising agency has devised a marketing plan for the Western Chassis Company that will increase the market for Deluxe chassis. The plan will increase demand by 75 Deluxe chassis per month at a cost of $100 per month. Should Western adopt the plan? Briefly explain why.

Question 3:

A construction company is preparing for a nine-month project, and will need to develop a staffing plan. The company can assign up to 30 of its own full-time employees to the project, and will hire short-term contract employees to make up any shortage in meeting the personnel requirements. Company employees earn $6,000 per month, while short-term contract employees make $8,600/month. Contract employees can be assigned to the project beginning in any month, and their contract period is two months. The number of workers required for the project by month is shown below:

A) Determine the optimal staffing plan for the project.

B) The project manager is evaluating options to complete the project early so that the company can earn a bonus. He has determined that the project schedule can be compressed into a six-month schedule, with the same total number of worker-months. In that case, the staffing requirements are as shown below.

Develop an optimal staffing plan for the project under the accelerated schedule.

C) Suppose the bonus for completing the project three months early is $250,000. What would be the net bonus to the company, after adjusting for any difference in personnel costs under the accelerated schedule?