Overview
You work for a company that currently exclusively operates online. You’ve been tasked with creating a report about your company’s performance over a calendar year. You will be sending this to your boss and presenting it for a couple of company executives in the coming weeks. It’s also almost time for your annual review, so you really want to perform well.
Your company is CineMagic Home Theaters and they specialize in home theater systems, audio-visual equipment, and accessories for a cinematic home experience. Their data can be found here: For the report, your boss specifically wants to know:
- What was the company’s gross profit this calendar year?
- How was the company’s month-by-month performance?
- What goods are best-sellers and worst sellers in terms of quantity? Your VP of marketing is looking at an advertising initiative and would like to know what to focus on.
- What items have brought in the most profit for the company?
- Where would be good locations for brick-and-mortar locations based on proximity to orders?
- Are there any major repeat customers or especially big spenders (“whales”)?
Your boss trusts your analysis of the company’s performance and hasn’t given you a lot of instruction on how to answer these questions, but you do know there are a couple of specific things that she’ll want to see:
- Your final Excel workbook with how you answered the above questions, in addition to your memo and presentation (see below)
- A pivot table containing sales and profit information for the company’s products.
- She’ll immediately want to see the 3 products with the least profit, so you’ll highlight that whole row in red
- Since she also wants to know where the highest profit margins are, you’ll highlight the top 3 in green.
- At least 4 data visualizations that paint a picture of what’s happened over the past calendar year. You want to remind her that you can make them in both Tableau in addition to Excel, so you’ll create something in one of those platforms as well. And, since you know everyone loves a map, you’ll make at least one visualization a map.
You are able to pull down information on sales over the last calendar year, however you were only able to pull down 3 separate tables from your company’s database: Orders, Products, and Customers. You’ll need all 3 to answer the questions your boss and company executives have.
You’ll be giving your report as a PowerPoint Presentation, but you need to send your boss a one-page business memo as well, so she isn’t surprised by your presentation. She sent you a reminder of what that memo should look like:
- An opening paragraph that summarizes your report
- 3 body paragraphs that discuss the findings in detail
- A closing paragraph that provides your overall recommendation to the company based on her questions. Your boss wants to know what you’d do in her shoes.
- Your visualizations will be included at the end with captions, and you’ll refer to them throughout the memo.
- The writing is single spaced with an appropriate header
She also briefed you on what is expected from your presentation: Approximately 8 slides
- Slide 1: Introduction
- Slides 2-6: Analysis of Specific Questions
- Visualizations/tables/charts should be included where appropriate
- Slide 7: Other findings
- Slide 8: Recommendations
The final submission should include: your Excel file, the 1 page memo, and a PPT file.