Analysis Assignment
Description - An independent home goods retailer, Savvy Styles, began offering seasonal items five years ago and would like to analyze the sales trends to reduce product obsolescence and develop a plan for upcoming summer offerings and locations. For the past five years, Savvy has had one location, but this year they have expanded into two additional locations, both with greater foot traffic, street frontage, and parking options.
In the past, Savvy Styles purchased seasonal items to sell in a 1000 sq ft area of the sales space of the first location. Each of the new locations has 1250 sq ft dedicated to sales space for the seasonal items as the manager of store 1 believes that the seasonal area generates strong sales. However, as the items are seasonal, some question whether the products are generating profit and think the use of the square footage should be reconsidered.
To get more insight into the seasonal space and plan for the spaces in the two new stores, the owner of Savvy Styles has asked you to use available data to calculate the value of the obsolete inventory from the 2017 season and develop a sales forecast for the new stores while considering the available data.
Guidelines & Recommended Steps
Using the data available in the accompanying four MS Excel files, develop a ROBUST MS Excel file that clearly lays out the analysis for each of the following questions and makes all inputs visible. In an accompanying MS Word doc, prepare a report following the outline described at the end of this document. Also document any assumptions you made during your analysis at the end of the MS Word document.
The bullets below are intended to guide you through the steps necessary to answer the question. You may need to move the provided data into MS Access in order to generate the necessary aggregated data.
It is strongly suggested that you read over the entire assignment before beginning and allot plenty of time to complete it.
Question 1: What products did not sell well in 2017 and should not be re-ordered for 2018?
- The 2017 inventory acquisition data and sales data for the products is provided.
- The selling period for these seasonal products is into broken down into two periods (period 1 = April, May, June; period 2 = July, August, September). The sale price listed for the products was the original sale price, which applies to all of period 1. Starting July 1, the price is reduced by 50%.
- Determine the total quantity sold and the revenue generated from each product for 2017. Present these values for period 1, period 2, and the total across both periods.
- Determine the profit generated from each product for 2017 and the cost of goods sold.
- All summer seasonal products are considered obsolete after October 31st of their selling year because Savvy determined they will not sell and does not have the space to store out of season merchandise. Obsolete inventory write offs occur when the inventory's market price has fallen to a level below the cost at which it is currently recorded in the accounting records. Savvy Styles thus needs to determine the amount of inventory to write-off, which should be the difference between the book value (cost) of the obsolete inventory and the amount of cash that the business can obtain by disposing of the obsolete inventory in the most optimal manner. Savvy can sell any unsold items to a discount retailer for $2.00 per item. Determine how much cash can be generated by disposing of the obsolete inventory and the amount of the write-off for 2017. The write-off is equal to the sum of amount money spent on each item in inventory minus the cash generated through disposal.
- Determine the percentage of the write-off that can be attributed to each product. Based on your analysis, what two products should not be re-ordered for the 2018 season? Why should these products not be re-ordered? Answer this question in your report under section I c.
Question 2: What is the rough revenue forecast for 2018 for the three stores?
The 2013-2016 inventory acquisition and quantity sold data by sales period is provided. Augment this data by adding appropriate data for periods 1 and 2 of 2017 using your analysis from Question 1.
The store manager believes that there is a difference in the number of summer days across period 1 and period 2 each year. Conduct a t-test to determine if period 2 has more sunny days than period 1. Answer this question in your report under section II a.
Conduct a regression analysis of the 2013-2017 period-level data. The manager of store 1 believes the sales of the season products are highly impacted by which period of the summer season it is, available inventory, weather patterns (i.e., number of sunny days), and holiday weekend shopping trips (i.e., number of holidays weekend). Prepare a regression model that tests whether each of these significantly impacts total quantity sold during the period. The data provided is based on the following variable definitions:
- Period: 1 for Period 1 (April, May, June, products sell at regular sale price), 2 for Period 2 (July, August, September, products sell at discounted price)
- Total Annual Inventory Acquisition: total number of summer season products acquired for the seasonal section of the store
- Number of Holiday Weekends: Number of times a national holiday (Memorial Day, Fourth of July, Labor Day) falls on a Friday, Saturday, Sunday, or Monday during the period
- Number of sunny days: number of days classified as sunny by National Weather Service during the period
Using your regression analysis results, develop a forecast of the quantity sold by period for store 1 for 2018. It has been predicted that there will be 21 sunny days in period 1 of 2018 and 39 sunny days in period 2 of 2018, and the total inventory acquisition for store 1 for 2018 will be 3500. You should develop the holiday weekend data by consulting the calendar. Be sure to round down as quantities must be whole numbers.
Expand your analysis to forecast quantity sold for the two new stores. Since the new stores have 1250 sq ft compared to store 1's 1000 sq ft (in addition to other advantageous features), it can be assumed that quantity sold at store 2 will be 12% higher than the forecasted figures at store 1 for 2018, and quantity sold at store 3 will be 15% higher than the forecasted figures at store 1 for 2018. Calculate the predicted quantities for each store for each period and across both periods. Be sure to round down as quantities must be whole numbers.
Calculate the average sale price of the items in inventory in each period. Using these average price, multiple the average price by the forecasted total quantity sold per period for each store to determine a rough revenue forecast for each store and overall revenue.
Reference Information and Formatting Guidelines -
- The costs of the products for 2018 is expected to be the same as the 2017 costs.
- Savvy Styles plans to sell the products in 2018 at the same price as 2017.
- Report all figures with zero digits after the decimal. When figures are percentages, show a percent sign (%). Do not show dollar signs ($) in tables, but utilize them when writing the prose of the report.
Deliverable -
Submit your MS Access file, MS Excel file, and your report in a MS Word file via Blackboard. Your report should follow the outline below. All tables should have a title.
I. Analysis of 2017 Product Sales
a. Present a table showing the total quantity sold and total revenue for period 1 and period 2 of the summer season and provide the totals for both figures for the entire summer season.
b. Present a table showing, by product, the total quantity sold, price, cost of goods sold, profit generated, actual write-off amount associated with the product, and the percentage of the total write-off associated with the product
c. Recommend two products for removal from the product offerings in the summer season area. Discuss how the tables in a. and b. provide evidence for your recommendation.
II. Forecast of 2018 Revenue
a. Discuss your analysis of the number of sunny days across period 1 and period 2. Indicate whether there is statistical support for a difference in the mean number of sunny names in period 1 vs. period 2.
b. Present a table showing the summer seasonal product inventory acquisition each year from 2013 to 2018, whether actual or planned.
c. Present a table showing the predicted 2018 total sales quantities and revenue by store for period 1 and period 2 and the total across both periods.
III. Assumptions: Document all the assumptions you have made during your analysis (e.g. Disposal cost per item was taken as $2)
Helpful Tips
For the following set of tips, refer to the sample scenario data below.
|
Item
|
Total Qty Acquired
|
Sale Price
|
Qty Sold
|
Cost
|
Unsold Qty
|
Disposal
|
|
Snickers
|
11
|
1.00
|
10
|
0.30
|
1
|
0.10
|
|
Twix
|
25
|
0.75
|
22
|
0.25
|
3
|
0.10
|
|
KitKat
|
8
|
1.05
|
6
|
0.35
|
2
|
0.10
|
Revenue is calculated as the sum of the sale price of each item multiplied by the quantity sold of each item. Based on the sample data, the revenue would be: Revenue = (1.00*10) + (0.75*22) + (1.05*6) = $32.80
Profit is calculated as revenue minus costs. For the purpose of this assignment, the only known costs are the cost of acquiring each item. Thus, the profit can be calculated as the sum of the quantity sold multiplied by the difference between the sale price and the cost of the item.
Based on the sample data, the profit would be: Profit = (10*(1.00-0.30)) + (22*(0.75-0.25)) + (6*(1.05-0.35)) = $22.20
The total write-off, or loss, due to unsold inventory is calculated as the sum of the total unsold quantity multiplied by the difference between the cost of the items and the cash generated through disposing each item.
Total Write-off = ((0.30-0.10)*1) + ((0.25-0.10)*3) + ((0.35-0.10)*2) = $1.15
Referring to the above example, the percentage each item contributed to the total write-off would be as follows: Snickers: 0.20/1.15 = 17.4%
Twix: 0.45/1.15 = 39.1%
KitKat: 0.50/1.15 = 43.5%
Knowing that in this scenario the profit is $22.20, the write-off of $1.15 could be subtracted from the profit for a clearer picture of the overall success.
Attachment:- Assignment Files.rar