Water You Up To (WYUT) is a small business located in San Luis Obispo, California. It sells swimwear and related products to specialty stores throughout the United States. Cecelia Archer founded WYUT almost 10 years ago and her business has grown rapidly. A simple accounting system is currently used, but Cecelia is worried it is not robust enough to handle future transactions as the business continues to grow. Cecelia has hired your team to help her develop business model diagrams and implement a new accounting system using Microsoft Access. Given her inexperience with Microsoft Access, she has also requested you provide her code to run common queries she will need monthly after the project is complete. The two processes Cecelia would like to document are: (1) sale of inventory and cash receipts process (sales process); and (2) purchase of raw materials and cash disbursement process (purchases process). The following information is provided to help your team develop business model diagrams. An Excel spreadsheet with transaction data (Module_2_Dataxlsx) has been provided to your team for input into Microsoft Access. Deliverables When the project is complete, each group will submit three deliverables: 1. Business Model Diagrams Summary Report (PDF) a. There are four separate diagrams requested. b. Submit all diagrams in one document with four pages. c. Make sure each diagram is appropriately labeledamed. 2. Microsoft Access Database (Access database) a. Submit the file used to generate query results. b. Make sure the file has saved queries. 3. Query Results (Excel) a. There are eight queries requested. b. Submit all results in one Excel workbook with eight worksheet tabs. c. Identify worksheet content by renaming tabs "Query_l", "Query_2", etc. SalesProcess WYUT provides retailers with a catalog of swimwear and related products for next summer in July of each year. WYUT manufactures their own swimwear and tracks products that are ready to be shipped in the Finished Goods Inventory account. WYUT tracks its inventory by catalog number (catalog\#). Each product is identified by color code, use (e.g., tops or bottoms), and type (e.g., the specific design of the piece). The color codes reflect the color and fabric design options, and they can change each year. Individual retailers can place orders between October and November by emailing a WYUT sales representative. When an order is received by a sales representative, the same sales representative is responsible for filling and shipping the order. Orders are usually shipped starting in April. The accounting department is notified when an order is shipped, and they issue an invoice. Customers are billed for products ordered and shipping costs. Standard payment terms are 2/10/30, meaning they receive a 2% discount on any amount they pay within 10 days with the net balance due within 30 days. When WYUT receives a payment check, the accounting department records the payment in the Cash Receipts account and deposits the check in their main bank account. The payment from the customer always applies to only one order, but sometimes the customer sends multiple checks for a single order. Each cash receipt is tracked by a unique sequential cash receipt number. Purchases Process WYUT manufactures their own swimwear using fabric purchased from local fabric vendors. Every year starting in December, production department employees review the sales orders and determine the quantity of fabric needed from various fabric vendors. After a purchase order is placed, fabric vendors usually ship the fabric within 2 weeks. A warchouse employec receives the fabric and verifies quantities received. Next, a production employee verifies the quality of the fabric received. If the fabric is deemed unusable, the vendor is contacted, and a return is initiated. If the fabric is acceptable, the accounting department initiates payment to the vendor. Each payment to the vendor applies to a single purchase. Purchases are sometimes paid off in installments (i.e., multiple payments over time). All vendors are paid using checks. WYUT assigns sequential numbers to each check. All checks are written on its main bank account. Dataset. x lsx \begin{tabular}{l|lllr} \hline 1 & Account & Bank & \multicolumn{1}{c}{ Account Description } & Balance \\ \hline 2 & 0057909100 & Bank of America & Main Account & $327,472.50 \\ \hline 3 & 0057909105 & Bank of America & Payrall & $0.00 \\ \hline 4 & 0057909110 & Bank of America & Special Purpose & $175,000.00 \\ \hline 6 & & & & \end{tabular} Exercise Requirements 1. Based on the preceding information, prepare the following BPMN activity models: a. Sales Process o Include two pools: Customer \& WYUT b. Purchases Process o Include two pools: Vendor \& WYUT - Vendor pool can be opaque. 2. Based on the preceding information, prepare the following UML structure models using an REA framework: a. Sales Process o Include the following classes: Cash, Cash Receipts, Employees, Finished Inventory, Inventory Types, Retailer Customers, Retailer Sales b. Purchases Process o Include the following classes: Cash, Cash Disbursements, Employees, Fabric Purchases, Fabric Inventory, Fabric Vendors 3. Create an Access database using the Module_2_Data Excel spreadsheet. o Start by importing each worksheet. - Create relationships among tables to implement your data model. o Make sure you set appropriate primary and foreign keys. 4. Prepare SQL queries to generate the following tables. For each query, save the query in Microsoft Access so it can be run again. In addition, export and save the results in Excel. - Query \#1: Create a table that shows the total sales per order sorted highest to lowest. Columns: Order\#, TotalSales (calculated) Rows =55 Hint: Order\# 010105=$2,726 4. Prepare SQL queries to generate the following tables. For each query, save the query in Microsoft Access so it can be run again. In addition, export and save the results in Excel. - Query \#1: Create a table that shows the total sales per order sorted highest to lowest. Columns: Order\#, TotalSales (calculated) Rows =55 Hint: Order\# 010105=$2,726 - Query \#2: Create a table that shows the total amount invoiced per order which equals total sales (from Query \#1) plus shipping cost. Columns: Order\#, TotalSales, Shipping_Cost, Totallnvoice (calculated) Rows =55 Hint: Order\# O10100 Totallnvoice =$1,184.25 - Query \#3: Create a table that shows customer orders paid within the discount period. Columns: Order\#, Rcpt_Date, Delivery_Date, DaysOutstand Rows =5 Hint: There should be two orders paid in 4 days. - Query \#4: Create a table that shows total sales by Catalog\# during November 2022. Columns: Catalog\#, Description, TotalSales (calculated) Rows =54 Hint: Small V Neck Tankini in KORE had the highest total sales ($2,106.00) - Query \#5: Create a table that shows the department and name (first and last) of employees involved in cash receipt activity and the number of transactions they were involved in. Columns: Emp\#, Last_Name, First_Name, Department, NumberTransactions (calculated) Rows =4 Hint: Emp\# E1025 should have the most transactions. - Query \#6: Create a table that shows cost of fabric raw materials (calculated as Fabric Cost Per Yard times Qty_on_Hand) ordered by Color Name in ascending order. Columns: Color Name, Color Code, Fabric Cost Per Yard, Qty_on_Hand, Rawlnventory (calculated) Rows =10 Hint: Total cost of Midnight Symphony =$22,005. - Query \#7: Create a table that shows fabric vendors used for purchases during 2022 along with amounts owed and amounts paid. Columns: Vendor_Number, AmountOwed, AmountPaid Rows =7 Hint: FV1002 was owed and paid $32,017.61. - Query \#8: Create a table that shows the cost to manufacture each unique item currently in finished inventory (you can ignore size because it does not impact cost). Columns: Description, Color Code, Yds Fabric Required, Fabric Cost Per Yard, TotalCost (calculated as Yds Fabric Required * Fabric Cost Per Yard) Rows =14 Hint: Adjustable Tie Side in LABK color has a total cost of $2.70