Really confused as to how I can solve #1 and since we enter this into an excel sheet if I mess up on question #1 then it screws up everything else. Can you help? This is a marketing/accounting problem. I have attached the word document with the information and questions. Thank You!!!
MKTG 311 Winter 2014 Group Assignment #5 As you complete this assignment, make sure that you follow all the instructions below. Due: THURSDAY, February 20, at the beginning of class. Late submissions will not be accepted. You need to complete this assignment using the capabilities of Excel. Use the Excel template provided to complete this assignment. The template is set up so that your answers should go into the highlighted spaces. You should have plenty of room to complete each problem within Excel in the space given (show your intermediate steps). On the first problem lots of labels are shown to get you started. This level of detail isn't given for the later problems, you should add your own detail as you work the problems. NOTE that you need to fill in column H each time you use a calculation. This will help you understand the calculations and help prepare you for the exam. Submission Instructions Your printed solution needs to have two parts. Part 1 should contain your solution, showing all work and intermediate steps, in an easy to follow manner. Be sure to label your work completely. Part 2 should be a printout of your equations used in Excel. Note that part 2 should be a copy of Part 1, but show equations when they are used instead of numbers; all other information in Part 2 should match your information as shown in Part 1 (numbers that were given and labels). In your file, your solution should be on Sheet1 of your spread sheet, your equations should appear on Sheet 2. If you do not know how to submit your solution on D2L, watch the related web cast tutorial. Formatting Instructions: Dollar figures should be reported to the nearest cent, percentages to two decimal places, units should be given as whole numbers. Let Excel do the rounding for you and use the ROUNDUP function in Excel when applicable. Be sure to print your name at the top of your solution. Your solution should be printed so that it is no wider than a regular sheet of 8-1/2" x 11" paper, printed in landscape. There is no need to print your solution in color (don't worry about the highlights not being yellow when printed). Failure to turn in a hard copy of your solution (2 pages) will result in a 20% deduction on this assignment (if you turn in neither a hard or electronic copy, your score will be zero). Problem Set Assume you have just become a marketing manager for the 747 Classic model of Swingline staplers (note that in this case you work for the manufacturer of the product, so your figures need to be from the manufacturer's viewpoint). Your product carries a retail price of $18.98. Retailers mark up the product 45% based on selling price (a margin of 45%), while wholesalers take a 17% mark-up based on cost. Variable manufacturing costs for the 747 are $5.63 per unit. Fixed manufacturing costs are $1,742,000. The contracted advertising budget for the Classic 747 is $1,220,000. Your salary expenses total $253,000. The salespeople for the brand are paid entirely on commission, which is 11.5%. Direct administrative costs, related to selling and shipping Classic 747s are $0.31 per unit. In fiscal year 2011, the total market for staplers was 35.6 million units. The Classic 747 has 5.8% of the market. For 2013, what is? 1. The unit contribution of the Classic 747? 2. The breakeven point (in units) for the Classic 747? 3. The market share the Classic 747 needs to break even? 4. The Classic 747's 2011 profit? In fiscal year 2012, industry demand is expected to decrease to 34.2 million units per year. You are considering raising your advertising budget to $1.45 million, which you feel will increase your market share by 1.5 percentage points. In 2012, if the advertising budget is increased to $1.45 million (holding all other costs steady): 5. How many Classic 747s will have to be sold to break even? 6. How many Classic 747s will have to sell for the same profit to be made as in 2011? 7. Based on the information you have, should Swingline increase their advertising budget (yes or no, plus one sentence of reasoning, including one assumption)? Another option you have is to not increase the advertising budget. Instead, you think you might give retailers an incentive to promote the Classic 747 by getting them to raise their margins (mark ups based on selling price) from 45% to 52%. This increase would be accomplished by lowering the price of the product to wholesalers and, in turn, to retailers (assume that there will be no change in retail price). With this strategy, you feel your market share will increase by 2.8% in 2012. Wholesaler mark-up based on cost would remain the same at 17%. If retailer mark ups based on price are raised to 52% in 2012, then (assume all other factors are steady): 8. How many Classic 747s will have to be sold to have the same profits in 2012 as in 2011? 9. How many Classic 747s will have to be sold to break even? 10. Based on the information you have, should Swingline allow retailers to increase their margins to 52% in exchange for promoting the Classic 747 (yes or no, plus one sentence of reasoning, including one assumption)? (30 points)
