Thank you for your interest in joining the Stio finance team. Below is a list of questions to assess your excel, data mining, and analytical skills. This is a challenging assessment that we provide to all employee levels in the department. If some questions are not within your experience or skillset, don$$t be discouraged, please just document your challenges. Please complete within $72$ hours or communicate your expected completion date if there is a conflict.

Please submit your answers in any medium you find best to clearly explain how you achieved your answer $($retain formulas, pivot tables, techniques, and or take notes if necessary so your approach can be reviewed in a meeting$).$

$*$Feel free to ask for clarification to any questions within the $72-$hour window.

Background Info for $$Sales Data $(3)$ sheet:

$$ The data provided includes line item sales data $($not actual data$)$ from January of $2017$ and $2018$

$$ Fall Season Runs from August $$ February

$$ Spring Season Runs from January $$ August

$$ Existing styles retain the same name every season

$$ Root #s are assigned to styles

$$ SKU$$s are a combination of root #$,$ season of purchase, color code, and size

$$ SKU Origination season refers to all the SKUs created for that particular season, vs Style origination which is the first season a style was designed$/$created$.$ So$,$ a style may originate in Fall $2014$ but then a new SKU is created for that style each new season it is purchased.

$$

Question $/$ Task $1$

$1.$ Tab $1$ is a packing slip from our factory, listing out the inventory included in their last shipment to our warehouse. Tab $2$ is the receiving report created by our warehouse upon delivery of that factory shipment. Please reconcile the receiving report to the packing slip. Present the reconciliation the best way you see fit and provide a list of discrepancies.

Question $/$ Task $2$

$2.$ How many orders $($of any type...sales, damages, exchanges, etc$)$ occurred in $2018$ vs$.2017$ within the sales data on tab $3?$

Question $/$ Task $3$

$3.$ A few transactions on tab $3$ have been tagged with the wrong gender in the $$Gender$$ field. What is the total sales value of the products with the tagging error? $($Tab $4$ is the master listing of our SKUs$)$

Question $/$ Task $4$

$4.$ Did inline styles new to $2018($Fall $2017$ and Spring $2018$ styles sold in $2018)$ drive more revenue per style than new inline styles in $2017($Fall $2016$ and Spring $2017$ styles sold in $2017).$ How much more or less $ and $\%?($Keep in mind that a single style has many SKUs under it to represent individual inventory items based on the style, color, and size$)$

A$.$ Suggest a strategy that can be taken next year to improve our revenue per style.

$$

Question $/$ Task $5$

$5.$ We are attempting to improve efficiency in the line for the following fall season. We would like to identify a list of styles in the Fall $2017$ season to potentially drop from the line based on the following criteria. If a Gender Style meets the $3$ criteria below we would like to remove the style from the Fall $2018$ assortment. Please provide a list of styles meeting the criteria.

A$.$ Less than $30$ net units sold $($Fall $2017$ styles sold in $2018)$

B$.$ Lowest net revenue generating styles, defined as styles making up $<=30\%$ bottom of revenue $($Fall $2017$ sold in $2018)$

C$.<=10\%$ of styles with the highest average discount $\%$

$$

Question $/$ Task $6$

$6.$ Mens Hometown Down Hooded Jacket $($hint$,$ only look at hooded jackets$)$ and Vest grew YoY for both styles. We invested $$15$K into the Jacket $($not the vest$)$ color design for the Fall $2017$ line. The $$SKU Master$$ on tab $4$ includes all the costs to be capitalized as part of inventory. For the questions below we are looking to compare Fall $2016$ to Fall $2017$ performance.

A$.$ Assuming the change in Vest units sold acted as the control for growth, did we capture a positive ROI on this investment? Explain, including the calculated ROI.

B$.$ Based on actual Hometown Down Hooded Jacket sales growth, what would color investment need to be in order to break even $($keeping vest as control growth$).$ Explain.

C$.$ Based on the investment of $$15$k$,$ what would $2018$ Men$$s Hometown Down Hooded Jacket sales total $($in dollars$)$ need to be in order to break even $($again assuming the vest is control growth$).$ Explain.

D$.$ Can you explain your thoughts on the color investment decision and potential impact on the business?

$$

Question $/$ Task $7$

$7.$ From the $$Sales Data $(3)$ tab, please create the January $2018$ sales report. The report is formatted as two tables. The first table compares total sales by sales channel year over year for the month of January. The second table looks at January$$s average discount percentage by SKU origination season compared to last January $($hint in $2018$ we would have the Spring $2018$ season, while the comparative season in $2017$ would be Spring $2017).$