ZuoTi.Pro
Question

Can you please provide the formula for the worksheet also.
CASE PROBLEMS Level 1- Analyzing Sales for Crèmes Ice Cream Judd Hemming is the eastern regional marketing manager for Crèmes Ice Cream. Eac quarter, he completes two separate analyses: an analysis comparing ice cream flavor sale volumes from all regional locations with the same quarter sales volumes from the previou year and an analysis comparing total sales in dollars, including mean, median, mode, and standard deviation, of sales by store. The first analysis, sales by flavor, compares the total quantitics sold in gallons. The data collected lists for each flavor of the number of pints, gallons, and 10-gallon tubs sold for all stores. Pints and gallons are sold directly to the public, whereas 10-gallon tubs are used for in-store sales of cones, cups, and specialty items, such as sundaes and banana splits. To eliminate any impact of pricing changes or special promotions, Judd simply uses the ice cream volumes in gallons to compare sales by flavor. Judd has asked for your help this quarter in completing this analysis. Judd created two workbook files: Creme.xlsx, which contains the current quarters sales on a worksheet named Flavors, and HCreme.xisx, which contains the corresponding historical quarterly data for the previous year on a werksheet named HFlavors. For ease of data handling, the flavors in both data sets are in identical order except for two new flavors introduced this year, which appear at the bottom of the current data set. Keep in mind the following conversions when analyzing this data: There are 8 pints per gallon. . Each tub holds 10 gallons

Also, when carcuratu g down the values to the nearest whole tub. In Teanongy the data in the following categories: Tubs Used Total Gallons Total Gollons Current GtrSold Current Qi Sold Same Qr Flavor #Pint Containers #Gollon Last Year Sold Current Qtr Containers So Current Qi The second analysis you need to compiete is to summarize sales in dollars by store and com- pare the result with the previous years sales. The Stores workshect in the same workbook contains the individual store sales for the current quarter in dollars rounded to the nearest thousand dollars; this data is for the analysis by store. You nced to calculate some basic sta- tistics for store sales. In the same quarter of the previous year, these values were as follows: Mean: $8,817 . Median: $8,000 Mode: $5,500 . Standard Deviation: S6,920 Throughout the steps, when writing formulas, be certain to use the most efficient methor including the use of functio and relative and absolute cell referencing. Complete the following: 1.Open the workbook named Creme.xlsx located in the Chapter 2 folder, and then s 2. On the Flavors worksheet, for the current quarter, calculate for each flavor the t a separate area. Remember to round down the tub quantities to the nearest whole it as Creme Current Sales Analysis.xlsx number of gallons sold. List the conversion values used at the top of the workshe when completing the calculation. 3. Copy the values for total amount of ice cream sold for the corresponding flavor the HCreme.xisx workbook, found in the Chapter 2 folder, into the Current worksheet. 4. Calculate the overall total and mean number of gallons sold for all flavors fi quarter and for this quarter last year. 5. Calculate for cach flavor the percent of total gallons this flavor represents co with total sales for the current quarter of all flavors. Copy this formula to the a column to calculate the percent of total gallons this flavor represents compar historical total sales

Chopter 2 Solving Problems with Stotistical Analysis Tools and percent difference in sales, assuming a positive value represents an increase in sale Flavors without sales in both years should be left blank (these cells should be con pletely empty). 6. In the two adjacent columns, calculate for each flavor and for the totals the differen 7. Switch to the Stores worksheet. Calculate the mean, median, mode, and standan deviation for this data set. Label the cells so that they can be easily identified. each of these statistical values as compared with the historical values given in the prob lem description. Based on the changes, explain on the worksheet (just below your analysis), whether you feel stores are doing better this year and if sales in stores are 8.On the same worksheet, set up a table to analyze the change and percent change o more or lesslikely to vary from mean sales than they did last year. 9. Based on sales to all stores and total gallons sold for the current quarter, what is the price of a gallon of ice cream on average? Write a formula to determine this value and place it below the statistical analysis on the Stores worksheet. 10. Add appropriate worksheet titles and formatting to make the worksheets easy to read. 11. Save and close the Creme Current Sales Analysis.xlsx workbook Level 2 - Analyzing Demographic Data for La Rosa Restaurant You have recently decided to test your entrepreneurial skills by opening a restaurant that you plan to name La Rosa. In your restaurant, you plan to feature specialty desserts, along with fine cuisine. One critical decision you have to make is where to locate the restaurant. Right now fringe of several afluent suburbs (site X), and the other in the downtown district (site Y) Before making the decision, you have hired a local market research firm to provide you with some demograph that frequent othe you are considering two different locations -onc near a large retail area on the ics of the areas and the specific dining habits of the local population r restaurants in these areas. The on several worksheets in the LaRosa xls workbook raw results of this research h SiteX and SiteY) contains the detailed responses ol cach questions about theira

Another profitable facet of CKG Autos business is supplying parts for auto repal most critical component of the parts supply business is having enough of the right parts on hand so that repair shops can receive same-day delivery. The key to protts is minimizing the number of parts that need to be warehoused while also ensuring that sufticient parts are on hand to meet orders. Because each warehouse distribution center serves a different set of customers with different needs, each center must be considered separately. Distri- bution centers have a five-day lead time for ordering of parts; this must be taken into consideration when determining target inventory levels. The costs involved in warehousing the parts include the working capital tied up in the inventory itself (cost of each part), as well as the space to store the part. These costs can be substantial. On the other hand, alternative gencric parts are often available from rival suppliers, and keeping the auto repair centers supplied and customers satisfied is critical. One of the most problematic items to supply and store are bearings. This part is both highmet volume and relatively large, taking up considerable warehousing space. A bearing is also relatively expensive. So, analyzing bearing needs is a good place for CKG to start, specifi- cally in one of its largest distribution centers such as Central New Jersey, which serves 10 major customers. You have been asked to analyzs the bearing inventory level requirements for this center, including simalating demand levels based on 30-day historical extremes, calculating a target inventory level based on this simulation, and then comparing simulated values to actual values from the past five days from the targeted warehouse Complete the following: 1.Open the workbook named Parts.xlsx located in the Chapter 2 folder, and then save it as CKG Parts Analysis.xlsx 2. Modify the format of the data on the Bearing Data worksheet so that zero values are displayed with a 0 instead of the default dash, aligned in the Is column 3.On the BearingData worksheet, take the existing 30-day data for these bearings for cach customer to calculate the high and low limits of the bearing demand by customer 4.On a new worksheet named Simulation, use the high/low limits you just calculated to simulate daily requirements for each customer to obtain a combined daily require ment. Assume that the daily requirements will vary for each customer randomly between the high/low limits you have calculated from the existing 30-day data for that customer. Generate the data for approximatcly 100 instances, and then copy the results as values to a new worksheet in the workbook, keeping the original analysis intact on the Simulation worksheet so that it can be used again later. Name tbe new workshect th am the sy to Simulation Data 1

the daily mean, mode, median, and standard deviation for the combined requirements of all 10 customers. S. On the Simulation Data 1 worksbeet, use the daily demand total data to calculate 6. Extend your analysis (on the same worksheet) to inchude a ranking of the data (1 to 100) so that the day with the least total demand has the lowest ranking (rank 1) 7. To the right of the data on this same worksheet, create a listing of the top and bottom five daily combined demands from the 100 simulated instances. Clearly identify this listing using borders and shading. 8. Recommend a target inventory level needed for a five-day period based on the fol- lowing; to be on the cautious side, assume cach days supply will be equai to the average daily demand for all locations combined, plus two standard deviations: Because the mean and standard deviations might not already be integers, round the . daily demand up to the nearest whole number. Use the daily demand to calculate demand over a five-day period. Place this recommendation just below the top/bottom analysis, again clearly identi fying it. Add the label Bearing Recommended to identify the cell containing the actual valuc 9. The warehouse manager has tracked a total of five different parts over the past ive days, recording for cach shipment the value of the part and the number of days it was in storage. One of these tracked parts is the bearing you have just analyzed. The data has been compiled in a workbook named Demand.xlsx. Each line item represents a single shipment of one item. Copy the data from the Demand xlsx workbook, located in the Chapter 2 folder, to your workbook and place it on a sheet named Actual Demand 10. On the same worksheet, summarize the data to determine the number shipped by part the total values of those shipments by part, and the average number of days that part was stored, using the format shown in Table 2.15. Toble 2.15: Setup for parts dato summary en Descipion sp Shipped Total Dollar Value of Items veroge iDays Held in Belts Fan Belt Boord MacBook Air

In the #Items shipped column, write the necessary formula to determine the number of bearings shipped. Write the formula so that it can be copied down the column to auto- matically determine the number of timing belts shipped, the number of air filters shipped, and so on. In the Total Dollar Value of Items Shipped column, write the necessary formula to determine the value of all bearings shipped. Again, write the formula so that it can be copied down the column to automatically determine the value of timing belts, air filters, and so on. In the Average #Days Held in inventory column, write the necessary formula to determine the average number of days bearings shipped were held in inventory; again write the formula so that it can be copied down the column. Be certain that these formulas will work even if the data is updated in the future. 11. Based on the recommended inventory level you previously calculated for bearings, would you have had enough bearings in the warehouse to cover these orders? Place your answer in a cell below your analysis on the Actual Demand workshcet. Clearly identify this answer and highlight it in yellow 12. Double-check all values and formulas for correct implementation. Be certain that you included sufficient formatting and titles to clearly identify the worksheet clements. 13. Save and close the CKG Parts Analysis.xlsx workbook

6297.00 1,043.77 15,549.00 6,550.37 3,231.00 1,88784 2,489.00 164 254 215,191 27,503 57,513 76,236 38,361 99,103 91,186 Vanilla Chocolate Maple Walnut Praline Butter Pecan Chocolate Chip Mint Chocolate Chip Cookie Dough Rocky Road Fudge Swirl 745.07 2,571.00 4,691.00 1981.00 1,229.00 3,040.001,023.65 3,430.00 2,800.001,234.45 1423.00 1233.91 10 46,187 51,974 65,808 13 Peach 14 Marshmallow Fudge Coffee Mocha Chip 8,467 228,592 722.00 2 130,943 4,311.00 185.74 Watermelon 54,154 269200 1478.99 3,083.00 3.536.00 1,597.04 20 Cinnamon Spice Chocolate Fudge 47,952 69,125 8 175.68 37 Stores

2630 orn& Sales 101 2,630.00 10,020.00 1039,730.00 104 14150.00 105 1,750.00 106 1,710.00 07 12,950.00 108 18,190.00 5,520.00 110 13,020.00 111 14,590.00 112 11,640.00 113 9,270.0 114 12,200.00 115 19,240.00 116 19,280.00 117 1,440.00 8,020.00 119 9.350.00 120 1,790.00 640.00 122 15.150.00 123 17,040.00 1,930.00 125 16,370.00 26 12,970.00 127 1,450.00 2,550.00 128 5,030.00 6,350.00 5,010.00 132 6,940.00 133 6,040.00 134 13,240.00 35 7,320.00 136 10,780.00 137 16.820.00 6.450.00 130 4 3,820.00 140 17,010.00 14.330.00 5,910.00 143 10,790.00 144 14,720.00 2,230.00 141 6.120.00 147 17 210 00 10.420 00 8 610.00 Stores

Orders for a Sample 30-Day Perou Customer# ay 1001 1038 1147 1249 1355 1397 1453 1533 1575 16 12 13 93 4 10 2 10 10 3 6 6 4 16 41 10 12 1 12 11 2 81 3 10 2 2 5 6 14 811 9 9 6 3 10 5 10 12 9 472 10 94 10 119 43 8 13 11 10 12 13 14 12 3 9 6 133 9 5 14 15 5 7 63 72 13 15 34 46 13 2 10 16 17 18 19 1365 66 12 1 20 10 59 14 5 10. 12 10 9 6 14 12 12 10 10 23 13 24 25 26 27 28 12 29 30 10 5 10 12 13 4 13 2 13 13 10 12 12 15 2 2

math   Statistics-And-Probability   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

fast ๆ.deshon , since cou have cttackool san een S1吆 아 ald attacheol the excel bcok on even ag R-ste.we basic defa soap Goald ωοΥ k On tLe Loevel can explain, can eaplaun, you how u Con cook hou ot ueshon & andous Yars chang Mean 835 12- ssoo 2920 222S 8/. Median 9358600 170ゾ Modo936 Deviahon 195 Hs Canfuned! to the Comfeny is doing 386 } Ck Ce cacam.pu usempan of sles value and mean of o quanh mcan of avesage sale pellon Sals quantity to asrive of ou havo to ca (atehe assent pa ied lod Ce (culeted meen of cales%-23,120(flavo aos skel)and s 12 les vale nean Poyy Pro stoves sheel sse Per allon- Gouy 23120 0.39,8 |gallons

0 0
Add a comment
Know the answer?
Add Answer to:
Can you please provide the formula for the worksheet also. CASE PROBLEMS Level 1- Analyzing Sales...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT