Another key part of NoTable's success is its designers, highly-paid artists who design the tables with and for customers. They currently have five designers, and one is assigned to each purchase order. They meet extensively with each of the customers and develop comprehensive specifications.
Production steps and required raw materials are specified with the highest level of detail. Accuracy of cost estimates are of utmost importance for NoTable given that they determine a table's price.
You receive data generated by NoTable's ERP system that show the estimated and actual labor and material cost in dollars for each production order (table). You can find these data in the PurchaseOrders worksheet. You are asked to analyze how accurate the design specifications are both overall and among designers.
- Determine and analyze the overall variances.
- Compare variances among designers.
Please download the Excel input file. complete your Excel file. create your Word file. and submit (upload) your completed Excel and Word files. I have uploaded everything needed, please make sure to answer it in EXCEL AND WORD FILES
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 their ERP system. The underlying data model is shown here, followed by a data dictionary. Data Model PurchaseOrders | fo Comers os QuoteLeadTime ActualLeadTime Supplier Designer DeliveryStatus EC one LaborActual reese RawMaterialActual Designer ST SalesOrders Customer ? sulesonderid Er Paid Days DaysOutstanding Customer 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 RawMaterialEstimate The estimated dollar amount for raw materials for the production order. RawMaterialActual The actual dollar amount for raw materials for the production order. Designer ID of the designer who designed the production order (table). Supplier Name Description ID Uniquely identifies a vendor. Name A vendor’s name. SalesOrders 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 receivable has been outstanding. Customer ID of the customer to whom the table was sold. Customer Name Description ID 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. Designer Name Description ID Uniquely identifies a designer. Name A designer’s name. Click here to view the Excel. Part of NoTable’s success rests on its use of premium wood such as Bubinga and Dalbergia. One of its main challenges is to find suppliers for such wood. They currently work with eleven different suppliers, and selection is exclusively based on price: the order goes to the supplier with the lowest price. You point out that supplier performance, particularly on-time delivery, should be considered as well. Risks related to late deliveries include lost sales, downtime, or not meeting the delivery schedule. Late deliveries can dramatically impact the bottom line. You decide to use the data to support your statement. You start by developing an algorithm to create a field called DeliveryStatus that contains one of the following values: Early, Ontime, Late, Disruptive, Canceled, Returned. You already added that column to the NoTable file. 1. Using the values in the DeliveryStatus field, explore the delivery risks NoTable is facing. 2. Assess the risk of each supplier, and assign them one the following labels: Safe, Risky, High-Risk. 3. To mitigate supplier risk, how would you suggest tiering NoTable’s suppliers? Please download the Excel input file, complete your Excel file, create your Word file, and submit (upload) your completed Excel and Word files. PurchaseOrders PurchaseOrderIdStatusQuotedLeadTimeActualLeadTimeSupplierLaborEstimateRawMaterialEstimateLaborActualRawMaterialActualDesignerDeliveryStatus 1Delivered3028128560.331416.3326656.2835224.371On Time 2Delivered304531901.552263.752807.052535.45Disruptive 3Canceled4548222416.5220815.3422416.5229621.834Cancelled 4Canceled60531322.7219470.8830476.1626243.364Cancelled 5Delivered90630709.8227096.923483.9825290.445Early 6Delivered12013335356.126312.574399.676503.865Late 7Returned6063214657.7614250.615879.2410993.323Returned 8Delivered3042125033.817447.816689.221999.43Disruptive 9Returned30520146.6222471.2317822.0115497.45Returned 10Delivered6075328459.4431403.5229440.823552.645Disruptive 11Returned302828382837730.89387.44Returned 12Delivered90100317558.7221399.6910974.213169.043Late 13Canceled4546111542.7211542.728657.0410460.595Cancelled 14Delivered1515430997.0520369.4929225.7923912.012On Time 15Delivered3030420634.0416212.4622107.916949.392On Time 16Canceled516238.7516238.7524033.3514939.654Cancelled 17Delivered454027426.249363.529040.647426.245Early 18Delivered4575619417.9713120.2511545.8211021.015Disruptive 19Delivered3029112918.3212501.610834.728334.43On Time 20Returned530191.3531053.9631916.5721565.255Returned 21Delivered3032224288.281641118380.3214441.682On Time 22Delivered302941188.21960.531366.432198.174On Time 23Delivered4540632941.2422283.7836816.6826159.222Early 24Delivered45123533989.4619993.836988.5334989.155Disruptive 25Delivered454518770.88186.088770.810817.322On Time 26Canceled822857.4919768.6412355.424710.83Cancelled 27Delivered6055216335.2725669.7119446.7524891.843On Time 28Canceled510721.2816081.9212954.8816528.641Cancelled 29Canceled831492.5226102907026647.53Cancelled 30Delivered4545622511.4429747.2624923.3817687.563On Time 31Delivered454428414.5611474.411474.495621On Time 32Delivered3030132378.2826491.3239246.428453.645On Time 33Delivered3031419570.722123.423825.2255272On Time 34Delivered4560320941.216287.622686.319196.14Disruptive 35Delivered4542223281.1728219.615520.7821164.72On Time 36Returned45518630.1822484.719272.612848.41Returned 37Delivered6055127455.6829171.6624023.7218875.783On Time 38Returned89755.857004.25503.38755.251Returned 39Delivered457581255.651027.351027.351103.454Disruptive 40Delivered607528955.910747.086567.666567.665Disruptive 41Delivered90135523751.616626.1219001.2827710.23Disruptive 42Delivered3025115769.9119576.4413594.7516857.494Early 43Delivered4592825365.625365.623414.427316.84Disruptive 44Delivered60552129151119316789.512484.53On Time 45Delivered6065411371.7515920.4514555.8417739.931On Time 46Canceled522144.3630053.0618980.8823726.13Cancelled 47Delivered4545315317.1215317.1216411.217505.284On Time 48Delivered454627267.754983.67683.056021.853On Time 49Canceled82024.83340.922328.523239.683Cancelled 50Delivered453072773.713595.552362.793698.281Early 51Delivered4535817187.3321622.7719959.4821068.341Early 52Delivered4530733089.2626993.8733089.2626993.875Early 53Delivered4580829433.2417169.3917986.9818804.573Disruptive 54Delivered4545116088.527280.51818720285.51On Time 55Delivered4560820799161771848815021.54Disruptive 56Open45375786630.754925.737895OPEN 57Canceled810005.9217401.612616.1615226.41Cancelled 58Delivered453074210.154210.153127.542646.382Early 59Delivered4546913641.323130.92372421944.74On Time 60Canceled813340.814591.51250714174.62Cancelled 61Open60114745.824330.5727279.7328754.315Open 62Delivered4538918391.2315604.6821177.7821177.784Early 63Returned84440.246184.623171.64123.082Returned 64Open90131295.3422837.1431295.3430449.524Open 65Delivered457585623.828843028.24470.25Disruptive 66Canceled821596.93322626580.819104.955Cancelled 67Delivered3030214815.221605.522222.819136.34On Time 68Delivered302565155.55302.844194124.42Early 69Delivered155932354690.753558.53396.755Early 70Delivered4535745945742.591885053.44Early 71Delivered4515927446.8418566.9832290.429061.362Early 72Delivered3012133942.825457.117819.9733942.83Early 73Open4517099.757708.35679.87708.31Open 74Open45816031.2519878.7513466.2513466.251Open 75Open9063588.033322.252923.584916.932Open Supplier IDSupplierName 1BENBE 2CROSSCUT 3BECK GROUP 4BLAIR 5SUPERIOR WOODS 6FISHER AND SONS 7LINEAR 8MEMOA 9VELODROME 10JOHNSONFIVE 11TIMBERLAND 12BLAIR SalesOrders SalesOrderIDAmountPaidDaysDaysOutstandingCustomer 117258Y301 221767Y302 329583N1373 421445Y555 57187Y784 613813Y157 746972Y808 847062Y8010 925755Y506 1016769Y309 1119950Y3011 1240538Y1513 1332233Y1514 1480773N9110 1523810Y6016 1638111Y5517 1749654Y4518 1833881Y305 1936476Y3019 2016969Y3020 2114466N7522 228434N7425 2327632Y4526 2414443Y5028 2535317N629 2628537N6029 2718447Y307 2826438N5630 2949812N5222 3051957N4110 3122759N3633 327658N3535 3349461N3136 3432279Y1537 3523778N1918 3644948Y1512 3728662N133 3820640N119 3911085N839 4031231N440 Customer IDCustomerNameStateSalesTaxRate 1AD&DHI4 2Brad CageCA7.25 3Nicolas PittCO2.9 4 Brookhaven MuseumFL6 5PradosFL6 6BlueBirdsCA7.25 7Peter NowitzkiME5.5 8Belle JonesMO7 9Rudy BransonNY4 10Carl RooksRI7 11Houston NetsCA7.25 12Whitney SimpsonWI5 13Cameron JollyCT6.35 14MegaCartoonsLA4.45 15Carla AsarteWV6 16Kendra KeysKY6 17Vanessa OwensAZ5.6 18Dikembe WilliamsCA7.25 19PPR InternationalNY4 20BonanzaNY4 21Kofi KwansaTN7 22Petrov PetrovskiWA6.5 23Emmanualla LagrandeFL6 24Jesse WilliamsCA7.25 25MegaLegosNY4 26Patricia KeynesMD6 27Jacques PiafIN7 28Larry JordanAL4 29Michael BirdNY4 30Bob SegherOR0 31Marvin BdayWA6.5 32Margarita LongFL6 33Peter SkenaziNY4 34Danielle KergerWA6.5 35Hakeem JohnsonWA6.5 36Lucy LeeAZ5.6 37Chaquille O'NealAZ5.6 38Jessica MarcouxCA7.25 39Vlad GuerroCA7.25 40Annie KarlssonDE0 Designer IDDesignerName 1Michelle Angelo 2Vincent Monet 3Pierre Peiyo 4Alexandra Nanini 5Clara Walker OverDueCodes CodeName 1Outstanding 2Overdue < 30="" 3="" overdue="" 31-60="" 4="" overdue="" 61-90="" 5="" overdue=""> 90