Paragraph Styles Z CHEDOR Project Fall 2019 Also, prepare a corresponding data listing' for the prototype database. Identify where different fields/attribute names provide the same information and develop recommendations for charges in field names. Review the data model with both Mr. Z and Prof Smith before working on developing your database. Prepare a prototype database for Z CHEDDR. Mr. Z has agreed to provide financial data from his business for the 1* quarter of his fiscal year. That data will be in EXCEL format. You will move the data into Access, structure it appropriately, and set relationships consistent with your data model. Make changes as necessary in file structures to Implement efficient tables and eliminate duplication. 5. Answer the Z Chedde questions in CONNECT. Follow the CONNECT instructions and prepare appropriate queries to answer the questions (rather than using the EXCEL data]. 6. Prepare financial reports using your database. You will prepare queries as necessary to provide the following reports and management information. a. Z CHEDDR income statement for the 1" quarter. b. Z CHEDDA balance sheet as of the end of the 1" quarter. Income statements for each Z CHEDDR stores for the 1# quarter. d. Other performance measures, such as ROA, ROE, Inventory turnover, etc, that you believe would be important to Z CHEDOR management. 7. Evaluate the financial performance of Z CHEDDR for the 1" quarter. Use your Access queries to also prepare formal financial statements and compute appropriate ratios to evaluate performance against the industry and past performance. Also, use one of the analytics software products, Tableau of Power BI, to compare results for the current quarter against historical performance and industry performance to the extent that you can find comparable industry data. Clearly explain what those performance measures mean for Z CHEDDR. Recommend changes to Z CHEDDR business processes necessary to implement an integrated system that will capture sales and inventory information at each store, track inventory movement, manage human resource information and payroll, integrate with the Z Cheddr website, etc. You may assume that Mr. Z has committed to purchasing smart cash registers for each store, mobile devices for van drivers, and other technology necessary to implement the integrated system. Document your recommendations for at least three critical processes by preparing BPMN diagrams that describe Z CHEDDR processes post-implementation. Explain how your recommended changes will create value for Z CHEDOR. 9. Evaluate the extent to which Z CHEDDR complies with COSO principles and recommend appropriate and cost-effective actions to bring Z CHEDDR into compliance, improve internal controls and reduce risk. Report to Z CHEDOR Submit all the deliverables in one report to Z CHEDDR at the end of the project. Also, submit your prototype Access database with all the queries along with that report. Include a cover page and table of contents. Present your report in an executive summary style and not longer than 6 pages not counting the cover page, table of contents, any appendices or attachments. Include financial statements, data model, BPMN activity diagrams, and other information necessary to support your analyses and conclusions in appendices or attachments. Make your report content clear to an accounting novice such The data lasting is different than the table listings that you have prepared. You are to list all the attributes/fields in alphabetic order, define what the field means, indicate which table(s) contain that field, and whether it is a primary key or foreign key in those tables.A Normal No Spac... Heading 1 Heading 2 Title Paragraph Styles as Mr. Z. Present your findings using tables, charts, and bullet points whenever possible Avoid long paragraphs. Administrative Information Submit your complete project through the dropbox on or before midnight November 27. The complete project indudes one report with all appendices and the Access database with dearly named queries for each group. Also, each team member must submit an individual peer evaluation. As soon as your group is ready, you should schedule an interim review of your data models and activity diagrams with me. I will provide feedback on the models that you should then incorporate before the final report is due. Grading criteria. I will provide detailed grading criteria by the time you are ready for your interim reviews. I will grade your project relative to the other groups. In general, however, I expect the following: You should perform professional work; quality is important. You will be graded on the quality of your preparation, group interactions, and dealings with Mr. Z. You should know how to prepare financial statements and how to explain them to a non- accountant. You will be graded on the accuracy and quality of the financial statement information. You should be accurate (do not make assumptions; ask Mr. Z). You have all the data necessary to complete the project. You should support all recommendations with facts; explain why Z CHEDDR should follow your advice. Avoid vague statements and opinions (such as Z CHEDDR performance is good). Tip. The best grades typically go to the group that starts fast, gains a thorough understanding of Z CHEDOR's business through questions and examining the data and does the best job of allocating the workload. You should be curious about everything and check all your results at least twice. Lifelines. You get two free lifelines. In other words, if you are stuck-you know what to do but you don't know how to do it- you can ask me for help twice without penalty. After that, it will cost you 5 points per request. The choice to use a lifeline should be a group decision. Group participation. I will reserve 25%% of the grade to adjust according to your group's peer evaluations that must be submitted separately by each individual group member on or before the project due date. I will first determine the group grade. Then, I will adjust grades upward for the group members that contributed the most and downward for the group members that contributed the least. If you fail to submit a peer evaluation, you will be penalized 5 points.Font Paragraph Styles Editing Overview The following interviews were conducted prior to the start of the project to explore specific aspects of Cheddr's business processes. The interviews with employees provide information that is representative of activities performed by all employees with the same titles. For example, the interview with Gordon, manager of the Bend store, provides information about the activities that all store managers perform. It is not suggested that these interviews provide complete information about all of Z Chedde's business processes. They are meant to provide an initial overview of the business, Summary of Interview with Mr. Z Western Oregon's mild climate and abundant rainfall contribute to the state's allure for dairy farmers. Mr. Z routinely said, "We have more green grass longer than any state in the country. " What's more, land prices and living costs are relatively low in Oregon, luring a number of cheesemakers from neighboring California. Many new cheesemakers are also attracted by the cross-marketing potential of locating in Oregon wine country. Those were some of the reasons that he started Z Cheddr just over 10 years ago. His business quickly grew from one store to 10 stores plus an internet presence. Recently, he ako started selling some cheeses on a wholesale basis to small groceries and other cheese and wine shops on the west coast. Mr. Z is the sole owner of Z Cheddr and operates his business as a limited liability corporation. He has a small board of directors, including his daughter and her husband, but they are seldom involved in any real business decisions. Mr. Z is a good businessman and knows a lot about selling wine and cheese. Unfortunately, Z's accounting knowledge is marginal. His systems did not keep up with the growth of his business. He currently relies heavily on manual processes, and his data is spread among several different systems and EXCEL spreadsheets. He plans to expand his business to other west coast states to take advantage of the rapid growth in artisan cheese, wine, and beer makers. So, in addition to providing financial information about his current performance, he wants advice on the risks his business faces and cloud based accounting system where each store submits transactions daily via the internet. He also sells cheeses and cheese and wine gift baskets over the internet. His website looks good, but the backend processes require heavy manual intervention. After a customer places an order and pays by credit card, his employees must print the orders and move to another system to update inventory, prepare shipping documents, and record the sales. He knows that this can be streamlined, so he also wants an overall evaluation of how he can use information technology to streamline his online process. Of course, he also wants to know how those changes would affect his internal control system. Summary of interview with Gordon Lightfoot, manager of the Bend store Gordon has been the manager of the Bend, Oregon store since it opened in 2005. He has made a number of friends among the regular customers that visit the store. Gordon is known in the area for his knowledge of cheeses and especially his knowledge of which wines to pair with which cheeses. Each morning, Gordon opens the store, checking to make sure that the display cases are at the right temperature, the cash drawers are ready, and the signs are set up to display the daily specials (in no| particular order). Then, Gordon or his clerks spend the day selling wine and cheese. A few times a week, Gordon hosts cheese and wine tasting promotions for his customers. These tastings have been very successful. Customers come from all over town to attend, and most leave purchasingboth the cheese and wine that they tasted. To determine which wine and cheese to promote, Gordon checks his inventory to be sure that he has plenty on hand so that customers will be able to buy the product after the promotion. Using the Z Cheddy promotions form, he carefully records the amount of cheese and wine pulled from inventory for the promotion, so those items are no longer counted as cost of goods (but rather as administrative (marketing) expense). After the promotion, he files the promotion form until he can send it to Chad, the Z Cheddr accountant, with the van driver. At the end of the day, Gordon closes out the cash registers and collects the various cash drawers. He does a final check of the display cases, making sure that all cheeses are appropriately refrigerated overnight. He puts the drawers and the cash register tapes in the safe for the bookkeeper. Summary of interview with Joan Baez, bookkeeper for the Bend store Joan is the part time bookkeeper for the store. The job is perfect for her, because she just comes in after the store closes, summarizes the daily sales for each inventory item (cheese or wine) in a spreadsheet. She emails the spreadsheet to Chad, the Z Cheddr accountant. Then, she prepares the cash drawers for the next morning, prepares the deposit slips, and takes the daily receipts to the bank for deposit. All this usually takes two or three hours. She files the deposit slips until she can send them to Chad with the van driver. Summary of interview with Bob Dylan, cheese and wine buyer Bob recently graduated from Oregon State University, where he worked with other food science students at the O.5 Creamery. They learned about cheese making by making and selling cheese, especially their famous Beaver Classic cheese. He uses his knowledge of artisan cheese making and his connections with other cheese makers to select the best cheeses to sell at Z Cheddr, Bob negotiated contracts with each of the cheese, so all he has to do is call them to place an order. Each day, Bob reviews the Z Cheddr inventory records to determine what to order. He considers recent sales trends and potential seasonal demand for particular products. He sometimes checks the physical warehouse inventory and calls stores to verify inventory levels, since he is not completely confident in the electronic records. He then calls the suppliers to place the orders. The suppliers deliver their products to the Z Cheddr central warehouse in Eugene Oregon. Occasionally, Bob will arrange for a van to pick up the order if it looks like shipment might be delayed. After the products are received and accepted in Eugene, Bob authorizes the payment and sends a copy of the supplier's invoice to Chad. Chad pays each cheese maker at the end of the month in which the cheese is received. Sometimes, he month. will combine payments if there is more than one shipment from the same cheese maker during the Recently, Bob has also assumed the duties of wine buyer. The wine buying process is identical to the cheese buying process. It requires a good relationship with the suppliers and knowledge of the products offered by each supplier. Bob has a good rapport with his suppliers, but he makes them compete for our business. This helps keep costs down and allows Z Cheddr to provide high quality wines for reasonable prices to our customers.Summary of intervie with Chad Mitchell, accountant Chad has been with Josh (Mr. Z) since the beginning. He used to work in the Bend store while he finished school, but when Mr. Z needed someone to do the accounting, Chad volunteered. Chad's job is to pay the bills, update the inventory records, put together the financial records, make sure Z Cheddr pays its taxes on time, and keep Mr. Z informed about company performance. Chad gets the sales and promotions information from the stores via the van drivers. He gets transfers, wholesale and internet sales information from shipping and receiving. That way, he can maintain up to date inventory counts and record sales daily. Z Cheddr conducts physical inventory counts each quarter, and Chad uses those counts to update or correct the electronic inventory records. Chad uses average inventory cost to value transfers, promotions, and ending inventory. The beginning inventory is valued at the average cost for the previous quarter (beginning inventory cost). Chad also pays all the bills. His clerk assembles the invoices and recurring payment information for both inventory purchases and administrative purchases and prepares the checks. The administrative purchases include recurring payments for rent of Z Cheddr retail stores, all of which are rented through one broker, Oregon Commercial Real Estate. Then Chad reviews the support material, corrects any errors, and signs the checks. Chad also gets the timesheets from each store at the end of the month and with his assistant enters that payroll information on the payroll services' website. Each employee then receives a monthly check from the payroll service. Chad transfers money from the main operating account to the payroll account to cover the payroll each month. Chad sends checks for federal and state payroll taxes, social security (FICA) and Medicare [MC) withholding, including the matching employer amounts for FICA and MC, to appropriate tax authorities each quarter. Oregon has no sales tax, so he doesn't have to make those tax payments. Chad and his assistant are also responsible for bank reconciliations each month. They use the deposit slips received from the stores to confirm deposits. Additionally, deposits include the internet credit card receipts and payments from the wholesale customers, 2 Cheddr uses their bank's merchant services to process customers' credit card payments. Credit card payment information is transferred electronically from the website to the bank's site. The bank takes their cut, which can range from 3 to 6% of the sale amounts, depending on the credit card the customer uses. It then electronically deposits the rest of the payment in the Z Cheddr account and sends an email to Chad to confirm the deposit. Of course, Chad payments. uses the Z Cheddr check book (and any transfer vouchers for transfers to the payroll account) to confirm Lately, Mr. Z has been more and more frustrated because Chad is too busy to give him any timely information on how the business is really doing. Chad barely has time to put together quarterly financials, let alone monthly comparisons to the budget. He is stretched way too thin and needs a better accounting system to take some of the load. summary of interview with Judy Collins, Shipping and Receiving clerk at Central Warehouse Judy's job is not easy, although she has help from several part time clerks. She records the incoming shipments from our cheese and wine suppliers in Oregon. She checks each shipment for damage, and in those rare situations where there is damage, she returns damaged items to the suppliers. She updatesthe invoices/delivery slips and sends those to Bob (the buyer], so Bob knows what has been received and can check that against what he ordered. Judy also updates the inventory records based on the items received and accepted. She and her clerks then store the items in the warehouse. Each day, Judy also prints the list of internet orders and pulls those items from inventory. She packs the products for delivery sets them aside for pickup by UPS. UPS handles all the outgoing shipments for our internet sales. UPS bills Z Chedor monthly for shipping costs, but so far, the flat rate $9.95 shipping charges that the customers pay have covered the shipping costs. Additionally, she packs the Z Cheddr vans that deliver to our ten stores around the state. Sometimes, she even drives the vans. She prepares the transfer lists for each store, and a clerk at the store confirms the deliveries. Then, she or one of the other van drivers brings the transfer sheets, along with any promotion forms and deposit slips sent from the stores, back to Chad, so he can update the inventory and accounting records. She also uses the company credit card to get gasoline for the vans or pay for repairs and maintenance. She sends the credit card slips to Chad so he can confirm the bills from the credit card company. Finally, Judy also takes orders from the wholesale customers, small grocers located in Oregon. After taking the order, she helps packs the vans and prepares an invoice. She sends the invoice with the delivery and the wholesale customers typically pay at the end of the month. Summary of interview with Bobby Darin, Van Driver at Central Warehouse Bobby is on the road most of the week. He helps Judy pack his van and then hits the road to deliver cheese and wine to the retail stores as well as to wholesale customers. He helps prepare the transfer list for the transfers to the retail stores. Then, he gets the manager or an employee at the retail store to confirm the transfer. When he gets back to the warehouse, he gives all the transfer forms to Chad (or puts them in his mailbox when he is not available]. Bobby also delivers to the wholesale customers. He usually combines trips to avoid unnecessary driving. The wholesale customers confirm receipt on a copy of the delivery document, and he gives them an invoice at that time. He brings those confirmed delivery documents back to the warehouse and again gives them to Chad. Summary of interview with Cameron Cody, Vice President Cameron is an old friend of Mr. Z. He was recently hired to run the business while Mr. Z pursued expansion plans. While familiarizing himself with the business, he became concerned about inadequate systems and lax internal controls. So, his focus is on selecting a new system and improving controls.Paragraph Styles Chedd Project Description and Requirements Background Mr. Z (real name Josh Zubkoff) runs a successful chain of cheese and wine shops in Oregon that specialize in Oregon artisan cheeses and local wines. He also sells his cheeses and wines in packages over the internet. He is considering expanding his business, but he thinks his current accounting and financial systems are restricting his ability to expand. He has come to you for assistance. Instructions Your supervisor met with Mr. Z and discussed his requirements in detail. At that meeting, they agreed on the following deliverables for your project. 1. Plan and manage your project with one of the tools listed in the separate Project Management Software Short List document. First, identify all the tasks and the team member(s) that will perform each task (the entire team may perform certain tasks). Then, each class period should start with a review of pending tasks and submitting your plan for the day to the dropbox. 2. Document process activity using BPMN. After gaining an understanding of Z CHEDDR's business processes, you will document current processes using business process modeling notation (BPMN). This involves documenting at least five current business processes. Each member of your team must prepare at least one BPMN diagram. These diagrams will serve multiple purposes. Z CHEDDR expects to use them to train new employees. In addition, they will provide information about potential improvements to those processes by the efficient use of information technology. They will also highlight areas of exposure to unnecessary risks in his business. As you finalize the BPMN diagrams, you should review the diagrams with Mr. Z and Prof. Smith (Mr. Z will evaluate whether the diagram accurately reflects his business, and Prof. Smith will evaluate whether the diagram meets BPMN standards.) 3. Prepare an integrated data model using UML class diagrams and the REA framework. This data model will serve as the blueprint for Z CHEDDR's prototype database that you will also develop