York University$-$ School of Administrative Studies

ADMS $3511-$ Winter $2024-$ Assignment $1$

Little Vehicles is a manufacturer of vehicle toys. The company has decided to study its sales in

the past few years. The data set includes all the orders placed in the last three years. The

following table includes description of each of the variables in the data set.

Variable Description

ORDERNUMBER Order number

ORDERLINENUMBER

Order line number $($each order can have more

than one line, each of which is normally for a

different product$)$

SALES The dollar value of the order line

QUANTITYORDERED Quantity of the product ordered

PRODUCTCODE Product code

PRICEEACH Price for each piece of product

STATUS Status of the order

YEAR Year of the order

PRODUCTLINE Product line $($e$.$g$.,$ trains, planes$)$

CUSTOMERNAME Customer$$s name

PHONE Customer$$s telephone number

ADDRESSLINE$1,$ ADDRESSLINE$2,$ CITY,

STATE, POSTALCODE, COUNTRY Customer$$s address

TERRITORY

Customer$$s geographic territory

NA: North America

EMEA: Europe, the Middle East, and Africa

APAC: Asia$-$Pacific

Japan: Japan

CONTACTLASTNAME,

CONTACTFIRSTNAME Name of the customer$$s contact person

DEALSIZE Size of the order $($i$.$e$.,$ small, medium, large$)$

Please create each of the charts$/$tables in a separate worksheet.

$2$

$1.$ Create a list that shows all orders, the number $($i$.$e$.,$ count$)$ of line items for each order, and

the total sales for each order.

Hint $1$: Make a table in which every row is one order and in front of the order number, you

have the number of order lines for that order and the total sales for that order.

Hint$2$: Every order must only appear in one row.

$2.$ Create a column chart that shows the total sales in different territories in different years.

$3.$ Create a Pivot Table that shows different product lines versus sum of sales, average price for

each piece of product, and the count of order lines.

$4.$ Create a bubble chart with the following characteristics:

x$-$axis: sum of sales for orders that have been cancelled $($i$.$e$.,$ status: Cancelled$)$

y$-$axis: sum of sales for orders that are in$-$process $($i$.$e$.,$ status: In Process$)$

size of bubbles: sum of sales for orders that have been shipped $($i$.$e$.,$ status: Shipped$)$

Different product lines must be represented by different bubbles.

$5.$ Write a managerial report to discuss your findings from the above analyses. $($Max length: one

page; Font: Times New Roman; Size: $12$; Margin: $1-$inch each side; Double$-$spaced$)$