You are working as a data scientist consultant for Bibitor, LLC.
Bibitor, LLC is a retail wine and spirits
company, with about 80
locations (depending on the year-end) within the fictitious state
of Lincoln.
Katie Chen, Bibitor’s CEO wants you to analyze the retail
location of Hardersfield, Lincoln. Ms. Chen wants you to perform
data analysis with the goal of making recommendations in regard to
price, products and daily financial analysis.
The first worksheet contains all sales transactions for the
Hardersfield #1 store from July 1, 2015 to June 30, 2016 (the
company’s fiscal year) while the second worksheet contains the
purchase (i.e. invoice) prices for each product. NOTE: Purchase or
invoice prices are the prices the store pays to buy the inventory
or products.
Data Preparation: In the “BUS102-SalesData” worksheet, you need
to find the invoice (or purchase) price for each product (in column
M) ([vlookup] makes this easy) and then copy down; in cell N2
insert a formula for profit (formula given in the lecture), then
copy down. In the “BUS102-PurchaseInvoicePrice” worksheet,
calculate the number of units sold by product ([sumifs] makes this
easy) in column G and copy and paste down to the end of the data.
In cell H2, calculate the Cost of Goods Sold. The formula is
=F2*G2; and copy down to the end of the data. NOTE: A Product No.
can have different sizes (i.e. Volume) so this needs to be taken
into account. Also the column labeled Volume is the size of the
product in ml; NOT the amount in inventory
- You Are Working As A Data Scientist Consultant For Bibitor Llc Bibitor Llc Is A Retail Wine And Spirits Company With 1 (212.51 KiB) Viewed 37 times
D E M M N SalesQuantity Purchase Price Profit 1 1 3 2 1 2 1 2 1 1 1 1 2 A B C 1 Store Product_No Description 21 1 261 Svedka Citron Vodka 3 1 261 Svedka Citron Vodka 4 1 261 Svedka Citron Vodka 5 1 261 Svedka Citron Vodka 6 1 261 Svedka Citron Vodka 7 7 1 261 Svedka Citron Vodka 8 1 261 Svedka Citron Vodka 9 1 261 Svedka Citron Vodka 10 1 261 Svedka Citron Vodka 11 1 1 536 Svedka Cherry Vodka 12 1 536 Svedka Cherry Vodka 13 1 536 Svedka Cherry Vodka 14 1 536 Svedka Cherry Vodka 15 1 536 Svedka Cherry Vodka 16 1 536 Svedka Cherry Vodka 17 1 693 Svedka Vanilla Vodka 18 1 693 Svedka Vanilla Vodka 19 1 693 Svedka Vanilla Vodka 20 1 693 Svedka Vanilla Vodka 21 1 693 Svedka Vanilla Vodka 22 1 693 Svedka Vanilla Vodka 23 1 693 Svedka Vanilla Vodka 24 1 779 Svedka Raspberry Vodka 25 1 779 Svedka Raspberry Vodka 26 1 1 779 Svedka Raspberry Vodka 27 1 779 Svedka Raspberry Vodka 28 1 779 Svedka Raspberry Vodka 29 1 779 Svedka Raspberry Vodka 30 1 779 Svedka Raspberry Vodka 31 1 779 Svedka Raspberry Vodka 32 1 779 Svedka Raspberry Vodka 33 1 779 Svedka Raspberry Vodka 34 1 779 Svedka Raspberry Vodka 35 1 779 Svedka Raspberry Vodka 36 1 779 Svedka Raspberry Vodka 37 1 780 Svedka Clementine Vodka 38 1 780 Svedka Clementine Vodka 39 1. 780 Svedka Clementine Vodka Size Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter Liter 2 1 1 1 2 1 1 2 1 1 2 2 1 SalesDollars 12.99 12.99 38.97 25.98 12.99 25.98 12.99 25.98 12.99 12.99 12.99 25.98 25.98 12.99 12.99 25.98 12.99 25.98 12.99 12.99 25.98 25.98 12.99 25.98 25.98 12.99 12.99 12.99 12.99 12.99 12.99 12.99 12.99 25.98 12.99 12.99 12.99 12.99 G H SalesPrice SalesDate 12.99 7/10/15 12.99 7/11/15 12.99 7/17/15 12.99 8/19/15 12.99 8/28/15 12.99 10/1/15 12.99 1/22/16 12.99 4/21/16 12.99 6/6/16 12.99 7/17/15 12.99 8/2/15 12.99 8/3/15 12.99 8/19/15 12.99 8/28/15 12.99 4/21/16 12.99 7/17/15 12.99 8/28/15 12.99 10/1/15 12.99 10/16/15 12.99 10/17/15 12.99 11/25/15 12.99 1/22/16 12.99 7/17/15 12.99 8/19/15 12.99 8/28/15 12.99 9/2/15 12.99 9/11/15 12.99 11/7/15 12.99 11/25/15 12.99 12/19/15 12.99 12/29/15 12.99 1/22/16 12.99 4/18/16 12.99 4/21/16 12.99 5/20/16 12.99 7/17/15 12.99 8/19/15 12.99 9/2/15 J K Volume Excise Tax City 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 3.15 HARDERSFIELD 1000 2.1 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 2.1 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 2.1 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 2.1 HARDERSFIELD 1000 2.1 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 2.1 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 2.1 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 2.1 HARDERSFIELD 1000 2.1 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 2.1 HARDERSFIELD 1000 2.1 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 2.1 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 1.05 HARDERSFIELD 1000 105 HARDERSFIELD Location HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #11 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 HARDERSFIELD #1 2 2 1 1 1 1 1 1 1 Liter Liter Liter Liter Liter Liter Liter Liter Liter liter 1 1 2 1 1 1 1