our supervisor met with Mr. Z 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 Z's business processes, you will document current processes using business process modeling notation (BPMN). This involves documenting at leastfivecurrent business processes. These diagrams will serve multiple purposes. Mr. Z expects to use them to 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 Mr. Z is exposed to unnecessary risks in his business.
2. integrated data model using UML class diagrams and the REA framework. This data model will serve as the blueprint for Z's prototype database that you will also develop.
3. prototype database for Mr. Z. He has agreed to provide financial data from his business for the 1^stquarter 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.
4. Prepare financial reports using your database. You will prepare queries as necessary to provide the following reports and management information.
   1. Z Cheddr income statement for the 1^stRemember that Z uses accrual accounting.
   2. Z Cheddr balance sheet as of the end of the 1^st
   3. Income statements for each Z Cheddr store for the 1^st
   4. Analysis of product sales and profitability for the 1^stDetermine product turnover and profitability for each inventory item.
5. Evaluate the financial performance of Z Cheddr for the 1^stUse your Access queries but also prepare formal financial statements and appropriate ratios to evaluate performance against the industry and past performance.
6. Assess the business case for use of information technology in Z's business (see chapters 13 and 14 in the text). Mr. Z wants to move to a cloud-based accounting system, such as Netsuite. He wants to upload transactions daily from new smart cash registers in each store.

Report to Mr. Z

Each group will submit all the deliverables in one report to Mr. Z at the end of the project. You will also submit your prototype Access database with all the queries along with that report counting any appendices or attachments. The appendices or attachments should include financial statements, data model and activity diagrams, and other information necessary to support your analyses and conclusions. The content of the report should be clear to an accounting novice such as Mr. Z. Most of the content should be presented using tables, charts, and bullet points. In all cases, you should avoid long paragraphs. Specific content of the report should include:

• Financial performance evaluation as outlined in 4 and 5 above.
• Business case for technology as outlined in 6 above.

Other materials and guides:

• Guide to Importing EXCEL data into AccessDownload Guide to Importing EXCEL data into Access
• Modeling Software Links ListDownload Modeling Software Links List
• z data Fall 2018.xlsxDownload z data Fall 2018.xlsx
• z historical income.xlsxDownload z historical income.xlsx

Appendix

Overview

The following interviews were conducted prior to the start of the project to explore specific aspects ofZ Cheddrbusiness 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 isnotsuggested that these interviews provide complete information about all ofZ Cheddrbusiness 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 startedZ Cheddrjust over 10 years ago. His business quickly grew from one store to 10 stores plus an internet presence. Recently, he also 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. 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 purchasing both 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 theZ CheddrPromotions form, he carefully records the amount of cheese and wine pulled from inventory for the promotion. After the promotion, he files the promotion form until he can send it to Chad, the Z Cheddraccountant, 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, prepares the deposit information, and records the daily transactions in a spreadsheet. She emails the spreadsheet to Chad, the Z Cheddr accountant. She also prepares the cash drawers for the next morning 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.S 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 does is call them to place an order.

Each day, Bob reviews theZ Cheddrinventory records to determine what to order. He also considers recent sales trends and potential seasonal demand for particular products. Often, he will check the physical warehouse inventory and call the 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 theZ Cheddrcentral 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 by Judy in shipping and receiving, 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 for purchases during the month. Sometimes, Chad combines payments if there is more than one shipment from the same cheese maker during the month.

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 allowsZ Cheddrto provide high quality wines for reasonable prices to our customers.

Summary of interview 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 sureZ Cheddrpays 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 Cheddrconducts physical inventory counts each quarter, and Chad uses those counts to update or correct the electronic inventory records. Chad uses average inventory cost (average cost in the inventory worksheet) to value transfers, promotions, and ending inventory. The beginning inventory is valued at the average cost for the previous quarter (beginning cost in the inventory worksheet).

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 Cheddrretail 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 after the end of 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. Z 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 8% of the sale amounts, depending on the credit card the customer uses. It then electronically deposits the rest of the payment in theZ Cheddraccount and sends an email to Chad to confirm the deposit. Of course, Chad uses the Z Cheddr checkbook (and any transfer vouchers for transfers to the payroll account) to confirm payments.

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 receipts for the incoming shipments from our cheese and wine suppliers in Oregon. She checks each shipment for damage and returns damaged items to the suppliers. She updates the 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. She also updates the inventory records. Then, she sends the list of internet orders to Chad, so he can record sales information. UPS handles all the outgoing shipments for our internet sales. UPS bills Z Cheddr 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 theZ Cheddrvans 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.

Z Cheddralso sells and delivers to small groceries and wine shops around the state. Judy handles those wholesale sales. She takes orders from the wholesale customers over the phone, and then she packs the vans for delivery to those customers. Judy includes an 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, so Chad can record wholesale sales.

Summary of Interview with Woody Guthrie, Clerk and Accounting Assistant to Chad Mitchell

The following interview was recently conducted to provide more information on aspects ofZ Cheddrfinancial reporting processes. Woody usually assembles the end-of-quarter data necessary to prepare financial statements for each store and for Z Cheddr overall. He understands that you will be performing this task, but he wanted to provide you some background on the process that he has used in the past.

Income statement (IS)

Sales

1. First, Woody takes the sales item transaction data (for retail, wholesale, and internet sales) and ensures that all transactions occur within the quarter. He then checks the Item Amount extension to make sure that sales amounts are calculated correctly. The result is the Sales Detail for Quarter.
2. Using the Sales Detail for Quarter, he then summarizes sales and shipping costs for each store. He calculates total revenue by adding sales and shipping costs. First, however, he needs to convert blank cells (null values) for shipping costs to zeroes (Use the combination of the Val and NZ functions to change null values to zeroes that can be formatted as currency, e.g., Val(NZ([Shipping Cost per Order], 0)).), since the only store with shipping costs is the warehouse. He calls this result the IS Stores Revenue.

Cost of Goods Sold

1. Calculating cost of goods is the hardest part of the income statement. In all cases, Woody limits the transactions to the quarter.
   1. Beginning inventory, valued using the physical inventory count on Dec 31 times the beginning cost in the Inventory table.
   2. Purchases, valued at actual cost.
   3. Transfers Out, valued by multiplying the quantity transferred times the average cost figure in the Inventory table.
   4. Transfers In, valued by multiplying the quantity transferred times the average cost figure in the Inventory table.
   5. Promotions, valued by multiplying the promotion quantity times the average cost figure in the Inventory table.
   6. Ending inventory, valued using the physical inventory count on Dec 31 times the average cost in the Inventory table.
2. Then, he summarizes the values by store. Join the Stores table with each query identified above. Change the JOIN properties so that the resulting query shows all Stores. He changes null values (blanks) to zeroes as described in footnote 1. He makes sure that all summary-by-store queries show one row for each of the eleven stores.
3. He calculates goods available for sale at each store as beginning inventory plus purchases minus transfers out plus transfers in minus promotions.
4. Then, he subtracts ending inventory to compute cost of goods sold for each store and calls this query IS Stores COGS.

Pay Expense

1. Woody first prepares a query to limit the payroll transactions to the quarter and calculates the pay expense for each transaction. Pay expense includes gross pay plus employer contributions to FICA and MC. He calls this result Pay Expense Detail for Quarter.
2. Then, he summarizes the Pay Expense Detail for each store. He calls this result IS Stores Pay Expense.

General and administrative expenses

1. Woody first limits the miscellaneous expenses (rent, electricity, phones, and credit card bills for example) in the Purchases Admin table to those incurred during the quarter. These purchases are recorded when they are incurred, so there are no prepaid amounts. Woody calls this result Admin Expense Detail.
2. He then summarizes the administrative expenses by store. He calls this result IS Stores Admin Expense.

Promotion Expenses

1. Woody uses the information on promotion expenses subtracted from cost of goods sold as the promotion expense amount.

Credit Card Fees

1. Finally, Woody calculate the expense related to credit card fees. This expense arises from the charges assessed by the credit card processor and appears as the difference between sales plus shipping amounts and the corresponding cash receipts.
2. Woody calculates credit card fees related to each deposit and that amount is shown in the Cash Receipts table. So, for the end of quarter processing, he first limits cash receipts to the first quarter.
3. Next, he summarizes cash receipts by store. He calls this result IS Store Credit Card Expense.

Van and Store Depreciation

1. Woody divides the annual depreciation figures for each van by 4 to calculate the quarterly depreciation expense for the vans. He does the same for the owned store.
2. Then, he joins the Store table and the depreciation calculation. He changes the JOIN properties to show all stores and converts null values to zeroes for all stores. He names these two queries IS Stores Van Depreciation and IS Stores Store Depreciation.

Overall Income Statement

1. Once Woody completes the income statement for the stores, he then summarizes the amounts in that query to show the overall income statement for Z Cheddr. He calls this IS Z Cheddr Overall.

Balance Sheet (BS)

Woody says that the balance sheet is easier to prepare, since he doesn't need to calculate balance sheets for individual stores. He just prepares one overall balance sheet.

Assets.

• Cash. To compute the ending cash balance, Woody first summarizes the cash balance beginning, the cash receipts, and the cash disbursements (cash disbursements for inventory purchases, cash disbursements for miscellaneous expenses, and payroll) in separate queries. Each query should have one row showing only the total. Then, the ending cash balance is calculated as the beginning cash balance plus cash receipts less cash disbursements.
• Accounts Receivable. Woody says that AR amounts only arise when the wholesale customers' payments are delayed. He first summarizes wholesale sales. He then summarizes cash receipts for wholesale sales. Finally, he creates a query to calculate AR as the difference between wholesale sales and wholesale cash receipts.
• Inventory. Woody uses the physical count of inventory at the end of quarter as the basis for the ending inventory value. Inventory is valued at average cost as shown in the Inventory table.
• Stores and Vans. Woody uses the book values of the owned store and the vans. He computes accumulated depreciation including the depreciation for the current quarter. Z Cheddr records a full year's depreciation in the year of acquisition, so accumulated depreciation is the number of years since acquisition plus one quarter times the annual depreciation amount.

Liabilities.

• Trade accounts payable. Woody calculates trade accounts payable by first summarizing purchases of inventory in the first quarter, then summarizing cash disbursements for inventory in the first quarter, and finally subtracting cash disbursements from purchases.
• Administrative accounts payable. Woody uses the same process to calculate administrative accounts payable.
• Taxes and withholdings payable. Finally, he calculates payroll taxes payable by summarizing employer and employee FICA and MC, federal withholding, and state withholding. He sums those six items to determine taxes payable.
• Z Cheddr has no mortgages or notes outstanding, so Woody does not need to calculate long-term liabilities.

Z Equity. Mr. Z owns all the stock and he originally contributed $500,000 when he started the business. He hasn't taken any distributions. Woody repots Stockholders' Equity value as contributed capital plus retained earnings and calculates retained earnings as assets minus liabilities minus contributed capital.

Description of data in EXCEL spreadsheet and appropriate treatment in Access

1. Admin suppliers. This table lists the administrative suppliers, such as the utility companies, phone companies, etc. The primary key is supplier#.
2. 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 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#.
3. Cash receipts. This table lists the cash receipts from customers. For retail stores, the customer information is not recorded. The primary key is cash receipt#.
4. 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#.
5. CD invent purchases. This table lists the cash disbursements to Z's cheese and wine suppliers. Again, payments are made on or near the end of the month. The primary key check#.
6. This table lists the wholesale customers (small retailers around Oregon that buy from Z Cheddr) and internet customers that place online orders. The primary key is customer#.
7. This table lists the employees, including the stores they are assigned to, their job category, and planned work schedule. The primary key is employee#.
8. 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. Z Cheddr values inventory at average cost. The beginning cost 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#.
9. Job categories. This table lists the various jobs in Z Cheddr, the count of employees in each category, and the starting pay for employees in that category. The primary key is job category.
10. 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#.
11. 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 store# and invent#.
12. 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.
13. Purchases admin. This table lists the administrative purchases, such as utilities, rent, taxes, etc. The primary key is purchase#.
14. 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 purchase#, in practice they do not. The item amount is the total paid for that quantity (not the cost per item). To make it more difficult for students to determine the accounts payable, the check# foreign key is omitted. The primary key is purchase order#.
15. This table lists the retail store, wholesale, and internet sales. Each type collects somewhat different information. Z Cheddr applies one order# to all the retail stores sales each day. Since this table lists the quantity of each inventory item, it contains substantial redundant data. It should be split into a sales table (with order#, order date, sale date, customer#, order employee#, pack employee#, van#, sale type, cash receipt#, and shipping per order) and a 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 they are text. The primary key of the split sales table is order#. The primary key of the split sales items table is order# plus invent#.
16. 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#.
17. 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#.
18. 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 to both employees and stores. The primary key is transfer control#.
19. 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.
20. Wine and cheese suppliers. This table lists the suppliers. The primary key is supplier#.
21. Work schedules. This table lists the four different work schedules for employees. The primary key is schedule.

PreviousNext