Please show step by step, thank you!

Yolanda Robertson, your manager at NuTrends Market Research, has been pleased with your previous work and has assigned you to a project with a new client. 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 Iowa. 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 with the name 2-PBMSales. Yolanda has asked for the following statistics and wants you to read the instructions found on the next page before you begin.

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 2006 Total sales for the stores established prior to 2014

Total sales for the Michigan stores established prior to 2014Create the formulas for Yolanda in rows 3 to 16 of columns H and I. 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 the workbook.

\

Case Study 2

In the marketing package for new prospects, Yolanda plans to include sample sales figures and related franchise royalty payments. Pizza By Mario charges each store a royalty percentage based on its annual sales. As sales increase, the royalty percentage increases. For example, a store that sells $430,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 in column F. Format the royalty percentage and royalty fee columns appropriately. Save the revised workbook.

E6 4 fx 652146 B E F . City Ohio 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Store 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 Detroit Detroit Flint Ann Arbor Ann Arbor Kalamazoo Port Huron Toledo Toledo Toledo Akron Cincinnati Madison Madison Milwaukee Milwaukee Milwaukee Cedar Rapids Cedar Rapids Cedar Rapids Des Moines Lansing Green Bay Columbus Davenport Livonia Pizza by Mario 2021 Franchise Sales State Gross Sales Net Income Michigan 2005 652,458 160,115 Michigan 2005 466,153 112,540 Michigan 2006 324,965 82,375 Michigan 2007 652146 166,050 Michigan 2007 378,458 92,610 Michigan 2007 528,346 130,250 Michigan 2008 345,892 88,965 Ohio 2008 653,485 167,458 2008 354,268 90,525 Ohio 2009 554,213 133,553 Ohio 2009 461,853 110,258 Ohio 2010 512,463 117,653 Wisconsin 2010 325,496 80,450 Wisconsin 2010 745,865 195,635 Wisconsin 2012 451,236 118,457 Wisconsin 2012 554,168 130,200 Wisconsin 2012 653,856 166,352 lowa 2013 541,285 142,875 lowa 2013 347,856 80,357 lowa 2015 568,457 137,865 lowa 2015 641,257 155,238 Michigan 2016 347,865 66,370 Wisconsin 2018 401,588 66,200 Ohio 2019 504,796 111,055 lowa 2019 310,496 53,370 Michigan 2020 314,698 66,087 % of Sales 24.5% 24.1% 25.3% 25.5% 24.5% 24.7% 25.796 25.6% 25.6% 24.1% 23.9% 23.0% 24.7% 26.2% 26.3% 23.5% 25.4% 26.4% 23.1% 24.3% 24.2% 19.1% 16.5% 22.0% 17.2% 21.0% E6 4 fx 652146 B E F . City Ohio 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 Store 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 Detroit Detroit Flint Ann Arbor Ann Arbor Kalamazoo Port Huron Toledo Toledo Toledo Akron Cincinnati Madison Madison Milwaukee Milwaukee Milwaukee Cedar Rapids Cedar Rapids Cedar Rapids Des Moines Lansing Green Bay Columbus Davenport Livonia Pizza by Mario 2021 Franchise Sales State Gross Sales Net Income Michigan 2005 652,458 160,115 Michigan 2005 466,153 112,540 Michigan 2006 324,965 82,375 Michigan 2007 652146 166,050 Michigan 2007 378,458 92,610 Michigan 2007 528,346 130,250 Michigan 2008 345,892 88,965 Ohio 2008 653,485 167,458 2008 354,268 90,525 Ohio 2009 554,213 133,553 Ohio 2009 461,853 110,258 Ohio 2010 512,463 117,653 Wisconsin 2010 325,496 80,450 Wisconsin 2010 745,865 195,635 Wisconsin 2012 451,236 118,457 Wisconsin 2012 554,168 130,200 Wisconsin 2012 653,856 166,352 lowa 2013 541,285 142,875 lowa 2013 347,856 80,357 lowa 2015 568,457 137,865 lowa 2015 641,257 155,238 Michigan 2016 347,865 66,370 Wisconsin 2018 401,588 66,200 Ohio 2019 504,796 111,055 lowa 2019 310,496 53,370 Michigan 2020 314,698 66,087 % of Sales 24.5% 24.1% 25.3% 25.5% 24.5% 24.7% 25.796 25.6% 25.6% 24.1% 23.9% 23.0% 24.7% 26.2% 26.3% 23.5% 25.4% 26.4% 23.1% 24.3% 24.2% 19.1% 16.5% 22.0% 17.2% 21.0%