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 a dummy variable for clearance months. 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.

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; column E is the total daily revenue from the sales of product B; Column F is the number of units sold from product C; column G is the total daily revenue from the sales of product C. the Warranties & Returns sheets 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; Column F is the quantity of units accounted for as returns during the month from product C; column G is the quantity of units accounted for as warranty exercises from product C. Notice that due to the lagged nature of the accounting for returns and warranty, the returns data for the last month and the warranty data of the last two months in the dataset are blank. Assume the price of the returned items / warranty exercises is the average price during the month pertaining to these sales. Analysis:

1. A brief description of the task you were given, the data used and your method of analysis. 2. A graphic / table representation of the time series analysis of the data you were given. 3. AN ANALYSIS OF THE GRAPHS / TABLES YOU PRESENT. The analysis should pertain to the data presented in the tables / graphs and should answer questions pertaining to seasonality of the different products, returns over time, warranties exercised and any interesting patterns you observe in the data. 4. Your recommendations for a) warranty expense calculation and the reasoning behind them, and b) return allowance, if needed and your reasoning behind it. ASSUME WARRANTY EXPENSE HAS BEEN ACCOUNTED FOR THE YEAR, EXCEPT FOR THE LAST TWO MONTHS. What do you recommend as warranty expense for this period? 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).