ClientID DM-341 YR-125 YR-133 YR-166 DM-412 DM-652 YR-316 PT-124 PT-783 DM-643 PT-532 YR-487 DM-762 Nu Trends Market Research First Quarter Marketing Plans Client Consultant Mktg Plan Budget Deering Industries Debhee Morenz 358000 Precision Design and Packaging Yolanda Robertson 542000 Real Photography Yolanda Robertson 158IOD Dollar Wise Financial Services Yolanda Robertson 754000 Macadam Realty Debbie Morenz 4960100 Cedarview Construction Debbie Morenz 2100XIOD Capstan Marine Products Yolanda Robertson 543000 Northwest Supplies and Production Petr Tomasz 386000 Everureen Products Petr Tomasz 574000 Vanlage Videos Debbie Morenz 285000 AllClaims Insurance Brokers Petr Tomasz 845000 Pizza by Mario Yolanda Robertson 280X100 O'Donovan & Sullivan Law Associates Debbie Morenz 112000 Campaign Start Date 2/2/15 1/12/15 1/19/15 2/2/15 3/2/15 3/23/15 2/16/15 2/2/15 3/2/15 1/12/15 1/12/15 3/2/15 1/16/15 Planned Expenditures Ist Month 2nd Month 3rd Month TOXIODI 2000 58IXO 1 SCOIX 250000 142010 500000 SOLO 586000 3XIODO 25400 2000XIO 196000 200X 1000XIO 750000 750XXO LMI00 IU 143000 200XXXI 2001XIO 100000 186000 100000 2500) 250 12500X 12500X 20X1000 401XXI 2450XIO 8000 1 SOXMOKI SODIO 52000 30%O 30000 74000 35000 Case Study Apply Your Skills Port Yolanda Robertson of NoTrends Market Research was pleased with your previous 1 Yolanda is preparing a marketing plan for a franchise expansion for the owners of Pizza By Mario. The franchise was started in Michigan and has stores in Ohio, Wisconsin, and lowa. The owners plan to double the number of locations within the next two years by expanding into neighboring states. The owners have provided a confidential franchise sales report to Yolanda in an Excel file named PBMSales.xlsx. Yolanda needs your help with Excel to extract some statistics and calculate franchise royalty payments. With this information, Yolanda will develop a franchise communication package for prospective franchisees. Open the workbook and save it as EL2-C2-CS-P1-PBMSales, Yolanda has asked for the following statistics: A count of the number of stores with sales greater than $500,000 A count of the number of stores located in Michigan with sales greater than $500,000 Average sales for the stores in Detroit, Michigan Average sales for the Michigan stores established prior to 2004 Total sales for stores established prior to 2010 Total sales for the Michigan stores established prior to 2010 Create the above formulas for Yolanda in rows 3 to 16 of columns H and L. Create range names for the data so Yolanda will be able to easily understand the formula when she reviews the worksheet. You determine the layout, labels, and other formats for the statistics section. The royalty rate and fee will be completed in Part 2. Save and print the worksheet. Part in the marketing package for new prospects, Yolanda plans to include sample sales figures and related franchise royalty payments. Pizza By Mario charges each 2 store a royalty percentage based on its annual sales. As sales increase, the royalty percentage increases. For example, a store that sells S430,000 pays a royalty of 2% of sales, while a store that sells $765,000 pays a royalty of 5% of sales. A royalty rate table is included in the worksheet. Create a range name for the table and then create a lookup formula to insert the correct royalty percentage for each store in column F Next, create a formula to calculate the dollar amount of the royalty payment based on the store's sales multiplied by the percentage value in column F. Format the royalty percentage and royalty fee columns appropriately Save the revised workbook and name it EL2-C2-CS-P2-PBMSales. Print the worksheet