Fundamentals of Data Analysis in Excel - Case Study

Assignment Task

Stage

1. Standardize The Dataset

Fix Imprint names, so that they are consistent across all tabs. The Imprint name should not contain the word "Kaplan" and all letters must be UPPERCASE.

Fix Test names, so that they are consistent across all tabs. The Test name should not contain the word Kaplan" and all letters must be UPPERCASE.
Ensure all ISBN fields are formatted consistently across the different tabs - an ISBN should always contain 13 characters (sometimes the first 2 characters are 0's) and all ISBN's should be present on all tabs for correct lookups. The field should be formatted as text with no leading or trailing characters Check for unique ISBN's - an ISBN and its pertaining data should only appear once on each tab. There could be cases, where the database pull might have resulted in duplicate entries, so make sure to de-
dupe those entries.

Make sure units are formatted as a number with no numbers displayed after the decimal point. Ensure panes are frozen so that headers are always visible when scrolling through the data and that the data is formatted in a clean and presentable way. Once you have a clean dataset, with no duplicates and consistent formatting, you can move to the next tasks below.

2: Aggregate The Data

Populate the unique instances of "Imprint Test ISBN and Title in these columns. You may have to populate the data multiple times to ensure all conditions below are met.
Populate the correct month (JAN, FEB, MAR, etc.) for each entry. You have to ensure each unique ISBN is listed on the sheet multiple times for each month, i.e. one ISBN must have 12 entries (one for JAN, Make sure to populate the data on this tab with the correct year (2017 or 2018) for each entry. You have to ensure each unique ISBN and each month is listed on the sheet twice - once for 2017 and once After you are done with the above 2 tasks, you should have 24 entries for each unique ISBN - 12 for
each month x 2 for each year. Continue to the next task only after you are done with this data manipulation (hint: you should end up with 7056 rows with ISBN data on this sheet ) Create a formula to populate the Scenario - the formula should populate the value "ACTUAL" if the year is 2017 and "BUDGET" if the year is 2018
Create a formula to populate the Price for each unique Imprint from the "DataPrice--2017" tab.

Create a formula to populate the Gross Units Sold for each unique ISBN. Be careful to ensure you bring in the correct Units Sold for each month and each year. 2017 Units Sold data must be populated from the "DataUnits--2017" tab. 2018 Units Sold data must be arrived at by applying the growth rate from the "Units--Growth2018" tab to each ISBN for each month - i.e. 2018 Units Sold = 2017 Units Sold * (1 + Create a formula to calculate Gross Sales based on Units Sold and Price for each ISBN. Create a formula to populate the average Return Rate for each unique Imprint from the "DataReturns" tab. Make sure to account for the different return rates in 2017 and 2018. Create a formula to calculate Returns (Units) based on Units Sold and average Return Rate. Create a formula to calculate Net Units Sold based on Gross Units Sold and Returns (Units). Create a formula to calculate Returns (Sales) based on Gross Sales and average Return Rate. Create a formula to calculate Net Sales based on Gross Sales and Returns (Sales). All fields marked in yellow on the AggregatedData" tab should be formulaic and must show your work. After you complete this section, you should have the data structured in a way to be able to populate.

3: Pivot Table

• Create a PIVOT table in a new tab, "PIVOT", starting at cell B3, as follows:
• Table should be in a fixed format (format should not change after table is updated)
• Filter fields should include "Scenario"

Row fields should include "Imprint," "Test", and "Title" in tabular format with Subtotals for Imprint." The Title field should be fully collapsed, but should give the user the ability to expand it, if Column field should include the "Year."

Value field should show SUM of "Net Sales," shown in Accounting format. Sales should be shown for both years - 2017 and 2018, and a % Growth field should be inserted to show the difference between.

All columns/headers must be labeled correctly in the pivot table and panes should be frozen, so headers are visible when scrolling through the data.

4: Sumifs Summary Tables

Use SUMIFS (or other formulas/functions) to create summary tables for Units Sold on a new tab, as
follows:

• Rows should show the "Imprint"
• Columns should show the "Month" (include Grand Total for the Year as well)
• Values should sum the total # of Net Units Sold for a particular Imprint

Create 3 tables - one on top, showing 2018 Net Units Sold; the second one below, showing 2017 Net Units Sold; the third one at the bottom, showing the Growth % from 2017 to 2018. Re-create the logic above on a new tab, but for "Net Sales."
Re-create the logic above on a new tab, but for "Returns (Sales)." Format your resulting tables in a clean, easy to read way and make sure to put headings/labels on each tab to clarify what the data represents.

5: Charts

Use your presentation and Excel skills to create LINE charts that show a 2 year trend of "Net Sales" on a new tab (quarter by quarter, Q1-2017 to Q4-2018) for the following Tests:

• GMAT
• MCAT
• LSAT
• CHARTS
• PIVOT TABLE
• SAT
• NCLEX-RN
• ACT

Based on the tables/charts you have put together and the information you have at your disposal,
create a new tab where you can answer the following questions:

1. What test is experiencing the most unit growth from 2017 to 2018? Which title is driving most of the unit growth?

2. In what imprint do we see the least growth? Do you have suggestions on how to drive more net sales in the imprint?

3. Some tests are sold between two different imprints. If there is an approximate cannibalism rate of 15%, how many more sales will we see for those test(s) if we're able to reduce the cannibalism rate to 10%? Please present some initiatives on how we can reduce the rate of cannibalism between.

This Statistics has been solved by our PHD Experts at My Uni Paper.