BBB Finished Goods Inventory BBB 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. At the beginning of the season, the quantity on hand of each item is zero. The quantity on hand increases when BBB gets deliveries from its manufacturer (outside the scope of this case) and decreases as it ships the products to the retailers to fill orders. BBB Shipments BBB makes one shipment for each retailer order and records revenue when the products are shipped. Warehouse employees prepare the shipments. All shipments are made under BBB's contract with a shipping company. BBB charges customers for the cost of the shipment, so the amount due from the retailers depends on the wholesale price of each item, the quantity shipped, and the shipping cost for the shipment. Payments to the shipping company are outside the scope of this case. BBB Cash Receipts Retaller customers send payment by check according to the payment terms (B8B standard payment terms are 2%10 days net 30 , meaning they receive a 2% discount on any amount they pay within 10 days with the net balance due within 30 days). The payment from the retailer customer always applies to only one order, but sometimes the retallers send multiple checks for that order. A BBB employee (accounts receivable) logs cash receipts from retailors. At the end of the day, the cash receipts are deposited intact into one bank account (BBB's main account). Each cash receipt is tracked by unique sequential cash receipt number. Exercise Requirements 1. Based on the preceding information and the following attributes list, prepare a UML class model and corresponding table listing describing BBB's sales to retailers. List the tables in the following order: resources, events, agents, type images, and linking tables. Identify the primary keys and foreign keys in each table. Click bereto access the Attribute Listing for BBB to be used for this exercise. 2. Based on the preceding information, prepare a BPMN activity model that describes BBB's Sales to Retailers process. The model should begin with retailers placing orders and end when B8B collects payments for the sales to the retallers. 3. An Excel spreadsheet with the BBB information needed for this Exercise can be accessed here. Create a new Access database, and import each worksheet in the spreadsheet into the database. Set appropriate primary keys. 4. After importing all the data, create relationships among tables to implement your data model. 5. Prepare queries to answer the following questions: Background: Baer Belly Bikinis Inc. (BBB) is a small business located in Santa Monica, California. It sells swimwear and related products to specialty stores throughout the United States. It also sells its products to individuals over a company website. Paige Boer founded BBB almost 10 years ago after she graduated from the Fashion Institute of Design ond Merchancising. She recognized the need for swimsuits sold as separates. Her business has grown rapidly, and now BBB has a large following of customers who want to be able to mix and match to find their ideal swimsuit. Currently, BBB products are carried in more than 1,000 specialty swimwear boutiques and online retallers. During an initial interview with Paige Baer, she outined BBB's business processes. She acknowledged that she doesn't know much about accounting and information technology. As the business grew, her accounting suffered and information systems were added piecemeal. So, she is looking forward to an assessment of her requirements and recommendations that would position 88B for substantial further growth. First, Paige described BBB sales to independent boutique retailers. BBB uses a group of independent sales representatives to sell its products to boutique retallers around the country. These sales representatives are not 88B employees. They are paid commissions based on the dollar volume of soles. After working with the sales representatives; individual retailers call BBB to place their order for the upcoming season. BBB will then ship the products when they become available. The retailers are expected to pay for shipments within 30 days. BBB offers a prompt payment discount for payment within 10 days. The following material summarizes BBB's activities to prepare for and conduct the sales process: - Summer - B88 finalizes designs for next year's products and prepares tatalog materials with information on its future products. - Fall to Spring - BBB sales representatives (independent agents working on commissions) visit retailers to develop sales. (BBB's payment of commissions to sales representatives is outside the scope of this case, although you should include the Sales Reps in your model and database.) - BBB's retailer customers place orders for one of two deliveries in season: (1) early spring (Aprii) and (2) early summer (May/June). BBB records information on its retallers before they place any orders. - Upon receipt of finished goods from their manufacturer in April and May, BBB ships products to retailers. - Retailers' payments are due 30 days after shipment and include shipping costs. Some retailers pay late. Some take advantage of the prompt payment discounts. BBB Finished Goods Inventory 3. An Excel spreadsheet with the BBB information needed for this Exercise can be accessed here. Create a new Access database, and import each worksheet in the spreadsheet into the database. Set appropriate primary keys. 4. After importing all the data, create relationships among tables to implement your data model, 5. Prepare queries to answer the following questions: a. What was BBB's total revenue? b. What is BBB's account receivable balance for each retailer customer as of the end of June? c. Which BBB product generated the largest sales volume? (List all products in descending order of sales dollars). Connect Requirements Complete the Chapter 5 Comprehensive Exercise using the instructions and resources listed above. For your Connect assignment, Sections 1 and 2 should be attempted after you have completed Exercise Requirement 2 listed above. Section 3 should be attempted after you have completed Exercise Requirement 3. Section 3, Question 1 Each question should be attempted after you have completed Exercise Requirement 3. Move the Excel data to Microsoft Access and prepare queries to answer the following questions. Answers in dollars should be rounded to two decimal places. Differences in rounding may produce slightly different totals, so select the closest answer when your exact answer is not shown in multiple choice scenarios. When you summarize information by store, be sure to include all stores and convert and blanks (NULL values) to zeroes. Required: Use the Retailer Sales Items table. For each record, calculate the wholesale price times the quantity (qty) ordered and name this field "amount." How many records are there in your query? (Save this query as "Sales Detair for use in subsequent queries. Format the amount field as currency.)