General:

You work for a sports gear company. You have been tasked with providing the CFO of your firm an analysis of product returns and warranty related expenses associated with the sales of your

firm’s products. Management needs to understand the trends in sales of the different products as well as provide an estimate for warranty expense for the ending period of the year and decide

whether a return allowance is needed.

The company’s business model is as follows: all products, EXCEPT for products sold during clearance sales, can be returned within 30 days of purchase. All returns are booked in the

following month. For example, if a customer purchased a ski jacket on February 15th, and

returned it at anytime within 30 days, the return will be accounted for in March. The company provides a 60-day warranty on its products. Any warranty that is exercised is booked in month  t+2 after the sale. For example, if a customer purchased a tennis racket on February 15th, and     exercised the warranty at anytime within 60 days, the warranty will be accounted for in April.

You requested that the firm’s IT personnel provide you with historical information dating back ten years. The data contains information on the two products that comprise the majority of the  firm’s sales (disregard the rest of the products). The “Sales” sheet contains daily data of the

quantity sold and the total dollar amount received. The “Returns and Warranty” sheet contains monthly data on the number of products returned and the number of exercised warranties each month.

Instructions to download the file:

1.   Download the file “SalesDataSimulation.xls” from Blackboard.

2.   Open the file using Excel. The file is structured to produce a simulated database. Each

student will work on a different dataset. To generate the database click on the “Generate Data” button. You might need to “enable macros” on your program in order for the code to work.

3.   Save the file under the name “ Sales  LastName  FirstName_ID.xls.” You will need to submit this file on Blackboard under this name, with all your data work, along with a   short report.

4.  PLEASE DO NOT MANIPULATE ANY OF THE DATA COLUMNS IN SHEETS

(COLUMNS A-E IN “Sales” & COLUMNS A-E IN “Warranties & returns”), OR WE WILL NOT BE ABLE TO GRADE YOUR WORK. THAT INCLUDES ADDING

AVERAGES / SUMS TO THE BOTTOM OF THE COLUMNS! FURTHERMORE, PLEASE DO NOT CHANGE THE NAMES OF THE EXISTING SHEETS. YOU

ARE WELCOME TO ADD AS MANY SHEETS AS YOU NEED TO THE FILE.

Explanations and assumptions:

Once the data is generated you will see 3 worksheets, “Sales”, “Returns and Warranty”, and

“Solution”. The “Sales” sheet contains DAILY information on the quantity of each product sold   and the total revenue from each product. Column A includes the date; column B is the number of units sold from product A; column C is the total daily revenue from the sales of product A;

Column D is the number of units sold from product B; and column E is the total daily revenue

from the sales of product B. the “Warranties & Returns” sheet contains MONTHLY data on

returns and warranty exercises. Column A includes the month; column B is the quantity of units accounted for as returns during the month from product A; column C is the quantity of units

accounted for as warranty exercises from product A; column D is the quantity of units accounted for as returns during the month from product B; column E is the quantity of units accounted for   as warranty  exercises from product B. Notice that due to the lagged nature of the accounting for returns and warranty, the returns data for the first month and the warranty data of the first two

months in the dataset are blank. Assume that the price of the returned items / warranty exercises is the average price during the month pertaining to these sales.

Analysis:

a.   Create a monthly aggregation of sales data.

b.   Calculate the average price each month. Those months that you see a reduction in price  are CLEARNACE months. Create an indicator variable equal to 1 for clearance months and 0 otherwise.

c.   Calculate the percentage returns of the different products by month. Make sure you account for the lags in the data!

d.   Calculate the percentage warranty exercises of the different products by month. Make sure you account for the lags in the data!

e.   Analyze the monthly sales patterns of the different products.

Output excel file and Report

You are required to fill in the “Solution” sheet on the excel spreadsheet and prepare and submit a short report of your findings.

Excel Solution sheet

The excel solution sheet contains highlighted cells that will need to be filled. You are

welcome to use any formulas you need and as many sheets you need to calculate the numbers requested. However, only the numbers in the highlighted cells will be checked and graded.

Please remember to enter your name and ID in the relevant cells in the solution sheet.

Report

The report should not be more than two pages long (including graphs and tables) and should be saved under the name “ Sales  LastName  FirstName_ID.pdf” . The report should provide the following:

1.   A brief description of the task you were given, the data used and your method of analysis.

2.   A graphic representation of the time series analysis of the data you were given. Think  about the sales numbers you arepresenting (units or dollar value – each has a different meaning) as well ashow to present the warranty / returns (absolute number of units

returned or percentage of sales).

3.   AN ANALYSIS OF THE GRAPHS YOU PRESENT. The analysis should pertain to the   data presented in the graphs and should describe the sales seasonality of the two products, returns overtime, warranties exercised and any interesting patterns you observe in the

data.

4.   Your recommendations for end-of-year 2020 calculations for a) warranty expense and the reasoning behind them, and b) return allowance, if needed and your reasoning behind it.    ASSUME WARRANTY EXPENSE HAS BEEN ACCOUNTED FOR THE FIRST 10

MONTHS, what do you recommend as warranty expense for the last two months in

2020? Should the company allocate an allowance for returns to offset the sales at the end of the year?  Explain!

5.   Anything else you would like to share with your CFO regarding the data you acquired

(can you think of different data you might be able sot use to better get a sense of the sales trends).

Additional direction and tips

1.   This is an independent data assignment, the purpose of which is for you to explore both

your technical skills and your analytical skills. It covers fundamental accounting practices in sales and revenue recognition. If you are unclear about these journal entries, please

search them up online or in any of your accounting textbooks.

2.   If you are unclear about any functions in excel which are needed for the analysis, please look them up online. You will find everything you need by googling a question such as  “what are the different IF functions in excel?” if you have a question, it has most likely  been asked and answered online in the past!

3.   Other than the cells you are required to fill out in the excel file, you are welcome to use any graphic representation / tables you deem will visually help your manager

understand your analysis. There isn’t one correct format for this report – you have to

think how to present your results and analysis in a concise and informative manner.

“More” is not always better – so make sure any graph / table added actually adds content to your narrative.

4.   Please check your grammar and spelling – clarity is very important when presenting results. Use short sentences.