You recently joined Grand and Toy as an accounting intern. The accounting manager, Sophie Lamda, met with you and handed over your first assignment.

Sophie: Good morning intern, welcome to Grand and Toy. Annually we review sales return to estimate the percentage we are going to apply to our sales for next year. The intern who used to prepare the preliminary information has just left and I would like to you to take over.

You: This is exciting!

Sophie: I believe the previous intern has left extensive notes on how to use Excel PivotTable to organize the data to be more comprehensible. However, we often find the PivotCharts he picked not the easiest to understand. I hope you can improve on it and choose charts that give us a clear snapshot of whats going on.

You: What exactly would you like me to do?

Sophie: We have sales and return data that can be downloaded into an Excel workbook. They are on separate sheets, therefore you will first need to link them together by Order ID and create a new column that will calculate the amount returned.

After you finish preparing the data, then you can begin to analyze sales trend and return trend. We always start with an overall big picture, then narrow it down to by country, by region, by category. We have never drilled down to sub-category but you may want to explore that if you find anything worth mentioning.

By the end of the month, you will submit the excel workbook with all your work and a preliminary report to discuss your findings. Some recommendation on what percentages we should use to calculate returns will be required, although we would like you to tell us whether one percentage can be applied to all sales, or we need to have specific estimated return percentages at a lower level (country, region, category etc.) and please explain your rationale. These estimates change every year because sometimes the return experience is quite homogeneous, but in some years certain things happen and particular types of product or change in customer base may cause a big difference in return pattern.

Your preliminary analysis will help us determine particular categories and/or regions to look into before finalizing the percentage(s) to be used to estimate returns.

For the one to two-page memo, please include a summary finding of overall sales trend and return trend, any particular region, product category and sub-category that we need to pay more attention to before deciding on the percentage of return to be used. Also, for other region/product category with return pattern that is relatively stable or not material relative to total sales, suggest the estimated percentages of return to be used. Be sure to explain how you come up with those observations.

Heres the list of instructions and notes left by the intern.

Have fun and Ill see you in 2 weeks.

Step 1: Preparing the data

If you are using Excel in Office 365, follow the steps below to link returns data with sales data:

Sales Data sheet: Give R1 a column name, Returned. Insert the Xlookup function in cell R2 with the following inputs:

Lookup_value is the cell with the first Order ID (A2) in the Sales Data sheet.

Lookup_array is the range of cells with Order ID ($B$2:$B$297) in the Returns Data sheet. Make sure you type in the $ signs to fixate the cell in the function. This will enable you to autofill the rest of the column without retyping the function again. You need to click into the Returns Data sheet and select B2:B297, then type in the $ signs.

Return_array is the range of cells with the Return status ($A$2:$A$297) in the Returns Data sheet.

If_not_found is the return value you want the cell to display if the order is not returned. Please type in NO with the quotation marks to indicate it is a text value.

You do not have to specify any values for match_mode or search_mode

Select cell R2 in the Sales Data sheet and point your curser to the bottom right corner of the cell. Double click to autofill the Xlookup function to the end of the record.

OR If you are using other Excel versions where Xlookup is not available, follow the steps below to link returns data with sales data:

Sales Data sheet: Give R1 a column name, Returned. Insert the Iferror function in cell R2.

Inside the Iferror function

Value The match function is the input (i.e type Match( ) where the function asks for value). Please follow the instruction under b. Match function below to fill the inputs within the Match function.

Value_if_error type in NO with the quotation marks after completing the input for the Match function.

Match function

Lookup_value is the cell with the first Order ID (A2) in the Sales Data sheet.

Lookup_array is the range of cells with Order ID ($B$2:$B$297) in the Returns Data sheet. Make sure you type in the $ signs to fixate the cell in the function. This will enable you to autofill the rest of the column without retyping the function again. You need to click into the Returns Data sheet and select B2:B297, then type in the $ signs.

Match_type please type in 0 (without the quotation mark) to perform exact match.

Select cell R2 in the Sales Data sheet and point your curser to the bottom right corner of the cell. Double click to autofill the Iferror function to the end of the record.

You now have a column with Nos and random numbers, dont worry about those numbers, they do not matter.

Continue below whether you use Excel in Office 365 or another version of Excel:

Create a new column to specify sales returned

Sales Data sheet: Give S1 a column name, Amount Returned. Insert the IF function in S2 such that if R2 = NO then the cell will return an amount equal to 0, otherwise, the amount in P2 will be used (i.e. sales). If you do not know how to use the IF function, please use Excel Help to look for further information.

Fill down to the end of the record.

Your total amount returned should equal to 178659.8611. If this is not the amount you get, then you have done something wrong either in linking the two sheets together, or have used an incorrect IF statement.

If you have used the sum function to add up column V, please make sure you remove it before you move onto the next step.

Step 2: Understand Sales Trend

Overall sales analysis

Create a pivot table in a new sheet

Add a new sheet after Returns Data and name it Sales Trend 1

Click on the Sales Data sheet and click Insert PivotTable

Select all cells of the Sales Data sheet in the top range.

Choose Existing Worksheet and then click A1 on your newly created Sales Trend 1 sheet. Then choose OK.

You can now drag the pivot table fields into the 4 different quadrants:

Filter: leave blank

Rows: Ship Year

Values: Sales

You now have an unformatted table with 2 columns: Ship Year, Total (represent sum of sales).

Now you want to create a PivotChart by clicking into the table, then choose PivotTable Analyze PivotChart in the ribbon. Choose a line chart that best suit for showing sales trend. After you have created the chart, please change the chart title to Sales Trend 2018 to 2021.

Analysis by year, country, region

In the same sheet, create a PivotTable for sales by year and country. Your PivotTable quadrants should contain:

Rows: Ship Year

Columns: Country

Values: Sales

Hint: When create multiple PivotTable in the same sheet, you have copy and paste the previous PivotTable and then make changes to the quadrants.

Then following step 5 above to create another PivotChart.

You will then repeat step 1 and 2 to create a PivotTable and PivotChart for sales by ship year, country and region by including region to the Column quadrant.

Analysis by year, product category, and product subcategory

Create a new sheet after Sales Trend 1 and name it Sales Trend 2

This time you want to create two PivotTables and two PivotCharts to analyze

Sales by ship year and product category

Sales by ship year and sub-category

Follow the same procedures as in (B) above by changing the columns entry.

Step 3 Understanding Sales Return

Overall sales return trend, by year, country, region

In a new sheet (Sales Returned), create the following PivotTables and Charts by following the instructions in Step 2 understanding sales, but replace sales with Amount Returned:

Sales return by ship year

Sales return by ship year and country

Sales return by ship year, country, and region

Sales return by ship year and category

Sales return by ship year and sub-category

Step 4: Understand relationship between sales and returns

Analysis by year, country, region

In this step, you want to explore the returns as a percentage of sales

Create a new sheet and name it % returned

Create a new PivotTable with

Filter: Country

Rows: Ship Year

Columns: region

Values: Amount returned, Sales

In the PivotTable Country Filter, choose Canada. Then,

Create a percentage of sales return by ship year: Click any cell in the PivotTable and choose PivotTable Analyze, Fields, Items, & Sets, then Calculated Field.

Rename Name: Field1 as % sales return

Formula: Select from the fields such that you can calculate the amount returned as a percentage of sales

Click Add and Ok, then you will see a new column with the calculated % sales return in the PivotTable.

Create a PivotChart. You want to keep only the % returned in the chart. You can do so by right clicking sum of amount returned and sum of sales and select remove field. You should now only see sum of % returned

The same columns will also be removed from your PivotTable automatically.

Now copy and paste the PivotTable into a new cell below and create the same PivotChart for U.S.

Analysis by year, product category, and product subcategory

Create a new sheet and name it % Returned Category. In this sheet, you want to repeat Step 3 Part A. Instead of understanding % sales returned by year, country, and region, you want to understand it by year, category and by year, sub-category (similar to Step 2 Part C).