Analytics Assignment
Instructions:
• Answer all problems completely using an Excel spreadsheet. Do not use it as a word processor; use cell address formulas for necessary calculations. No credit will be given if you do not use proper formulas in cells.
• You may wish to use a Word document for your qualitative answers.
• On your spreadsheet and, if you use one, Word document, clearly label which question you are answering. Do not include the questions themselves anywhere-I know what they are.
• Remember that the work you submit must be your own. Answers identical or suspiciously similar to those of your classmates' constitute cheating and will not be tolerated.
• Please review the Analytics Hints posted on Blackboard for help on these problems.
1. Alpha Auto sells high-end "previously owned" cars, such as Audis, BMWs, etc. Alpha is looking at opening a new showroom and has completed a detailed marketing plan, based on financial data from the original branch. It expects sales to be $2,650,000. The cost of the cars is 50% of sales. Salespeople at the original branch are paid a base salary plus a commission of 20% of each car they sell. Alpha expects to use this compensation plan at the new branch but has added an additional 5% to the commission rate to attract good salespeople. Base salaries are forecast at $250,000. Alpha is planning to use newspapers as its primary advertising media, and will use these to announce a special first year promotion of a 5% rebate on each car sold. Advertising cost is $10,000. Rent and other overhead costs for the showroom are projected at $125,000, and administrative salaries and expenses at $110,000.
a. Develop a pro forma income statement for the first year of Alpha's new branch using the information given.
b. Given some concerns about economic conditions, Alpha would like to know how this new showroom would do if sales are 10% less than forecast. Develop a second pro forma income statement for this situation.
c. Describe at least one marketing action Alpha could take to address the situation in question 2b and develop a third pro forma to illustrate its impact. You can make reasonable assumptions about what will happen-just indicate what they are.
2. Alpha would like to develop some additional financial information to help them examine the potential success of the new showroom the first year (without considering the 10% sales reduction).
a. Complete a performance analysis for Alpha Auto's new branch for the first year.
b. What is Alpha's dollar break-even point for the new branch?
c. What does this tell you about Alpha's new branch? What marketing actions should Alpha take?
3. Pleasingly Punch is a tropical fruit drink consisting of 100% pineapple, mango, and papaya juices, sold in industry standard 16-ounce bottles, 12 to a case (also the industry standard). The producers, who started and have been in business in Massachusetts for over ten years, are considering expanding to Rhode Island. Last year's sales were about 180,000 cases. While Pleasingly does not have juice consumption data for the New England area, they do have some national data which includes share data for the four varieties in the juice/juice drinks category, based on their fruit content: 100% juice (54.9%), nectars containing 25% to 99% juice (6.1%), juice drinks with up to 25% juice (33.7), and fruit-flavored drinks with no juice (5.3%). They have also learned that annual juice consumption is about 8.6 gallons per person. A check of census data reveals that there are a total of 14,681 million people in New England, with 6.745 million of them residing in Massachusetts and 1.055 million of them in RI. Based on the information you have:
a. What is Pleasingly's current share of the Massachusetts juice/juice drinks market?
b. How many total cases will Pleasingly sell if they add Rhode Island at the current share rate?
c. Does this seem reasonable? Why or why not?
d. A major juice drink company is considering purchasing Pleasingly and considering expanding distribution to all of New England. This company will not invest unless they can achieve at least a 1.5% market share of the segment they're investing in. How many cases would this mean for Pleasingly?
4. Worldwide Wire makes specialized wire and cable for the high speed data transmission industry. Worldwide has about 1000 customers and currently uses its own sales force. Sales force members are paid a base salary of $40,000 plus a commission of 4% on their individual sales. Each salesperson currently spends about two hours, including travel time, once a quarter with each customer. In between visits, salespeople spend an additional three hours per quarter on phone calls and emails for each customer. The remaining 40% of their time is spent on non-customer specific matters (expense reports, internal meetings, product training, general sales analysis and forecasting, etc.).
Worldwide is introducing a new type of wire which will double the amount of data carried over existing wire. It uses a specialized material that customers will be unfamiliar with. Worldwide estimates that its salesforce will need to spend an additional one and a half hours per account per quarter introducing the product and training customers on how to integrate it into their products. Recognizing the amount of time its reps spend on non-customer tasks, Worldwide is investigating some process improvements that would reduce the time reps spend on non-customer administrative tasks by a quarter. With the new product, Worldwide expects total annual sales to be about $26 million. (Hints: Make reasonable weekly hours and vacation time for Worldwide's reps. Think about where you would allocate the customer-related emails and phone calls. Note that sales force size analysis is usually done on an annual basis.)
a. How many sales reps should Worldwide have before the new product addition?
b. How many would they need with the new product and sales process improvements?
c. Worldwise is concerned about the financial implications of adding this new product and has asked the sales department to consider using and independent sales agency to reduce expenses. If the agency is paid 8% of sales, what sales volume would be required to justify this choice?
d. What are the implications of using independent reps for Worldwide?