Homework 1

1

I. Title: Winery Case Study Using a REA Database Design

II. Introduction: Congratulations! You are fast becoming a raising star as a Staff Auditor at Levin & Associates, LLP. Your supervisor, Mr. Lee, arrives at your office early Monday morning and states he was so impressed with your last project for the Lending Club that he wants to see how well you can work with a team of other high achievers within the company.

Further, Mr. Lee says, “I will be assigning you to a team shortly, but first, I’d like to share some additional information about the new client and the project. The new client is a small wine producer in the Finger Lakes Region of New York State that must keep track of its business processes and operate necessary accounting functions. The Finger Lakes Wine Company (FLWC) grows grapes, produces wine, sells wine tastes, runs a gift shop, and operates a restaurant. FLWC sells wine at retail prices through the gift shop and restaurant to customers who visit the winery. In addition, FLWC sells wine at wholesale prices to distributors, liquor stores, night clubs, and restaurants in the Rochester metropolitan area.” Mr. Lee continues, “the Managing Partner, Corky Winepoper, designed the database with no knowledge of accounting or information systems. As a result, FLWC management is trying to operate the company with a poorly designed database. FLWC is experiencing many problems such as stock-outs and a physical inventory count that does not reconcile to the inventory balance in the accounting records. In addition, other business processes are also operating poorly. For example, payroll checks are frequently incorrect, vendor invoices are paid late, production costs are not tracked, and management has no confidence in the reliability of the financial statements.”

Mr. Lee concludes by saying “the partners of FLWC would like to expand its market to Syracuse, Oswego, Buffalo, Niagara Falls, and Toronto. However, Corky is too busy tasting wine to fix the database problems. Accordingly, your IT audit team has been assigned to re-design the database that can support the expansion with an enterprise information system. Your team’s design will be presented to Corky and FLWC management.”

Mr. Lee leaves your office and you excitedly dive into the nitty gritty which is provided below.

III. Steps to Completion

1. Review Levin & Associates, LLP rules on operating as a team in Appendix

A.

2. Review FLWC’s organization structure and business processes in Appendix

B.

2

3. Review FLWC’s Operational and Financial Information in Appendix C.

4. Review additional information about FLWC in Appendix D.

5. Review detailed, specific information about the project deliverables in

Appendix E.

6. Now introduce yourself to your team members and get started on this

exciting endeavor.

IV. Deliverables

1. Case Report: The written case report must be completed using MS Word. The report should include a cover page, table of contents, executive summary, answers to all required questions, references, and a team activity log. Use 12-point font, double spacing, and one-inch margins (top, bottom and sides). [Should we recommend APA?]

2. PowerPoint Presentation: Your PowerPoint file should contain between 8 to 16 slides written in your own words. It should include the 2 DFDs (context and 0 level), the Relationships Diagram (from Access), a flow chart, and the ER (REA) Diagram.

3. Access Database: The database should contain 5 to 10 relations, a Relationships diagram, 5 to 10 reports, 2 to 4 queries and the input forms. Some of the reports and queries should pull data from more than one table.

V. Hints and Tips

▪ Be active in your team and utilize each other’s strengths. ▪ Read the entire project and review the grading rubric before beginning the

project to fully understand the requirements. ▪ This is a team graded project. Each team member will be receiving the same

grade so make sure you do your part and encourage each other to make this project a success.

▪ Ask questions about the project requirements as needed. ▪ Pick one team member to submit the deliverables to their Assignment folder

on or before the due date. All team members will receive the same grade. ▪ Review the late policy in your syllabus; it will be enforced.

VI. Rubric Before beginning this project, review the rubric to learn how the project will be graded. Rubrics can be found in Content/Course Resources/Projects & Rubrics/Project 2 Rubric Your deliverables will be assessed on the following competencies:

• Critical thinking

3

• Technology and tools

• Decision making

4

Appendix A – Levin & Associates, LLP Team Regulations

Your consulting team will operate under the following team rules: .

• All member names should be clearly listed on the first page of the project. • All team members will receive the same grade. • Team members should notify Mr. Lee immediately if a team member is not

participating. Free riders and uncooperative members will be penalized. • Teams may terminate a team member for non-participation after consulting with

Mr. Lee.

5

Appendix B – FLWC’s Organization Structure and Business Process

Table 1: SBU/Departments and Business Processes Each Intersection of a FLWC Process and Dept. is a Give/Get Economic Exchange

SBU: Strategic Business Unit, DM: Direct Materials, DL: Direct Labor, OH: Overhead, CRM: Customer Relationship Management, HRM: Human Resource Management

SBU or Department 🡪

Grape Farming

(G)

Wine Production & Bottling

(W)

Wine Sales &

Distribution (D)

Gift Shop & Tasting

(T)

Restaurant (R)

Administration (A)

Business Process:

Expenditure Cycle

1: Give cash get

DM & OH

3: Give cash, get DM & OH

5: Give cash, get,

marketing,

6: Give cash, get shipping and CRM

8: Give cash, get inventory

& OH

10: Give cash, get DM & OH

13: Give cash, get property.

plant & equipment

assets

Production Cycle

2: Give DM, DL & OH,

get grapes

4: Give DM, DL & OH, get wine

11: Give

DM, DL & OH, get meals

Revenue Cycle

7: Give

wine, get cash

9: Give wine &

gifts, get cash

12: Give wine &

meals, get cash

Human Resources and Payroll Cycle

14: Give cash for payroll, get

labor 15: Give cash and benefits,

get HRM services

Financing Cycle

16: Give cash,

get cash (owners & bankers)

17: Give

distributor financing, get

cash

6

Table 2: Brief Explanations of Business Processes

WIP: Work in Process FG: Finished Goods

No. Ref. Explanation

1 G Grape farming expenditure cycle: All operating expenses related to farming are purchased by the farming operation except labor and depreciation. Examples of farming operating expenses include water, gas, oil, farm equipment parts, picking baskets, fertilizers, insecticides, seeds, other supplies, etc.

2 G Grape farming production cycle: Grapes are planted, grown and harvested. All harvested grapes are immediately transferred to the wine production department. Farming does not maintain an inventory of harvested grapes because they spoil quickly. This department maintains an inventory of gas, oil, parts, other supplies, fertilizers, insecticides, seeds, etc.

3 W Wine production and bottling expenditure cycle: Wine is produced from grapes and other ingredients. The wine production department may buy additional grapes from other wine producers and farmers to satisfy customer demand. Items purchased include filters, bottles, barrels (oak and steel), yeast, cleaning supplies, etc.

4 W Wine production and bottling production cycle: Grapes are crushed and squeezed then the juice is filtered, blended, and fermented in vats. After the wine is bottled it is all transferred to sales and distribution. WIP wine that needs to be aged in barrels stays in the wine production department as FG inventory.

5 D Wine sales expenditure cycle: All operating expenses related to sales are purchased by this department except for labor and depreciation. Advertising, social media, and public relations are critical for this department. FLWC purchases print, Web, radio and TV ads.

6 D Wine distribution expenditure cycle: All operating expenses related to wine distribution and shipping are purchased by this department except for labor and depreciation. Customer relations such as feedback, returns and allowances are critical for this department.

7 D Wine sales and distribution revenue cycle: The sales and distribution department sells wine at wholesale prices to distributors. It also supplies the gift shop, tasting room, and restaurant with all of their wine through inside (internal) sales. This department maintains an extensive inventory of bottled wine.

8 T Gift shop and tasting expenditure cycle: Wine tasting and the gift shop are organized under one department. All operating expenses related to wine tasting and related gifts are purchased by this department except for labor and depreciation. The gift shop purchases and maintains a small inventory of items such as wine (internally purchased), cheese, meat, wine racks, glasses, cork screws, etc.

9 T Gift shop and wine tasting revenue cycle: This department sells wine, gifts and tastes of wine to retail customers. Credit cards, debit cards, and

7

cash are accepted as payments.

10 R Restaurant expenditure cycle: All operating expenses related to the restaurant are purchased by this department except for labor and depreciation. It also purchases items such as wine (internally purchased), food, utilities, and supplies.

11 R Restaurant production cycle: The restaurant maintains a small inventory of wine, food, and supplies. It also uses food inventory, supplies, and labor to produce meals. The chefs and cooks get their orders from waiters and produce meals for the restaurant and special catering events.

12 R Restaurant revenue cycle: The restaurant serves lunch and dinner. It internally purchases all of its wine from the sales and distribution department. The restaurant also hosts special events such as weddings, parties, outside barbeques, etc. The restaurant runs a catering service complete with tents, chairs, tables, portable bars, etc.`

13 A Administration expenditure cycle: All property, plant, and equipment are purchased, accounted for, and sold by the administration. Plant and equipment assets are depreciated by this department.

14 A Administration of the payroll function: All direct and indirect labor for all departments at the winery is expensed by the administration. This process receives time logs from various departments and distributes pay checks and pays all payroll taxes (federal and state).

15 A Administration of HRM services: All direct and indirect labor (personnel??) for all departments at the winery is interviewed and hired by the administration. This process manages all benefits such as health insurance, retirement, vacation, and sick days.

16 A Administration of owner and debt financing: All debt financing is acquired by the administration from bank loans (no bonds). There is no public equity financing (stock) but there are investments from and distributions to the owner and his partners. Should you say it is a private company?

17 A Administration of customer financing: The administration approves and manages the repayment for lines of credit to high volume wine distributors, liquor stores, night clubs, and restaurants that buy from FLWC.

8

Appendix C – FLWC’s Operational and Financial Information

The following estimates are based on industry data for the fundamentals of cool climate grape and wine production. This data should be used as background information to explain the size and scale of the FLWC.

Wine Production Process Explained

After harvest, the grapes are taken into wine production and prepared for the primary ferment. At this stage red wine making differs from white wine making. Red wine is made from the pulp (must) of red grapes and fermentation occurs together with the grape skins. This gives the wine its dark red color. To start primary fermentation yeast is added to the must for red wine making. White wine is made by fermenting juice which is made by pressing crushed grapes to extract a juice. Then the skins are removed and play no further role in white wine making. Yeast is also added to the juice for white wine making. Rosé wines are made from red grapes where the juice is allowed to stay in contact with the dark skins long enough to extract a pinkish color. Rosé wines can also be produced by blending red and white wines. White and rosé wines absorb little of the tannins contained in the grape skins. Primary fermentation takes between one and two weeks. During this time the yeast converts most of the sugars in the grape juice into ethanol (alcohol) and carbon dioxide. The carbon dioxide is evaporated into the atmosphere. White and rosé wines are then sent to bottling but red wines need more processing. After the primary fermentation of red grapes, the free run wine is pumped off into tanks and the skins are pressed to extract the remaining juice and wine. The press wine is blended with the free run wine to winemaker's taste. The wine is kept warm and the remaining sugars are converted into alcohol and carbon dioxide. The next process in the making of red wine is secondary fermentation. This process

9

decreases the acid in the wine and softens the taste of the wine. Red wine is sometimes transferred to oak barrels to mature for a period of weeks or months. This practice imparts oak aromas into the wine. The wine must be settled or clarified, and adjustments are made prior to final filtration and bottling. FLWC Farm Operation

Acres 100

Vines 100,000

Vines per acre 1,000

Tons of grapes per acre 3 to 4

Weight of one cluster of grapes 0.1 to 0.5 pounds

Tons of grapes harvested per year 320 approximately

Farming and Receiving Grapes

Gallons of wine per ton of grapes 120 to 180

Case of 12 wine bottles contain: 12(750 ml) 9 liters

Case of 12 wine bottles contain 2.378 gallons

Pounds of grapes per bottle 2.6

Bottles of wine per ton of grapes 750

Cases of wine per ton of grapes: 750/12= 63

Bottles of wine per one vine 2 to 3

Cases of Wine Produced: 63(320) 20,000 approximately

Grape clusters per bottle 12

Wine Production Information

Bottles per year: 20,000 * 12 = 240,000

Gallons of wine per year: 240,000/5= 48,000

FLWC has 4 Fermentation tanks 1,000 gallons each

1 Bottle of wine contains 750 ml

Bottles per gallon of wine 5

FLWC has 4 storage tanks 1,000 gallons each

Fermentation days for red 7

Fermentation days for white 21

Production accounting Job order costing

Bottles per case 12

Gallons per barrel 50

Fixed Expenses Restaurant, Farm and Wine Production

Insurance Depreciation

Property Taxes Loan Interest

Maintenance Cost of Equity

Utilities (phone and Internet) Management, legal, accounting

10

Variable Costs

Farming, Production and Tasting Restaurant

Grapes Food

Cooperage (wine barrels) Wine

Packaging Labor

Bottling Taxes and Fees

Taxes and Fees Labor

Labor Marketing

Marketing Utilities, Supplies and Misc.

Utilities (power and water), Supplies and Misc.

Inventory

Production and Sales Mix by Wine Type

Red Wine: Grape prices per ton

% of Production

Totals

Cabernet Sauvignon $1,100 30

Merlot 1,000 25

Syrah 1,200 20 75%

White Wine:

Chardonnay 800 15

Riesling $700 10 25%

Total

100%

Fixed Assets

Wine Production and Tasting Restaurant Farm

Receiving equipment (stemmer/crusher, membrane press, pump, lines, conveyor)

Dining room Tractors

Cellar equipment (tanks, tank mixer, transfer pump, transfer hose, barrel washer, filter, air compressor, pressure washer, lab equipment)

Kitchen equipment (stove, oven, microwave, sink, refrigerator, dishwasher, misc. equipment)

Barn

Material handling (pallet jack, hand cart, fork lift, rotator, tools)

Furniture and Fixtures (wood chairs, folding chairs, wood tables, folding tables, decorations, plates, silver ware, stemware, tents, serving trays, misc.)

Tools

Refrigeration and storage

Picking bins

Cooperage (barrels, racks, bungs)

Pickup truck

Tasting room (refrigerator, dishwasher, bar, chairs, decorations, stemware)

Plant and office (computers, furnishings, land, building)

11

Employees

Wine Production Restaurant Farm

General manager Servers Pickers (seasonal)

Winemaker Head Chef Farmers

Assistant winemaker Sous Chef

Cellar-man Bar Tender

Warehouse Restaurant Manager

Public relations and sales Line Cook

Customer service Bus Boy/Girl

Wine Sales manager Janitor

Office manager

Clerical

FLWC Sales Budget

Farming, Production

and Tasting

Restaurant and Special

Events

Tasting Units, Cases 5,200 14,000 Rest. Units in Persons

Wholesale Units, Cases 14,800 4,000 Event Units in Persons

Total Units 20,000 18,000

Tasting Case Price $ 215

$ 40

Price Per Person, Rest.

Wholesale Case Price $ 143

$ 30

Price Per Person, Events

Tasting Room Sales 1,118,000 560,000 Restaurant Sales

Wholesale Revenue 2,116,400 120,000 Event Sales

12

FLWC

Contribution Margin Income Statement FYE December 31, 2018

Farming, Production, Tasting and Special

Restaurant, Events and

Total Sales $ 3,234,400 $ 680,000 $ 3,914,400

Variable Costs -2,100,000 -225,000 -2,325,000

Contribution Margin 1,134,400 455,000 1,589,400

Fixed Costs -500,000 -272,000 -772,000

Operating Income 634,400 183,000 817,400

Taxes -200,000 -60,000 -260,000

Net Income $ 434,400 $ 123,000 $ 557,400

13

FLWC Balance Sheet

As of December 31, 2018 Grape Farming, Wine Production, Tasting and Restaurant

Assets Current Cash $ 200,000

Accounts Receivable 250,000 Supplies` 25,000 Inventory 100,000 Investments 200,000

Total Current 775,000

Non-current Equipment 1,000,000

Plant and Buildings 1,500,000 Land 200,000

Total Non-current 2,700,000

Total Assets $ 3,475,000

Liabilities Current Accounts payable $ 100,000

Accrued Interest Payable 15,000 Unearned Revenue 50,000

Total Current 165,000

Non-current Mortgage 900,000

Notes Payable 1,000,000

Total Non-current 1,900,000

Total Liabilities 2,065,000

Equity Paid in Capital $ 600,000

Retained Earnings 810,000

Total Equity 1,410,000

Total Liabilities and Equity $ 3,475,000

14

Appendix D – Additional Information Mission Statement: The Finger Lakes Wine Company (FLWC) strives to be a leading winery and restaurant company in the Finger Lakes region of New York State by staying focused on the requisite ethical practices to make high-quality wine, serve fresh and delicious food, while maintaining the highest customer satisfaction rating in the Finger Lakes region. Major Strategic Goals:

• Attract more investors • Increase sales growth 10% per year • Increase customer satisfaction • Increase income growth 10% per year • Attract and retain quality employees from diverse backgrounds • Grow the customer base by X% • Expand into major nearby metropolitan areas such as Syracuse, Oswego,

Buffalo, Niagara Falls, Toronto, etc. Partners and Company Address:

• Corky Winepoper • Mick Jagger • Eric Clapton • Jimmy Hendrix • Stevie Nicks • Paul Rodgers • 1234 Route 14., Geneva, NY, 14456, USA

Banks:

• Chase Bank • M&T Bank • First Niagara • LNB

Operational Bookkeeping Information

Ledgers Number of Accounts Average Balance

Accounts Receivable Subsidiary 500 $500

Accounts Payable Subsidiary 100 1,000

Inventory Subsidiary 10 10,000

Supplies Subsidiary 18 1,390

Equipment Subsidiary 25 40,000

Expense Subsidiary 200 60,000

Journals Number of Transactions Average Trans.

Cash Disbursements 10,000 $300

Cash Receipts 12,000 400

Sales 25,000 150

Purchases 1,000 500

15

Payroll Journal 500 2,000

16

Major Suppliers: • John Deere Tractors • Tractor Farm Supply • Smith Winery Supplies • Home Depot • Dell Computers • Jones Bar and Restaurant Supplies • Trib Fertilizers • Adobe Software • Home Depot Hardware

Major Customers:

• Bully Hill Vineyards • Lisa’s Liquor Barn • TGI Fridays • Wegmans Food Markets • Market View Liquor • Tops Markets • Ruby Tuesdays • PF Chang’s • Dinosaur Barbeque • Hedges Restaurant • Constellation Brands • Jack’s Goodtime Traven • 650 Black Oven Restaurant • Skipper’s Landing Restaurant • Dockers’ Restaurant • Marlin’s Restaurant • Sodus Bay Yacht Club • Sodus Height Golf Club

17

Appendix E – Report Requirements

Cover Page: List the members of the consulting team and the business processes related to your database designed (1 page). Table of Contents: Include the sections and page numbers of the report (1 page). Executive Summary: Summarize your report and be sure to follow the guidelines listed on the link from the AIS site (1 to 2 pages). 1. Introduction and Background Information: Explain the basic purpose of a database (DB) as well as the costs and benefits to the management of FLWC. Also explain the difference between a well-designed database and a poorly designed database. What are the advantages of a well-designed relational DB (2 to 4 pages)? 2. Literature Review of Security and Internal Controls: Explain the basic purpose of security and internal controls (IC) as well as the costs and benefits to the management of FLWC. Also explain the differences between preventive, detective, and corrective controls. Compare and contrast general controls with application controls. Also discuss the ERM framework (2 to 4 pages). 3. Project Profit Plan: Before you begin, plan out this consulting job (2-pages). Estimate the revenues, costs, and profit on this job. 4. DB Domain Narrative: Assume you are building part of a DBMS (database management system) for FLWC. Select one business. Explain in an essay the information that should be contained in the database related to the process you have chosen. Identify source documents, specialized journals, ledger accounts (including subsidiary ledgers and control accounts), and human operations that the accounting system will use for your part of the database. Also identify the related processes and relations that will interface with your part of the database. Feel free to make reasonable strategic and tactical assumptions (clearly state all assumptions) about the business to support your recommended database (DB). This will be a narrative that explains your section of the database qualitatively (1 - 2 pages). 5. Reports and Decisions: You will generate 5 to 10 queries or reports from your database. Explain how your DBMS and related management reports will support administrative functions and decision making at the FLWC. Describe 10 to 20 specific types of decisions in MS Word that could be supported by the reports. Clearly label each decision. Link the decisions to the reports in the table format shown below to show that the reports specifically support the decisions (1 to 2 pages). Queries and Reports and Decisions Table

Report Name Short Decision or Administrative Function Description

18

6. Data Flow Diagrams (DFD): Construct 2 data flow diagrams (DFD) of the system using MS PowerPoint. The first will be a context level DFD and the second will be a 0 level DFD. Data stores in the 0-DFD must be consistent with the tables in the database and the sources and destinations should also be consistent with the agents in the DB. Cut and paste these diagrams into the Word report (2 to 4 pages). 7. REA Entity Relationship Diagram: Identify the entities (resources, events and agents), relationships, and cardinalities that will be included in your DB. Draw an ER (entity-relationship also called an REA diagram) diagram using MS PowerPoint. Your design should contain at least 5 relations. Cut and paste these diagrams into the Word report (1 to 2 pages). 8. Document Flow Chart: Draw the document flow chart for your applicable business process (1 to 2 pages). 9. Data Dictionary: Build a data dictionary (DD) in MS Word to define the data that will be contained in the relations. Your DD should contain fields for relation name, attribute name, attribute type (numeric, alpha, etc.), attribute field length, attribute required or optional, primary key or foreign key, related input form, internal control, and other fields that you feel are important to this DB. As one of your fields note in the DD what reports or queries (if any) each data attribute will appear in (1 to 2 pages). 10. Risk Assessment and Internal Controls Narrative: Identify and discuss the various material risks that are associated with the specific business process you are working on for FLWC. What are the risks of fraud, user error, etc. Explain why these risks are significant in terms of threat, exposure, and likelihood. Feel free to make strategic and operational assumptions (clearly state all assumptions) about the business to support your risk assessment. Identify the internal controls that will be associated with the business process related your part of the database. Explain in a short essay the specific internal controls (IC) that should be implemented in the business process to mitigate the risks you identified. This will be a narrative that explains your section of the ICs qualitatively. Then list at least 6 controls in the following table format (2 to 3 pages).

Threats and Controls Table (example)

Activity Threat Controls

Shipping 10. Picking the wrong items or the wrong quantity

10.1 Bar-code and RFID technology

11. Theft of inventory 10. 2 Reconciliation of picking lists to

sale order details 11.1 Restriction of physical access to inventory

11. Input Controls Matrix (ICM): Complete an ICM for your team’s area of the system. List 10 to 15 input controls and at least 10 field names (from the input forms you will create in the database section of this case). Make sure your ICM is consistent with your risk assessment and internal controls narrative. See example below (1 to 2 pages).

19

ICM Example

Field Attributes> Sale Number

Date of Sale

Employee Number

Internal Controls:

Auto Number X

Input mask

X

Lookup Table

X

Cascade X

Required X X X

12. Narrative of Employee Roles and an Access Control Matrix (ACM): Assume the department has 5 to 10 employees. List the employee positions you think would be needed to run this process and describe their job responsibilities. Describe the various job functions that the employees would perform in your domain and develop an access control matrix. Strive for the best separation of duties that you can achieve in this small department. Then develop an ACM with permissions for access to all the database tables, reports and queries. Explain how and why you choose the various levels of permissions. See example below (2 to 4 pages). ACM Example

Access Tables > Customer Table Inventory Table XYZ Table

Position:

Accounts receivable clerk 1, 2 1, 2

Manager 1, 2, 3, 4 1, 2, 3, 4 5

Etc.

Access permission controls will be based on the following list:

0 = No Access 1 = View Data 2 = View and Change Data 3 = View, Change and Delete Data 4 = View, Change and Add New Data 5 = View, Change, Delete and Add New Data

14. References: List the sources you used to complete the literature review section of this report. Adhere to APA format and list 10 to 15 sources. Do not cite the same Web page more than once. Do not cite the course textbook, class notes, or lectures. See the AIS Web site for a link to APA format directions. Also see examples below (1 to 2 pages).

• Journal Article: Author, A. A., Author, B. B., & Author, C. C. (Year). Title of article. Title of Periodical, volume number (issue number), pages.

• Book: Author, A. A. (Year of publication). Title of work. Publisher Name and Location (city and state).

• Non-periodical Web Document: Author, A. A., & Author, B. B. (Date of publication). Title of document. Retrieved from http://Web address

20

• Newspaper Article: Author, A. A. (Year, Month Day). Title of article. Title of Newspaper. Retrieved from http://www.someaddress.com/full/url/

Case Study

Team Member Names Team Member’s Responsibilities

Team Member Time on Case

1.

2.

3.

4.

5.

Meeting Dates, MM/DD/YY

Meeting Type and Time Spent on Meeting: P = Physical, O = Online, T = Phone, E = E-mail

Team Members Participating in Meeting

Time in Meeting

Case Report Rules

• The answers to the case must be completed using MS Word. • The case answers must be organized and professional in appearance. • The report file should have a consistent look and feel. • This case study will be graded on a 100-point scale. • Late assignments will be subjected to substantial penalties. • Disorganized or unprofessional assignment will be subjected to substantial

penalties. • The case report must start out with a cover page (with name of client, business

process and team member names), table of contents and executive summary. The body of the report will be the answers to the questions listed above. Finally, the report will end with references and the team activity log.

• Clearly label your case answers by bolded and centered section titles (example: 1. Introduction).

• Your report must be written at the college level. MS Access Database Requirements 1. Data Tables and Example Data: Identify the database tables (relations) and create the tables in MS Access. Tables should contain attributes (columns) and primary keys. In addition, make up some example data and enter it into the relations. 2. Access Relationships: Identify the relationships (using primary and foreign keys) between the relations using the Relationships function in MS Access. Referential integrity must be enforced for every relationship. 3. Reports and Queries: Construct 5 to 10 management reports in MS Access that users could generate from the DB. Some reports must integrate data from two or more tables in the database. As part of the requirement, create 2 to 4 queries that perform relevant and meaningful calculations for the purpose of data analysis and decision making. Make sure some of your queries pull data from more than one table.

21

4. Implementing Internal Controls in the Database: Implement several of the internal controls you learned while completing the homework into the Access Database. Implement the following controls at the table level:

• Cascading relationships • Validation rules • Lookup tables • Referential integrity • Input masks • Field size • Data format • Attributes designated as required or not required • Attributes designated as allowing duplicates or not allowing duplicates • Decimal places • Data type • Auto numbers

5. Design Input Forms and Enter Example Data: Design 4 to 6 input forms for your section of the database. In addition, make up some example data and enter it into the example relations using MS Access. Purposely enter some erroneous data to see if your controls work. NOTE: Alternative to Access: You can use Excel for your database instead of Access. You will need to submit the following:

1. 5 to 10 pivot tables instead of the queries. 2. One comprehensive Excel dashboard instead of the Access reports. 3. You can create your input forms with Excel. 4. implement at least five controls in your Excel tables

http://www.exceluser.com/formulas/introducing-excels-three-types-of-spreadsheet- databases.htm PowerPoint Presentation Requirements 1. PowerPoint: Create a presentation with 8 to 16 slides that clearly communicate the design of your database. PowerPoint presentation rules are listed below:

• Do not present crowded slides with small character fonts that the audience cannot read.

• Make sure the PowerPoint slides hold together with a common look, background, and font style.

• Integrate items such as color, bullets, sound, media, and graphics into the presentation to emphasize important information.

• Present ideas in an organized and logical sequence.

22

REFERENCES

2019 AIS Educators Conference Denver, Colorado Paper Submission

Winery Case Study Using a REA Database Design

by

Thomas Tribunella III, PhD, CPA Professor of Accounting

School of Business, State University of New York at Oswego 209 Rich Hall, 7060 Route 104, Oswego, New York 13126-3599

Voice: 315.312.2544, thomas.tribunella@oswego.edu

and

Heidi Tribunella, MSA, CPA Clinical Professor of Accounting

Simon School of Business Administration, University of Rochester CS-3-145 Carol Simon Hall, 500 Joseph C. Wilson Blvd., Rochester, New York, 14627

Voice: 585.275.3757, heidi.tribunella@simon.rochester.edu

Borthick, A.F. 1996. Helping Accountants Learn to Get the Information Managers Want:

The Role of the Accounting Information Systems Course. Journal of Information Systems. 10(2). 75-85.

Chen, Peter 1976. The entity-relationship model—toward a unified view of data. ACM

Transactions on Database Systems. 1(1). 9-36. Codd, E.F. 1970. A relational model of data for large shared data banks.

Communications of the ACM. June 13(6). 377-387. Daigle, Ronald J., Louwers, Timothy J. and Morris, Taylor 2013. HealthSouth, Inc.: An

Instructional Case Examining Auditors' Legal Liability. Issues in Accounting Education Teaching Notes. November 28(4). 10-24.

Fickle, Le Ann A., Folwell, Raymond J., Ball, Trent and Clary, Carter 1996. Small winery

investment and operating costs: an extension bulletin. Retrieved from http://www.agribusiness- mgmt.wsu.edu/AgbusResearch/docs/wine_grapes/eb1996_05.pdf

Fordham, D.R. 2005. New Roles for AIS Courses: A Surprising Finding from a Case

Study. Journal of Information Systems. 19(1). 113-129.

23

Gelinas Jr., Ulric J., Levy, Elliott S. and Thibodeau, Jay C. 2001. Norwood Office

Supplies, Inc.: A Teaching Case to Integrate Computer‐Assisted Auditing Techniques into the Auditing Course. Issues in Accounting Education: November 16(4). 603-636.

Gowri Shankar, Premila and Tan, Hun‐Tong 2006. Determinants of Audit Preparers'

Workpaper Justifications. The Accounting Review: March 81(2). 473-495. McCrathy, W.E. 1982. The REA Accounting Model: A Generalized Framework for

Accounting Systems in a Shared Data Environment. The Accounting Review. July 57(3). 554-578.

McCollum, T. 2002. Data Analysis with SQL. The Internal Auditor. 59(4), 25-27. Miller, Charles R. (Tad) and Savage, Arline 2009. Vouch and Trace: A Revenue

Recognition Audit Simulation. Issues in Accounting Education. February 24(1). 93-103.

Norman, C. S., Rose, A. M., & Rose, J. M. 2006. Using Database Technology in the AIS

Classroom: Effects on Learning and Student Satisfaction. The Review of Business Information Systems. 10(1). 1-10.

Premuroso, Ronald F. and Houmes, Robert 2012. Financial statement risk assessment

following the COSO framework: An instructional case study. International Journal of Accounting & Information Management. 20(1). 26-48.

Romney M.B. and Steinbart P.J. 2018. Accounting Information Systems, 14th ed. Schafer, B. A., and K. Hurtt. 2006. The Lemonade Stand: A Teaching Case for

Developing an Information System for the Revenue Process. AIS Educator Journal 1(1): 5-10.

Taylor, E.Z. 2015. An Introduction to Access 2013. AIS Educator Journal. 10(1). 32-36.