NoTable is a furniture store in Halfway, Oregon, that manufactures custom-designed wooden tables of exceptional quality. Most of their customers are
collectors, including several movie stars. Their business model is simple: a customer contacts them, they work with the customer on a design, build the
table, and deliver it to the customer's residence. NoTable just hired you as an intern for their accounting department. They hope your data analytics skills
can help them with some of the decisions they face. They provide you with an Excel file that contains several worksheets with data generated from thei
ERP system. The underlying data model is shown here, followed by a data dictionary.
Data Model
ET ITS PurchaseOrders
1 Purchaseorderid
SupplierName Status
QuotelLeadTime
ActualLeadTime
Supplie
LaborEstimate
La LaborActual
DesignerName RawMaterialEstimate
RawMaterialActual
Designe
SalesOrders Custome
7 salesorderiD
Paid
Days
DaysOutstanding
Custome
Data Dictionary
PurchaseOrders
PurchaseOrderld Uniquely identifies a purchase order.
Status A purchase order’s status: Canceled, Delivered, Open, or Returned.
QuotedLeadTime The number of days the vendor committed to deliver the goods.
ActualLeadTime The number of days it took the vendor to deliver the goods.
Supplier ID of the vendor from whom the goods are purchased.
DeliveryStatus An order is canceled, delivered, open, or returned. For orders that
have been delivered, it is specified whether they were early, ontime,
late, or disruptive. Disruptive implies that the production process was
disrupted.
LaborEstimate The estimated dollar amount for labor for the production order.
LaborActual The actual dollar amount used for labor for the production order.
Name Description
RawMaterial Estimate The estimated dollar amount for raw materials for the production
order.
RawMaterial Actual The actual dollar amount for raw materials for the production order.
Designer ID of the designer who designed the production order (table).
Supplie
Name IIa]
ID Uniquely identifies a vendor.
Name A vendor’s name.
Name Description
SalesOrderId Uniquely identifies a sales order.
Amount The amount the customer owes NoTable.
Paid Has the order been paid in full: Yes or No.
Days For orders that are fully paid, the number of days it took to pay them.
DaysOutstanding For orders that are not fully paid yet, the number of days the
eceivable has been outstanding.
Customer ID of the customer to whom the table was sold.
Custome
Name IESE a0]
1D Uniquely identifies a customer.
Name A customer’s name.
State The state in which a customer lives.
SalesTaxRate The sales tax rate for the state in which the customer lives.
Designe
Name Description
ID Uniquely identifies a designer.
Name A designer's name.
Click here to view the Excel.
NoTable delivers items to customer residencies. You notice that no sales tax was collected for any of the sales.
1. Determine the total sales tax amount that NoTable should have collected.
2. Determine how much sales tax should have been collected and remitted to the different states.
Please download the Excel input file, complete your Excel file, create your Word file, and submit (upload) your completed Excel and Word files.
PurchaseOrders
PurchaseOrderId Status QuotedLeadTime ActualLeadTime Supplier LaborEstimate RawMaterialEstimate LaborActual RawMaterialActual Designer DeliveryStatus
1 Delivered 30 28 1 28560.3 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 1 On Time
2 Delivered 30 45 3 1901.55 2263.75 2807.05 2535.4 5 Disruptive
3 Canceled 45 48 2 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 4 Cancelled
4 Canceled 60 5 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 4 Cancelled
5 Delivered 90 6 XXXXXXXXXX 27096.9 XXXXXXXXXX XXXXXXXXXX 5 Early
6 Delivered 120 133 3 5356.12 6312.57 4399.67 6503.86 5 Late
7 Returned 60 63 2 XXXXXXXXXX 14250.6 XXXXXXXXXX XXXXXXXXXX 3 Returned
8 Delivered 30 42 1 25033.8 17447.8 16689.2 21999.4 3 Disruptive
9 Returned 30 5 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 15497.4 5 Returned
10 Delivered 60 75 3 XXXXXXXXXX XXXXXXXXXX 29440.8 XXXXXXXXXX 5 Disruptive
11 Returned 30 2 8283 8283 7730.8 9387.4 4 Returned
12 Delivered 90 100 3 XXXXXXXXXX XXXXXXXXXX 10974.2 XXXXXXXXXX 3 Late
13 Canceled 45 46 1 XXXXXXXXXX XXXXXXXXXX 8657.04 XXXXXXXXXX 5 Cancelled
14 Delivered 15 15 4 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 2 On Time
15 Delivered 30 30 4 XXXXXXXXXX XXXXXXXXXX 22107.9 XXXXXXXXXX 2 On Time
16 Canceled 5 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 4 Cancelled
17 Delivered 45 40 2 7426.24 9363.52 9040.64 7426.24 5 Early
18 Delivered 45 75 6 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 5 Disruptive
19 Delivered 30 29 1 XXXXXXXXXX 12501.6 XXXXXXXXXX 8334.4 3 On Time
20 Returned 5 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 5 Returned
21 Delivered 30 32 2 XXXXXXXXXX 16411 XXXXXXXXXX XXXXXXXXXX 2 On Time
22 Delivered 30 29 4 1188.2 1960.53 1366.43 2198.17 4 On Time
23 Delivered 45 40 6 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 2 Early
24 Delivered 45 123 5 XXXXXXXXXX 19993.8 XXXXXXXXXX XXXXXXXXXX 5 Disruptive
25 Delivered 45 45 1 8770.8 8186.08 8770.8 XXXXXXXXXX 2 On Time
26 Canceled 8 XXXXXXXXXX XXXXXXXXXX 12355.4 24710.8 3 Cancelled
27 Delivered 60 55 2 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 3 On Time
28 Canceled 5 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 1 Cancelled
29 Canceled 8 31492.5 22610 29070 26647.5 3 Cancelled
30 Delivered 45 45 6 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 3 On Time
31 Delivered 45 44 2 8414.56 11474.4 11474.4 9562 1 On Time
32 Delivered 30 30 1 XXXXXXXXXX XXXXXXXXXX 39246.4 XXXXXXXXXX 5 On Time
33 Delivered 30 31 4 19570.7 22123.4 23825.2 25527 2 On Time
34 Delivered 45 60 3 20941.2 16287.6 22686.3 19196.1 4 Disruptive
35 Delivered 45 42 2 XXXXXXXXXX 28219.6 XXXXXXXXXX 21164.7 2 On Time
36 Returned 45 5 XXXXXXXXXX 22484.7 19272.6 12848.4 1 Returned
37 Delivered 60 55 1 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 3 On Time
38 Returned 8 9755.85 7004.2 5503.3 8755.25 1 Returned
39 Delivered 45 75 8 1255.65 1027.35 1027.35 1103.45 4 Disruptive
40 Delivered 60 75 2 8955.9 XXXXXXXXXX 6567.66 6567.66 5 Disruptive
41 Delivered 90 135 5 23751.6 XXXXXXXXXX XXXXXXXXXX 27710.2 3 Disruptive
42 Delivered 30 25 1 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 4 Early
43 Delivered 45 92 8 25365.6 25365.6 23414.4 27316.8 4 Disruptive
44 Delivered 60 55 2 12915 11193 16789.5 12484.5 3 On Time
45 Delivered 60 65 4 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 1 On Time
46 Canceled 5 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 23726.1 3 Cancelled
47 Delivered 45 45 3 XXXXXXXXXX XXXXXXXXXX 16411.2 XXXXXXXXXX 4 On Time
48 Delivered 45 46 2 7267.75 4983.6 7683.05 6021.85 3 On Time
49 Canceled 8 2024.8 3340.92 2328.52 3239.68 3 Cancelled
50 Delivered 45 30 7 2773.71 3595.55 2362.79 3698.28 1 Early
51 Delivered 45 35 8 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 1 Early
52 Delivered 45 30 7 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 5 Early
53 Delivered 45 80 8 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 3 Disruptive
54 Delivered 45 45 1 16088.5 27280.5 18187 20285.5 1 On Time
55 Delivered 45 60 8 20799 16177 18488 15021.5 4 Disruptive
56 Open 45 3 7578 6630.75 4925.7 3789 5 OPEN
57 Canceled 8 XXXXXXXXXX 17401.6 XXXXXXXXXX 15226.4 1 Cancelled
58 Delivered 45 30 7 4210.15 4210.15 3127.54 2646.38 2 Early
59 Delivered 45 46 9 13641.3 23130.9 23724 21944.7 4 On Time
60 Canceled 8 13340.8 14591.5 12507 14174.6 2 Cancelled
61 Open 60 1 14745.8 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 5 Open
62 Delivered 45 38 9 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 4 Early
63 Returned 8 4440.24 6184.62 3171.6 4123.08 2 Returned
64 Open 90 1 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 4 Open
65 Delivered 45 75 8 5623.8 2884 3028.2 4470.2 5 Disruptive
66 Canceled 8 21596.9 33226 26580.8 XXXXXXXXXX 5 Cancelled
67 Delivered 30 30 2 14815.2 21605.5 22222.8 19136.3 4 On Time
68 Delivered 30 25 6 5155.5 5302.8 4419 4124.4 2 Early
69 Delivered 15 5 9 3235 4690.75 3558.5 3396.75 5 Early
70 Delivered 45 35 7 4594 5742.5 9188 5053.4 4 Early
71 Delivered 45 15 9 XXXXXXXXXX XXXXXXXXXX 32290.4 XXXXXXXXXX 2 Early
72 Delivered 30 12 1 33942.8 25457.1 XXXXXXXXXX 33942.8 3 Early
73 Open 45 1 7099.75 7708.3 5679.8 7708.3 1 Open
74 Open 45 8 XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX XXXXXXXXXX 1 Open
75 Open 90 6 3588.03 3322.25 2923.58 4916.93 2 Open
Supplie
ID SupplierName
1 BENBE
2 CROSSCUT
3 BECK GROUP
4 BLAIR
5 SUPERIOR WOODS
6 FISHER AND SONS
7 LINEAR
8 MEMOA
9 VELODROME
10 JOHNSONFIVE
11 TIMBERLAND
12 BLAIR
SalesOrders
SalesOrderID Amount Paid Days DaysOutstanding Custome
1 17258 Y 30 1
2 21767 Y 30 2
3 29583 N 137 3
4 21445 Y 55 5
5 7187 Y 78 4
6 13813 Y 15 7
7 46972 Y 80 8
8 47062 Y 80 10
9 25755 Y 50 6
10 16769 Y 30 9
11 19950 Y 30 11
12 40538 Y 15 13
13 32233 Y 15 14
14 80773 N 91 10
15 23810 Y 60 16
16 38111 Y 55 17
17 49654 Y 45 18
18 33881 Y 30 5
19 36476 Y 30 19
20 16969 Y 30 20
21 14466 N 75 22
22 8434 N 74 25
23 27632 Y 45 26
24 14443 Y 50 28
25 35317 N 62 9
26 28537 N 60 29
27 18447 Y 30 7
28 26438 N 56 30
29 49812 N 52 22
30 51957 N 41 10
31 22759 N 36 33
32 7658 N 35 35
33 49461 N 31 36
34 32279 Y 15 37
35 23778 N 19 18
36 44948 Y 15 12
37 28662 N 13 3
38 20640 N 11 9
39 11085 N 8 39
40 31231 N 4 40
Custome
ID CustomerName State SalesTaxRate
1 AD&D HI 4
2 Brad Cage CA 7.25
3 Nicolas Pitt CO 2.9
4 Brookhaven Museum FL 6
5 Prados FL 6
6 BlueBirds CA 7.25
7 Peter Nowitzki ME 5.5
8 Belle Jones MO 7
9 Rudy Branson NY 4
10 Carl Rooks RI 7
11 Houston Nets CA 7.25
12 Whitney Simpson WI 5
13 Cameron Jolly CT 6.35
14 MegaCartoons LA 4.45
15 Carla Asarte WV 6
16 Kendra Keys KY 6
17 Vanessa Owens AZ 5.6
18 Dikembe Williams CA 7.25
19 PPR International NY 4
20 Bonanza NY 4
21 Kofi Kwansa TN 7
22 Petrov Petrovski WA 6.5
23 Emmanualla Lagrande FL 6
24 Jesse Williams CA 7.25
25 MegaLegos NY 4
26 Patricia Keynes MD 6
27 Jacques Piaf IN 7
28 La
y Jordan AL 4
29 Michael Bird NY 4
30 Bob Segher OR 0
31 Marvin Bday WA 6.5
32 Margarita Long FL 6
33 Peter Skenazi NY 4
34 Danielle Kerger WA 6.5
35 Hakeem Johnson WA 6.5
36 Lucy Lee AZ 5.6
37 Chaquille O'Neal AZ 5.6
38 Jessica Marcoux CA 7.25
39 Vlad Gue
o CA 7.25
40 Annie Karlsson DE 0
Designe
ID DesignerName
1 Michelle Angelo
2 Vincent Monet
3 Pie
e Peiyo
4 Alexandra Nanini
5 Clara Walke
OverDueCodes
Code Name
1 Outstanding
2 Overdue < 30
3 Overdue 31-60
4 Overdue 61-90
5 Overdue > 90