What to do: Analysis/Pivot Tables 1. Use the attached Excel data file to analy

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 storefront 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-It’s
goods.
Bake-It’s 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 it’s 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. Name file yournamebakeitsales.xlsx.
Business Letter Recommendation to Investor: Read instructions carefully!
Write a formal business letter to Katie’s investor, Mr. Sikes, with your recommendations and the
details of what you found in your analysis.
Refer to Memos and Business Letter links in the 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.
In addition to the correct structure, your letter should include:
1. Introduction
2. Create your own letterhead. You may use a Word template customized with your
consultant information or adding information/graphics etc. to the header and
footer.
3. You are working for Katie Perez, owner of Perez POPS. You have completed the
analysis on the BakeIT! business and will share the information with Mr. Sikes
(possible investor) and make a recommendation.
Mr. Wesley Sikes, CEO
Investiture Inc.
4550 Main Street NW
Alpharetta, GA 30022
4. Integrate the 4 pivot tables in an informative and useful way to
answer the questions regarding the BakeIT! Sales and store data.
5. Create a Word table that provides the pros and cons for acquiring and investing in the
BakeIT! business for Perez POPS.
6. Include your recommendation based on the data.
7. Conclusion
8. Name your file: yournamebusletter.docx
What to Submit:
1. Submit your Pivot Table Analysis (Excel) document to D2L. A link is not acceptable. (Additionally
Numbers files and Google Sheets are unacceptable) You must submit the Excel file.
2. Submit your Business Letter (Word Doc) document to D2L. A link is not acceptable. (Additionally
Pages files and Google Docs are unacceptable) You must submit the Word file.