Preparing Data for Estimating Sales Returns

Instructions and Notes Left by the Previous Accounting Intern

Submit through e Class (submission instructions can be found at the end of this document)

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.

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 what’s 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.

Step 1: Preparing the Data

Here’s the list of instructions and notes left by the intern.

Have fun and I’ll 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:

1. 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

 

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

1. 1. Lookup_value is the cell with the first Order ID (A2) in the Sales Data sheet.
   2. 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.
   3. 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 “No’s” and random numbers, don’t 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

1. 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.
2. 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

1. Add a new sheet after Returns Data and name it Sales Trend 1
2. Click on the Sales Data sheet and click “Insert” “PivotTable”
3. Select all cells of the Sales Data sheet in the top range.
4. Choose Existing Worksheet and then click A1 on your newly created Sales Trend 1 Then choose OK.