Perez POPS wants to Expand!

Katie is really impressed with all your work so far! She has just found out that there is a shop in the shopping center where she plans to open where the owner is looking to sell his bakery. BakeIT!

Katie wants you to look at BakeIT numbers and help her decide if this is the kind of business that would work well with hers and she might consider taking on BakeIT!s products as part of the Perez POPS Family.

After you have completed the analysis, she has asked that you write a formal business letter to her investor, Mr. Johnson, with your recommendations and the details of what you found in your analysis.

Players:

Katie Perez owns Perez PopsMr. Johnson an outside investor. He knows Katie but does not work for Perez Pops. You the student You have your own consultant company and Katie has hired you do do some research on BakeIT!BakeIT! Owner has provided the sales data for his company.

What to do:

Analysis/Pivot Tables

1. Use the attached Excel data file to analyze and create the appropriate pivot tables needed to provide the appropriate information for the following. Place each pivot table on its own worksheet and label each appropriately for each question:

Bake-It is a local company in Kennesaw, GA which has a store front location in the same shopping center where Perez POPS will be opening. The owner is considering making a move out of town and has approached Perez POPS about possibly buying their existing business. Bake-It sells two ways. They sell direct to customers in the Kennesaw Location, and they also ship to several cities and states throughout the US where independent shops sell Bake-Its goods.

Bake-Its owner has provided raw data of the sales from 2020, 2021 and 2022. They sell 2 primary categories of products. First, they sell Soft Baked Cookies. Cookies are large and can easily feed 2 people as a dessert or snack. and second, they sell Trail Bars. Trail Bars are sold in mini bags with multiple bars in the bag. They are chewy, hearty muffin cookie hybrid snacks that are lower in sugar and use local honey to sweeten. All of the Bake-It products are organic which does have higher costs, but they think its worth it!

Katie CEO of Perez POPS needs you to analyze this should they ask the investor to help them take over the Bake-It business?

Create 4 Pivot Tables to answer the following questions:

1 What are the top products by category for each year that are sold at Bake It? (Hint look at this in terms of Units sold and Dollars)

2 What is the top selling product for each of the years?3 What is the most profitable product in each category? (Hint, you will need to create a formula

to calculate profit)

4 Which city orders the most per year dollar wise each year? (Hint, you will need to pick a city outside Kennesaw each year as the answer)

2. Namefileyournamebakeitsales.xlsx.

Business Letter Recommendation to Investor: Read instructions carefully!

Write a formal business letter to Katies investor, Mr. Johnson, with your recommendations and the details of what you found in your analysis.

Refer to Memos and Business Letter links in Effective Messages Module in D2L for the proper business letter format or use Google or check out your word processor for a business letter template. The letter itself should have a complete and correct standard business letter format. Appropriate language and tone should be used throughout the letter.