YNF Instructor Materia for Accounting Information Systems 3rd Edition by Vernon Richardson, Exams of Accounting

Download YNF Instructor Materia for Accounting Information Systems 3rd Edition by Vernon Richardson and more Exams Accounting in PDF only on Docsity!

SOLUTION MANUAL & INSTRUCTOR MATERIAL Accounting Information Systems 3rd Edition by Vernon Richardson, Chengyee Chang, Rod Smith ¥ Not Flowers Integrated Project Instructor Information Y Not Flowers ¥ Not Flowers is a demanding project but it is substantially less complex than the Z Cheddr project, primarily because Y Not Flowers does not maintain any continuing inventory levels. This project also requires fewer adjustments to file contents, since the linking tables are already split out. Contents Basic Steps in the Y Not Flowers Project Additional instructional opportunities Material to provide to students Instructor materials .... Description of data in EXCEL spreadsheet and appropriate treatment in Access UML Class Diagram 5 Sample Project Assignment Description Basic Steps in the Y Not Flowers Project The basic project involves analysis of ¥ Not Flowers data, preparation of UML class diagrams and BPMN diagrams, setting up an Access database, and preparing queries. There sre suggested extensions to the basic project. Prepare UML Class Diagram. Students use the ¥ Not Flowers employee interviews to develop a UML class diagram for ¥ Not Flowers. Then, they can use the Excel data to refine their diagrams. Prepare business process models (using BPMN). Students also use the ¥ Not Flowers employee interviews to develop BPMN diagrams. The number of diagrams is optional, but it is suggested that you require three to five diagrams. Note that students struggle with where processes start and end. The instructor diagrams are generally based on complete handling of one transaction, e.g., from placing an order to making the payment for that order. ‘Correct file structures (normalize). The worksheets are generally close to the final file structures. The students should find and correct data redundancies, such as in orders and sales items and purchase flowers items. Transfer data from an EXCEL spreadsheet to ACCESS. Establish appropriate relationships among the tables. Several tables have multiple links to joined tables, such as “payroll” has both a paying employee # and 3 paid employee #. Thus, “payroll” would be joined twice to “employees.” In that case, Access creates an “employees_1” ghost table to implement the join. See the instructor database. Prepare queries to produce financial statements. Examples are provided in the instructor database. 3. Income statement by store and overall b. Balance sheet cc. Other queries (instructor option) i. Accounts receivable detail Accounts payable detail Average equity and return on average equity iv. Current ratio, etc. Additional instructional opportunities aE Extensive CONNECT assignment that guides students through the basic project, including the UML class diagram, the table listing, and preparing many of the required queries. Project management and use of project management software. The student material contains a list of websites that provide either free or free trial versions of project management tools. Students can be required to use one of those tools and prepare a project mansgement plan. Then, their final submission can include the plan and their actual performance against that plan. 10. 11. 12. 13. (Cash. Shows the balance in each cash account at the beginning of the quarter. Students need to find the ending cash balances by adding cash receipts and subtracting cash disbursements to fisher, cash disbursement to miscellaneous vendors, and met pay to employees. Note that YNF moves money between accounts and that information is not provided, so the students only need to work with the totals. The primary key is account#. ‘Cash disbursements to growers. This table lists the cash disbursements to YNF’s flower growers. Again, payments are made on or near the end of the month. The primary key check. (Cash receipts. This table lists the cash receipts from customers. The primary key is rept. ‘Customers. This table lists the customers, flower retailers. The primary key is customent. Employees. This table lists the employees, including the stores they are assigned to, and their wage rates. The primary key is employeer. Flower Inventory. This table lists the flowers that YNF offers at all its stores along with the retail price and expected cost. The primary key is inventory#. ‘Growers. This table list information about YNF's flower suppliers. The primary key is grower#. Orders and sales. This table combines both order and sales information for YNF. Note that students should focus on sale date to ensure that transactions are limited to the first quarter. The primary key is order#. Orders and sales items. This is the linking table between Orders and Sales and Flower Inventory. The primary key is the combination of order? and inventory#. There is a foreign key linking each sale item to the corresponding purchase#. There is redundant information, flower name, that should be deleted from the table, since flower name relates to inventory# and is available in the Flower Inventory table. The amount of the sale is calculated by multiplying quantity sold (qty_sold) times sale price (sale_price) for each item and summing that amount for all items for each salle. Payroll. This table shows the gross pay, withholding, and net pay information for each employee and month. Note that the payroll expense includes the gross pay plus the employer FICA and MC (Medicare) contributions. YNF has not yet paid the withholding amounts, so those are liabilities at the end of the quarter. The primary key is check#. Purchase Flowers. This table lists the purchases of flowers from growers. The primary key is purchase#. Purchase Flowers Items. This is the linking table between Purchase Flowers and Flower Inventory. The primary key is the combination of purchase# and inventory. Again, this contains redundant information, flower name, which is available in the Flower Inventory table via the inventory#, that students should delete. Purchases Misc. This table lists the miscellaneous/administrative purchases, such as utilities, rent, taxes, etc. The primary key is PO#. Note that this table is combined with the corresponding cash disbursements information, since YNF pays each miscellaneous purchase individually. Note that to encourage students to examine the table contents closely, some items are recorded with a purchase date of 4/1/2018, but the notes field indicate that these items reflect March expense. These should be counted in the first quarter amd the purchase date should be changed to March 31. 14. Stores. This table lists the 12 stores for YNF. They are all owned by the company. The primary key is store? 15. Timecards. This table lists the summary information from the monthly timecards. Note that timecards must be linked twice to employees when setting relationships {and on the UML diagram) once for the employee that works and again for the store manager that scheduled the work. The primary key is timecard#. 16. Trucks. This table lists YNF delivery trucks, their original cost, and information necessary to calculate quarterly depreciation expense and accumulated depreciation for the quarter. All the trucks are assigned to stores. Y Not Flowers takes a full year's depreciation in the year acquired. The primary key is VIN. 17. Vendors Misc. This table lists the miscellaneous administrative suppliers, such as the utility companies, phone companies, etc. The primary key is vendor#. Note that students could combine the “purchases” tables, the “suppliers” tables, and the “cash disbursements” information. That simplifies the table structure, although it complicates their subsequent queries. In general, it is recommended that students keep the tables separate at least for part of the project. UML Class Diagram BPMN Diagrams Note that the following diagrams represent reasonable options for YNF's process diagrams, but they are not the only options. The allocation of tasks, however, is consistent with the interviews and data. Alternative models should not change the process flow or the description of which job function does each task. These are the main diagrams. They do not include simple processes such as paying taxes, preparing financial statements, and doing bank reconciliations. YNF Sales ¥ ¥ $ — oo i - . ies | oem ¥ | sou set face cm + +O This process starts with the receipt and recording af custamer orders. It includes a collapsed subprocess for Purchase Flawers. The process continues until payment is received and recorded. The diagram could be enhanced by adding data objects for the order, the delivery document/invoice, and the cash receipt/depasit. It could also include intermediate error events to include error situations for the Purchase Flowers subprocess (such as when ordered flowers are not available). YINF Flower Purchases Fi a —, $ tee i act oo scares et cunt ae rasum7e nose \ fon |" This process describes flower purchases and payments. it could be enhanced by including data objects for the purchase document and the check/paymant. It could also include intermediate error events when flowers are not available or when the review finds errors. YNF Timekeeping and Payroll ‘visor Spreadsheet 3 ) em {ee . 4 as -O | is } | oO | * c >) | Super Prepare | | lame hee kristin a ae re fron Process jet + | a | | Mia {hect) 4 Record Payroll —@) Pa \ Thisis a simple version of YNF's payroll process. The looping activity shows employees working and recording time during the month. The Payroll Process is described as 2 collapsed subprocess. It could be enhanced with data objects for the timecards, checks, and payroll records. 10 ACCOUNTING INFORMATION SYSTEMS, 30 Richardson, Chang. and Smith Sample Project Assignment Description ¥ Not Flowers Project Description and Requirements Y Not Flowers Background Forrest runs ¥ Not Flowers, Inc. (YNF], 3 wholesale flower distributor with stares in several major metropolitan areas of the U.S. He is considering expanding his business, but he thinks his current accounting and financial systems are restricting that expansion. He also worries that YNF is not adequately prepared to face expected business risks. He has come to you far assistance Instructions Your supervisor met with Forrest and discussed his requirements in detail. At that meeting, they agreed on the following deliverables for your project. 1. Document process activity using BPMN. After gaining an understanding of YNF's business processes, you will document current processes using business process modeling notation (BPMN). This involves documenting at least five current business processes. These diagrams will serve multiple purposes. YNF expects to use them ta train new employees. Plus, they will provide information about potential improvements to those processes by the efficient use of information technology. Additionally, they will also highlight areas where YNF is exposed to unnecessary risks in his business. 2. Prepare an integrated data model using UML class diagrams and the REA framework. This data mode! will serve as the blueprint for YNF's prototype database that you will also develop. Also, prepare a corresponding table listing for the prototype database. Identify where different fields/attribute names prowide the same information and develop recommendations far changes in field names. i Balance sheet summary $1,069,715.8 YNF Totals 1 $1,133,144.06 Assets $1,069,715.81 $0.00 $1,069,715.81 liabilities $2,202,259.87 $1,085,150.00 $154,939.62 $306,505.94 Bb ACCOUNTING INFORMATION SYSTEMS, 30 $154,939.62 $914,776.19 $758,644.08 $567,000.00 Richardson, Chang. and Smith $2,047,920.25 $192,500.00 $0.00 $5,168.00 $149,771.62 current ratio ros roe asset turnover debt to assets debt to equity $154,935.62 6.90 20.18% 21.68% a6 0.07 0.08 $154,939.62 14 using ending assets using ending equity ACCOUNTING INFORMATION SYSTEMS, 30 Richardson, Chang. and Smith ACCOUNTING INFORMATION SYSTEMS, 30 Richardson, Chang. and Smith TBEPYVR Darran Sample Project Assignment Description 34 16 ACCOUNTING INFORMATION SYSTEMS, 30 Richarditan, Chang. and Smith Basic Steps in the Z Cheddr Project The basic project involves analysis of Z Cheddr data, preperation of UML class diagrams and BPMN diagrams, setting up an Access database, and preparing queries. There are suggested extensions to the basic project. 7. 5. 10. 11. 12. Prepare UML Class Diagram. Students use the Z Cheddr employee interviews to develop 2 UML class diagram for Z Cheddr. Then, they can use the Excel dats to refine their diagrams. Prepare business process models (using BPMN). Students also use the Z Cheddr employes interviews to develop BPMN diagrams. The number of disgrams is optional, but it is suggested that you require three to five diagrams. Note that students struggle with where processes start and end. The instructor diagrams are generally based on complete handling of one transaction, .g., from placing an order to making ‘the payment for that order. However, one diagram for retail sales deviates from that by depicting 2 complete business day at the retail stores. Correct file structures (normalize). The worksheets are generally close to the final file structures. The students should find and correct data redundancies, such as supplier nsme in the “purchases admin” worksheet along with the supplier# Also, they should note redundancies in the “sales” table and split that table into “ssles” event information and “sales items” information, the linking table between “ssles" snd “inventory.” Transfer data from an EXCEL spreadsheet ta ACCESS. Establish appropriate relationships among the tables. Several tables have multiple links to joined tables, such as purchases inventory has both an ordering employee # and a receiving employee #. Thus, “purchases inventory” would be joined twice to “employees.” In that case, Access creates an “employees_L” ghost table to implement the join. See the instructor database Prepare queries to produce financial statements. Examples are provided in the instructor database. NOTE: There is 2 comprehensive (CONNECT assignment that walks the students through the queries necessary to create the income statement and balance sheet forZ ‘Chaddr. The use of that assignment is highly recommended 3. Income statement by store and overall b. Bslance sheet ¢. Other queries (instructor option) Accounts receivable detail Accounts payable detail Inventory average cost (average cost is provided but students can also calculate average cast to verify) Inventory item profitability Inventory item book versus count QOH with variances by inventory item and store <2 Ba. ACCOUNTING INFORMATION SYSTEMS, 30 Richarditan, Chang. and Smith ‘Completed UML diagram. See below Guidance on BPMN diagrams. See below. Access database with relationships and queries. See separate Access databases, one complete and ane for quantity-an-hand calculations Z Cheddr’s income statement information. See separate EXCEL document 10. Z Chaddr's balance sheet information. See separate EXCEL document. 11. Verignces from book value of inventory for both inventory items and stores. See separate EXCEL document. 12. Semple COSO principles analysis. See separate document. 6 z Ef a tion of data in EXCEL spreadsheet and appropriate treatment in Access 18. Admin suppliers. This table lists the administrative suppliers, such as the utility companies, phone companies, etc. The primary key is suppliers. 19. Cash. Shows the balance in each cash accaunt at the beginning of the quarter. Students need to find the ending cash balances by adding cash receipts and subtracting cash disbursements to fisher, cash disbursement to miscellaneous vendors, and net pay to employees. Note that Mr. Z moves money between accounts and that information is not provided, so the students only need to work with the totals. The primary key is account 20. Cash receipts. This table lists the cash receipts from customers. For retail stores, the customer information is nat recorded. The primary key is cash receipt CD admin purchases. This table lists the cash disbursements for the general and administrative expense items listed in the purchases admin table. Z Cheddr pays these bills at the end of each month. The primary key is check#. 22. CD invent purchases. This table lists the cash disbursements to Z's cheese and wine suppliers. Again, payments are madeon or nearthe end af the month. The primary key check# 23. Customers. This table lists the wholesale customers (small retailers around Oregon that buy fram Z Cheddr) and internet customers that place online orders. The primary key is customers. 24. Employees. This table lists the emplayees, including the stores they are assigned to, their jab category, and planned work schedule. The primary key is employest. Inventory. This table shows information for the various types of cheese and wine that Z Cheddr sells. The retail price is the selling price per pound of cheese or bottle of wine. ZCheddr values inventory at average cost. The beginning cast is the average cost from the previous quarter and it should be used to value the beginning inventory. All other inventory transactions are valued at average cost except purchases are at actual cost. For the internet sales, Z Cheddr sells packages that combine one or two bottles of wine and half or full pounds of cheeses. There is no separate physical inventory for the packages, since they are assembled after the order. The primary key is invent# 26. Jo categories. This table lists the various jobs in Z Chaddr, the cunt of employes in each category, and the starting pay for employees in that category. The primary key is job category. 2 2 ta 19 ia 7. 28. 29. 30 31 32 33. 24 35. 36. 37. 38 ACCOUNTING INFORMATION SYSTEMS, 30 Richardson, Chang. and Smith Payroll. This table shows the gross pay, withholding, and net pay information for each employee and month. Note that the payroll expense includes the gross pay plus the employer FICA and MC (Medicare) contributions. Mr. Z has not yet paid the withholding amounts, so those are liabilities at the end of the quarter. The primary key is check# Physical inventory counts. This table lists the physical counts of inventory at each store at the beginning and end of the quarter. Since there is a many-to-many relationship between stores and inventory, this is the linking table connecting them. The primary key is the combination of stores and inventé. Promotions. This table lists the items pulled from inventory and given away during wine and cheese tastings. Z Cheddr takes these amounts ‘out of inventory (COGS) and counts then as general and administrative expenses Purchases admin. This table lists the administrative purchases, such as utilities, rent, taxes, etc. The primary key is purchase#. Purchases inventory. This table lists the purchases of wine and cheese. Although it is possible for Z Cheddr to purchase several inventory items with a single purchased, in practice they do not. The item amount is the total paid for that quantity (not the cost per item). Ta make it more difficult for students to determine the accounts payable, the chack# foreign key is omitted. The primary key is purchase order#. Seles. This table lists the retail store, wholesale, and internet sales. Each type collects somewhat different information. Z Cheddr applies one orders to all the retsil stores ssles each day. Since this table lists the quantity of esch inventory item, it contsins substantial redundsnt dats. it should be split into a sales table (with order#, order date, sale date, customer, order employeed, pack employees, vang, sale type, cash receipt#, and shipping per order} and 2 sales items table (with order®, inventory#, quantity, sale price, and amount). Students need to take care importing the split tables into Access, since the first rows of data for order date and shipping per order are blank and Access will assume that thay are text. The primary kay of the split sales table is orders. The primary key of the split sales items table is order# plus invent Stores. This table lists the 11 stores, ten of which are rented. The warehouse is owned by Z Cheddr. The table includes either monthly rent or ‘original cost and depreciation information. The primary key is store# Timerards. This table lists the summary information from the monthly timecards. Note that timecards must be linked twice ta employees when setting relationships (and on the UML diagram) once for the employee that works and again for the store manager that scheduled the work. The primary key is timecard# Transfers. This table lists the quantities transferred from the warehouse to the retail stores. The transfers are done by Z Cheddr vans, The ‘quantity transferred is the amount to be subtracted from the warehouse inventory and added to the retail store inventory. This table would be linked twice ta both employzes and stores. The primary key is transfer control#. Vans. This table lists the vans, their original cost, and information necessary to calculate quarterly depreciation expense and accumulated depreciation for the quarter. All the vans are assigned to the warehouse. Z Cheddr takes a full year’s depreciation in the year acquired. Wing and cheese suppliers. This table lists the suppliers. The primary key is supplier#. Work schedules. This table lists the four different work schedules for employees. The primary key is schedule. Note that students could combine the “purchases” tables, the “suppliers” tables, and the “cash disbursements” tables. That simplifies the table structure, although it complicates their subsequent queries. In general, it is recommended that students keep the tables separate at least for part of the project. 20