Hello, I would like a little help with this case study, particularly on how to use Excel functions to solve the given problems. Thank you.
Case Study BIBLIOGRAPHY 21 Food and Beverages at Southwestern University Football Games Southwestern University (SWU), a large state college in Stephenville, Texas, 30 miles southwest of the Dallas/Fort SELLING Worth metroplex, enrolls close to 20,000 students. The school ITEM VARIABLE PRICE/UNIT PERCENT COST/UNIT is the dominant force in the small city, with more students dur- REVENUE ing fall and spring than permanent residents. Soft drink $1.50 $0.75 25% A longtime football powerhouse, SWU is a member of the Coffee 2.00 .50 25 Big Eleven conference and is usually in the top 20 in college Hot dogs 2.00 0.80 20 football rankings. To bolster its chances of reaching the elu- Hamburgers 2.50 1.00 20 sive and long-desired number-one ranking, in 2013 SWU hired Misc. snacks the legendary Billy Bob Dillon as its head coach. Although the 1.00 0.40 10 number-one ranking remained out of reach, attendance at the five Saturday home games each year increased. Prior to Dillon's arrival, attendance generally averaged 25,000-29,000. Season square foot per game; and six people per booth in each of the ticket sales bumped up by 10,000 just with the announcement six booths for 5 hours at $7 an hour. These fixed costs will of the new coach's arrival. Stephenville and SWU were ready to be proportionately allocated to each of the products based on move to the big time! the percentages provided in the table. For example, the revenue With the growth in attendance came more fame, the need from soft drinks would be expected to cover 25% of the total for a bigger stadium, and more complaints about seating, park- fixed costs. ing, long lines, and concession stand prices. Southwestern Uni- Maddux wants to be sure that he has a number of things versity's president, Dr. Marty Starr, was concerned not only for President Starr: (1) the total fixed cost that must be covered at each of the games; (2) the portion of the fixed cost allocated about the cost of expanding the existing stadium versus building to each of the items; (3) what his unit sales would be at break- a new stadium but also about the ancillary activities. He wanted even for each item-that is, what sales of soft drinks, coffee, to be sure that these various support activities generated revenue hot dogs, and hamburgers are necessary to cover the portion of adequate to pay for themselves. Consequently, he wanted the the fixed cost allocated to each of these items; (4) what the dol- parking lots, game programs, and food service to all be han- lar sales for each of these would be at these break-even points; dled as profit centers. At a recent meeting discussing the new and (5) realistic sales estimates per attendee for attendance of stadium, Starr told the stadium manager, Hank Maddux, to de- 60,000 and 35,000. (In other words, he wants to know how velop a break-even chart and related data for each of the centers. many dollars each attendee is spending on food at his projected He instructed Maddux to have the food service area break-even break-even sales at present and if attendance grows to 60,000.) report ready for the next meeting. After discussion with other He felt this last piece of information would be helpful to under- facility managers and his subordinates, Maddux developed the stand how realistic the assumptions of his model are, and this following table showing the suggested selling prices, and his information could be compared with similar figures from previ- estimate of variable costs, and the percent revenue by item. It ous seasons. also provides an estimate of the percentage of the total revenues that would be expected for each of the items based on historical Discussion Question sales data. 1. Prepare a brief report with the items noted so it is ready Maddux's fixed costs are interesting. He estimated that for Dr. Starr at the next meeting. the prorated portion of the stadium cost would be as follows: salaries for food services at $100,000 ($20,000 for each of the Adapted from J. Heizer and B. Render. Operations Management, 6th ed. Up- five home games); 2,400 square feet of stadium space at $2 per per Saddle River, NJ: Prentice Hall, 2000, pp. 274-275. Bibliography Ackoff, R. L. Scientific Method: Optimizing Applied Research Decisions, New Dekker, Rommert, Jacqueline Bloemhof, and Ioannis Mallidis . "Operations Research for Green Logistics-An Overview of Aspects, Issues, Con- York: John Wiley & Sons, Inc., 1962. tributions and Challenges," European Journal of Operational Research Beam, Carrie. "ASP, the Art and Science of Practice: How I Started an OR/MS 219, 3 (June 2012): 671-679. Consulting Practice with a Laptop, a Phone, and a PhD," Interfaces 34 Dutta, Goutam. "Lessons for Success in OR/MS Practice Gained from Experi- (July-August 2004): 265-271. ences in Indian and U.S. Steel Plants," Interfaces 30, 5 (September- Board, John, Charles Sutcliffe, and William T. Ziemba. "Applying Operations October 2000): 23-30. Research Techniques to Financial Markets," Interfaces 33 (March-April Fom, Sean B., and Eyong B. Kim. "A Survey of Decision Support System al of the Operational Research Society
