use case study to answer excel sheets

It was early February, shipments for the 1993 biking season were due to commence in less than three weeks, and Frank Drailleur, vice president, Marketing, at Monterey, California-based 21- Speed Gizmos, Inc., had yet to decide the price for the company's latest product, the Digital Cyclaudio System (DCS). The pricing decision could not be put off much longer: the price would affect sales which, in turn, would have an impact on production levels; distributors were holding back on orders until they knew the suggested retail and distributor prices; and product brochures had to be printed, and these needed information on pricing. Background 21-Speed Gizmos, Inc., was founded in 1987 by Paula Wheelman and Frank Drailleur, two cycling enthusiasts who first met while on a bike trip in Bali, Indonesia, and joked about quitting their investment-banking jobs, getting out of New York City, moving to California, and starting a company to develop and market electronic products for the serious cyclist. The Indonesian trip was followed by other trips, and what started as idle musings became serious personal and business proposals: (1) in June 1987, Wheelman and Drailleur got married while on a bike trip in Portugal; and (2) two months later, they moved to California and founded 21-Speed Gizmos, with Wheelman as president and Drailleur as vice president, Marketing. 21-Speed's first product was the Velocipedometer, a device to measure speed, time, and distance traveled. Three years later, in 1990, the company introduced the Skyline Cartograph, an electronic display of the maps of 50 popular biking routes in the San Francisco bay area; a Seattle, Washington, version of Cartograph followed in 1991. Now, in 1993, 21-Speed was about to introduce its third new product, the Digital Cyclaudio System, or DCS. DCS was Wheelman's idea. She had seen other cyclists use the popular Sony Walkman and other similar systems, but was concerned that the systems' earphones were a safety hazard. "Wouldn't it be nice," Wheelman remembered telling Drailleur back in 1988, "if we could design a digital cassette system with speakers that would wrap around the handlebars and for good measure-have a power supply that can be charged while the cyclist pedals?" Drailleur was considering three pricing options: $124.95, $99.95, and $74.95. At these suggested retail prices, the anticipated first-year demand would be 25,000, 37,500, and 55,000, respectively. Demand for at least the next two years was expected to grow at approximately 20%; the rate of growth could be higher if Drailleur chose a low price, and there was the threat of a competitive entry, but these were the only "hard" numbers that Drailleur had. 21-Speed typically gave the distributor a 30% margin on the suggested retail price. Most of the components required for DCS would be sourced out, but 21-Speed would do the final assembly at its manufacturing plant in Merced, a town near California's Yosemite National Park. Each assembled unit of DCS would require $24.50 in materials and one-quarter hour of direct labor (@ $20.50 per hour). 21-Speed had acquired special equipment for DCS assembly for $200,000; the equipment would be depreciated on a straight-line basis over ten years. Drailleur had been informed that overhead and other fixed costs at the Merced plant would increase by around $400,000 on account of the new product. In addition, Drailleur would have to budget for marketing and administrative expenses; 21-Speed typically estimated these at 15% of sales. 21-Speed's marginal tax rate was 35%. A B D E F G H 1 High $124.95 25000 Medium $99.95 37500 Low $74.95 55000 1 21 Speed Gizmos 2 3 Retail Price 4 Expected demand 5 6 Variable Cost Data: 7 8 9 10 Fixed Cost Data: 11 12 13 14 15 Distributor Margin: 16 17 Q1: CALCULATE MANUFACTURER PRICE 18 19 Manufacturer Price: 20 21 22 23 30% High Medium Low H 1 J K L M N C A B D E F G 1 21-Speed Gizmos 2 High Medium Low 3 Retail Price $124.95 $99.95 $74.95 4 Expected demand 25000 37500 55000 5 6 Variable Cost Data: 7 8 9 10 11 Fixed Cost Data: 12 13 14 15 16 17 Distributor Margin: 30% 18 19 Q3: WHAT IS THE NET PROFIT AT EACH PRICE POINT? HOW WOULD YOU PRICE the DCS? 20 21 High Medium Low 22 Total Revenue: 23 24 Total Variable Cost 25 26 Total Contribution: 27 28 Total Fixed Costs: 29 30 Net Profit Before Taxes: 31 F G H 1 J K L M M B D E 1 21-Speed Gizmos 2 High Medium Low 3 3 Retail Price $124.95 $99.95 $74.95 4 Expected demand 25000 37500 55000 5 6 Variable Cost Data: 7 8 9 10 11 Fixed Cost Data: 12 13 14 15 16 17 Distributor Margin: 30% 18 19 Q2: WHAT IS THE BREAK-EVEN VOLUME AT EACH PRICE POINT? 20 21 Selling Price: 22 22 23 Unit Variable Costs: 20 24 25 Unit Contribution: 26 27 28 Total Fixed Costs: 29 30 31 Break-even Volume: 32 33 34 35 36