Devos Inc. sells bulk stationery supplies to businesses in El Paso. They have 4 product lines. Binders, Pencils, Pens, and Desks. Each of the Units represents one bulk order for a given product line. The selling price for each bulk unit is as follows: Item Price Binder $25 Desk 100 Pencil 8 Pens 12 The cost of goods sold for each bulk order is as follows: Item COGS Binder $ 12 Desk 40 Pencil 3 Pens 4 Their total variable selling expenses are estimated to be $10,000 for the year and are assigned proportionally to each product line based on its sales mix. Fixed costs for the year are estimated to be $6,800. (note: this is different from your practical application Ch. 3 homework where you were given monthly fixed costs and extrapolate that to yearly fixed costs. Here, you are given yearly fixed costs and you need to solve for BE or Target profit at a yearly amount so this number does not have to be transformed.) Monthly selling data for each product line can be found here: Analytical Project 1 Data for Students.xlsx For any answer that is a $ amount, do not include the $ in your input box. For any answer that is a %, do not include the % in your input box. Units 27 83 95 11 15 36 81 4 16 56 11 66 105 10 14 75 105 Monthly Sales Product Line Jan 20 Binders Jan 20 Pens Jan 20 Desks Jan 20 Pencils Feb 20 Desks Feb 20 Binders Feb 20 Pencils Feb 20 Penis Mar 20 Desks Mar 20 Binders Mar 20 Pencils Mar 20 Pens Apr 20 Desks Apr 20 Binders Apr 20 Pencils Apr 20 Pens May 20 Desks May 20 Binders May 20 Pencils May 20 Pens Jun 20 Desks Jun 20 Binders Jun 20 Pens Jun 20 Pencils Jul 20 Desks Jul 20 Binders Jul-20 Pencils Jul 20 Pens Aug 20 Desks Aur 20 Binders Aug 20 Pencils Aug 20 Pens Sep 20 Desks Sep 20 Binders Sep 20 Pencils Sep 20 Pens Oct 20 Desks Oct 20 Binders Oct 20 Pencils Oct 20 Pers Nov 20 Desks Nov 20 Binders Nov 20 Pencils Nov 20 Pens Dec 20 Desks Dec 20 Pencils Dec 20 Binders Dec 20 Pers 15 75 90 19 101 13 32 105 3 10 11 35 101 7 28 73 8 14 25 101 What is the sales mix percentage of bulk Pencils that sold in October based on the Excel file you were provided from the very beginning instructions? There are two ways to do this problem. a. You can calculate monthly sales total for each product line in a given month based on the units in the data file provided. (Hint: You will first need to calculate the monthly sales total for each product line using the SUMIF function. Then you create use Excel's Chart function to prepare a 100% stacked bar chart of the monthly sales mix based on your calculations for each product line. You then just need to hover your mouse over your graph to see the percentage. If you used the actual sales data from the Chapter 3 practical exercise about the practice and tournament tennis ball sleeves, your results would look like this: MONTHLY SALES MIX VZ WZ NNNNNNN 100% 90% 80% 70% 60% 50% 40% 30% 20% 10% 0% Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Practice Tournament b. The other option is to just do the numerical calculations for each month without doing the graph and calculate what percentage of October's sales mix were bulk pencils. Round your answer to 1 decimal place. If your answer was 46.73%, you would enter 46.7 into the blank (or if you left it as a decimal without turning it into a percentage, your calculation would be 0.4673 and the answer you would enter in the blank would be 46.7)
