accounting information systems - group project

1

Analytics Group Project Instructions

OBJECTIVE:

Your objective is to evaluate a company’s processes, develop an integrated accounting database,

produce a simple set of financial reports, and conduct analyses that will add value and enhance the

performance of the company. Ideally, groups will have 3-4 members. You may work with your group in-

person and/or online.

Carefully read the description of Boneless Seafood company. Become familiar with the data contained

in the associated Boneless Seafood Excel data file. Review the instructions below and discuss the

project with your group before you begin work. Although you will divide up the workload, every

student should be familiar with every part of the project. Each aspect should be discussed and agreed

upon, and all members are responsible for producing an integrated final project.

What you’ll turn in:

In class:

Printed report that contains an executive summary, table of contents, and items described in

deliverables A through H. Further details are provided on p. 3 and in subsequent instructions.

One combined, sequential time log that shows dates, activities, and names of group member or

members who completed each activity. You can use a shared Google sheet or any format of your

choice to track your progress.

In my email:

Subject line: 335 HB1 Group Project: Amy Lee, Pat Garcia, and Joe Smith (use your own section

and names)

3 attachments: A single Access database that includes properly numbered and descriptively named

queries, a summary Excel file, and a single Word or PDF document that contains your entire report

(use a scanner or scanning app such as Genius Scan if necessary).

**Important** I change some aspects of my projects each term. If a group submits answers to a

previous term’s assignment, the group will receive a 0 on the project and will be subject to a hearing by

the university’s academic integrity disciplinary committee. You’re investing a lot of time and money to

learn accounting—do your own work so that you can get the knowledge and skills you’re here for.

Keep copies of all of your notes and preliminary work in case you need to provide evidence of the

independence of your work or of your contribution to the group effort.

Suggestions for how to get started

Before you begin, study the instructions, interviews, and data.

Complete part B first, and make sure everyone understands and agrees on the data model. Chapter 8 provides a lot of information that might be helpful as you work through the group project. For example, Figure 8.6 provides a generic data model you can use as a starting point.

Each table in the Excel file will represent an entity in your model, except that the orders_sales file will

need to be broken into its heading and line items components (to see why, look at Atla_O90007.) You

may break down some other event files as well.

2

The table below gives suggestions for how you might want to organize your work. This is a very big

project, and every group member will need to be a full participant.

Part When What Deliverables Split?

A any time

draft 4 BPMNs

discuss & revise

draw BPMNs with

flowcharting toos assign BPMNs

B&C *first* first draft of data model focusing on Rs, Es, As draw final data model everyone

identify PK for each table;

split tables if non-key fields don't describe the

PK,

remove duplicates everyone

upload tables to Access,

create relationships,

clean data if needed to enforce integrity

finalize relationships

diagram someone

D

*when you

finish C*

create accounting queries;

discuss and revise

transfer accounting data

to Excel template assign queries

E, F, G, H any time

analyze data

create reports

discuss & revise

clean up E,F,G,H for final

report and create

appendices where desired assign reports

Executive

summary last

assemble documents for group report;

label and number everything someone

write final report celebrate! everyone

3

COMPANY ANALYSIS

Executive summary/table of contents

After you have completed all of steps A through G below, prepare an executive summary. Imagine that

the CEO of Boneless Seafood has hired you to do this work, and you’re explaining what you found. The

summary should 1) briefly summarize what you did (e.g. We analyzed the interviews and prepared

process models…), and 2) describe what you learned about the company from the Excel spreadsheet

and from each of your reports. You are welcome to include recommendations about what the company

should do if you have them.

The table of contents should list items A through H and, if applicable, appendices for items D through H

with page numbers. Pages can be hand-numbered. If your process diagrams and/or data model are

difficult to read when reduced to 8 ½ x 11, you may include expanded versions in appendices.

Your report should include be organized as follows:

Executive summary

Table of contents with page numbers

Items A through H, labeled and in order

Appendices, in order—if you include appendices, label them with the letter they support

I don’t want you to spend a ton of time making your final report look professional and perfect. You

don’t need a fancy binder, and you can use handwriting for crow’s feet, page numbers, or other minor

things if necessary. I do need requirements in order and carefully labeled and page numbers so that I

can find things, and I do need a clearly written executive summary, and meaningful titles and labels on

your reports and Access queries, so that I can follow and understand your analysis.

A. Prepare process diagrams using BPMN

After gaining an understanding of Boneless’ business processes, document each current business process

with a business process activity diagram using business process modeling notation. You will likely have 4

processes: 1) purchase and pay for fish, 2) purchase and pay for miscellaneous items, 3) pay employees,

4) sell and receive payment for fish. You don’t need to create a process for returns (but you can). Use

your judgment to decide how much detail to include in your diagrams. Try to be consistent across the

diagrams, even when they are created by different group members. You do not need to include

documents or error symbols as shown in figure 8.4 in your textbook. You do need to include swim

lanes like the ones in figure 6.4. If you know what activities your external agent is doing, it is preferable

to show them, so figure 5.3 is preferred to figure 5.4. Once you finalize the diagrams, draw them using a

flowcharting tool such as draw.io. You can add annotations where helpful and/or where you aren’t sure

how to represent something in your BPMN model. You can also add a short written description or list

of activity steps for each diagram if desired.

Deliverable: BPMN process diagrams

B. Prepare data model using crow’s foot notation

Construct a data model for each of the four activity models described above. This should be done in a

way that is consistent with what we’ve done in class, and what is represented in the text. Many-to-many

relationships are OK and expected in this model. Draw your data model using your preferred

flowcharting software. It is a good idea to wait until you’ve finalized your Access database to formalize

beyond a rough sketch because the diagram may change as you work. You can draw crow’s foot

4

notations neatly by hand if you have difficulty drawing them with the flowcharting tool. Organize your

diagram with Resource files on the left, Events in the middle, and Agents on the right.

Deliverable: Data Model

C. Create normalized Access database

Using the Excel spreadsheet provided, transfer the all data to Access, establish efficient tables, link the

tables together on the relationships screen, and enforce referential integrity. You’ll need to do some

cleaning and organizing of the data as part of this process. The data contains occasional typographical

errors and some tables are not in a normalized form. You will notice that the names of columns

containing the same field differ. You may find the “Remove Duplicates” function in Excel and the “Find

Unmatched” and “Remove Duplicates” queries available within the Access query wizard to be helpful as

you complete these steps. Rearrange your relationships match your Data Model from B. as closely as

possible. Lengthen the boxes to make sure that every field in every file can be seen. Make sure that I

can see the infinity signs.

Deliverable: Printout of relationships in Access

ACCOUNTING AND ANALYTICS

D. Create database queries to populate a provided spreadsheet

Once your group successfully builds a normalized database, you’ll use the database to construct a set of

simplified financial reports. Required queries should be run for Q1 2013 and include: a) an income

statement, b) an income statement by location, and c) an A/R detail by customer. *Note that some of

the worksheet tabs contain data that is not from Q1.

Attempt to construct some of your queries using SQL commands, and look at the SQL code for the

queries you create in Design View. Remember that you can do queries in stages, for example you can

do customer sales and cash receipts queries, and then use these queries (rather than the original tables)

to calculate A/R. Every group member is responsible for understanding the logic of every query,

regardless of who wrote it.

Name the queries as follows. Begin the name a capital D and an a, b, or c, depending on which

requirement you’re working on. Then add a sequential number and a descriptive name. For example:

Da1NameOfYourFirstQuery, Da2NameOfYourSecondQuery, etc. This will allow me to follow the sequence of your query logic. You’ll use this same naming convention for E, F, G, and H.

Transfer your results to the Boneless Financial Results Excel template, filling in all the green cells. You

are allowed to edit the provided Excel workbook template to fit your group’s needs.

d) Create a pivot table and pivot chart. After you have complete the A/R detail spreadsheet, create a

PivotTable and PivotChart that summarizes Sales, Cash Receipts, and A/R by store. Sort your data from

highest to lowest by A/R

Deliverables: The spreadsheets for requirements a) and b) and the pivot table and chart

for requirement d) should be included in the body of the report. You do not need to include

the full listing from c) for A/R detail. You are allowed to add an appendix to further explain your work.

5

SYSTEM SOLUTION

This part of the project is unstructured, and every team’s solutions will be different. To create the

reports, you can use Access, Excel, or a combination of both. You are also allowed to use Tableau, but

it is not required or expected. For the reports described below use the same naming conventions for

your queries as you did in D.

Final reports and descriptions should be included in the body of your report. For each

required report, I’d like to see the final report and understand the steps you took to create it. If

there’s anything that may be helpful to me in understanding how you created the reports—descriptions,

screenshots, etc.—please include them in an appendix.

E. Report #1, Standard cost impacts

Many companies use standard costs to calculate costs and therefore profits for products, stores,

customers, etc. One reason for this is because real costs can fluctuate day to day, so profits are more

stable with standard costs. Standard costs are ideally equivalent to the average of actual costs.

Kendrick knows that if his standard prices are incorrect, he could be over or underestimating gross

margins and profits for his stores. Kendrick would like to know for which types of fish were the

standard costs most inaccurate and which stores were most affected.

Deliverables: One or more reports along with a written answer explaining which store’s

profits could be over/underestimated because of standard costs, and why. Label your

report(s) #1, Standard cost impacts

F. Report #2, Fish substitutions

When a customer orders a particular type of fish, and the fish is not available that day, Boneless Seafood

makes substitutions. These substitutions affect customers that wanted the fish. Kendrick would like to

improve his ability to satisfy customers and would like to know which kinds of fish were not available

when needed and which customers were most affected.

Deliverables: One or more reports along with a written answer explaining which

customers were affected by unavailable fish, and why. Label your report(s): #2, Fish

substitutions

G. Report #3, Sales expansion

Boneless Seafood is thinking of expanding. Create a report that will provide information that could be

helpful in thinking about where or how to expand.

Deliverables: One or more reports along with a written answer explaining which stores,

products, customers or another factor could be expanded, and why. Label your report(s):

#3, Sales expansion

H. Report #4, Student choice (this step is optional for groups with only 3 members)

Look at the data and ask a question of your own. Create a report that will provide information that

could be helpful in answering that question.

Deliverables: One or more reports along with a written answer that includes your

question and explains your analysis and what you have learned. Label your report(s): #4,

Student choice

6

COMPANY INFORMATION

Boneless Seafood (BS) purchases large quantities of fresh fish directly from fishers and sells it to

wholesale customers such as restaurants and retail stores. The company operates 8 stores along the

east coast, from Florida to Maine. These stores are essentially warehouses that purchase, process, re-

package and deliver fish. The company was founded by Kendrick Lamar, who had spent most of his life

as a deckhand and fisher. Through hard work, the company has grown rapidly and built a reputation for

high quality fresh fish.

Kendrick has recently suffered a computer malfunction, and has been rebuilding all of his accounting

records using an Excel spreadsheet. He has hired your team to help document current business

processes, rebuild his accounting system and prepare financial reports in Access, and use your analytical

skills to explore some business issues.

Interviews about business processes

Summary of Interview with Drake Graham, Supervisor, Portland Store

Drake is the supervisor for the Portland store, and his duties are similar to supervisor duties at all the

stores. Being supervisor is only a part-time responsibility. Most of the time, Drake is just another

employee at the Portland store, buying fish, preparing fish for delivery to customers, making deliveries,

etc.

Boneless seafood carefully monitors the fish catch and availability on a daily basis. Customers call to

place orders for future fish deliveries. Usually, they order about one week in advance. The store

employees know most of the customers by name and also know what types of fish they prefer. They

also know what types of fish are likely available, so they try to steer the orders to those fish. They

record customer orders in the order log. Then, every morning, they look at the log to estimate the

types of fish and quantities that they need to buy for the day. If it is clear that a customer’s order cannot

be fulfilled that day, the customer will be called and the order will be adjusted.

Each morning, one employee hops in a truck and drives to the local pier to buy the freshest available fish

from fishers to fulfill customers’ orders. The employee carefully selects the best fish and loads it into the

truck. Boneless doesn’t buy fish for specific orders, instead, the employees try to get enough fish of each

type to meet all the orders for that day. Sometimes, it is not possible to get the specific types and

quantities that the customers ordered, but other high quality fish is available. In that case, the employee

will buy the other fish and contact the customers to modify their orders. The purchase document

identifies the purchase number (sequential), the fisher number, the purchasing employee, the truck VIN

(to track mileage), the type of fish, the quantity purchased in lbs, and the purchase price. The fish is

purchased at the prevailing market price that day. On occasion, one purchase can involve multiple types

of fish, although typically one purchase is for one type of fish.

When the truck returns to the store with the purchased fish, all the employees unload the truck, clean

the fish, and place it on ice for delivery to the customers in the afternoon. The employees then prepare

the delivery documents. Those documents list the customers’ original order number, the order date,

the delivery (sale) date, the truck VIN used to deliver the fish (to track mileage), the types and quantities

of fish both ordered and sold, and the sale price. Kendrick sets the sales prices for all stores, and those

prices can change every couple of months. One order can (and typically does) involve several types of

fish.

7

Each afternoon, the employees load the truck for the deliveries to customers. One employee then

delivers the fish. Each customer receives the fish and the delivery document. They then pay for all their

deliveries by the end of the month. They send the payment to the address listed on the delivery

document (currently the address of the New Haven store). When the employee returns to the store,

we put all the delivery documents in an envelope and mail them to Kendrick.

Summary of Interview with Abel Tesfaye, Cash Receipts Clerk, New Haven Store

Every day, Abel opens mail from customers. He deposits all checks received into the bank, account and

then he mails the list of cash receipts along with a copy of the deposit slip to Kendrick. Abel receives

payments from customers at the end of each month. He assigns sequential cash receipt numbers to each

incoming payment, recording the customer number, the receipt date, and the receipt amount.

Summary of Interview with Nicki Minaj, Employee, Myrtle Beach Store

Boneless Seafood guarantees satisfaction and provides complete refunds if the customer is unhappy with

any fish received on an order. The customer calls the local store and reports a problem with the fish.

The employee that answers the phone immediately prepares a refund authorization. If the customer

hasn’t yet paid for that order, the employee instructs the customer to take that amount off their bill. In

that case, the employee then sends the refund authorization directly to Kendrick for information. If the

customer has paid for that order, the employee notifies the customer that they will receive a check

within about a week. The employee sends the refund authorization form to the accounts payable clerk

in Boston, who sends the customer a check. Then, the refund authorization and the payment

information is forwarded to Kendrick.

Summary of Interview with Cardi Bee, Cash Disbursements Clerk, Boston Store

Cardi is in charge of cash disbursements and accounts payable. She receives documents related to fish

purchases and for miscellaneous purchases on a daily basis. She prepares checks for payments to fishers

on a weekly basis and checks for miscellaneous purchases on a monthly basis. If, at the time she is

preparing checks, there are multiple invoices from a particular fisher or vendor, she will combine them

and write a single check.

When stores receive invoices from fishers, they check them for correctness, then mail them along with

a copy of the associated purchase order to Cardi. When stores receive invoices for miscellaneous

purchases, such as phone bills, truck repairs, gasoline, etc., they validate them, assign purchase order

numbers, and mail the invoices to Cardi.

Cardi stamps each document with the check number, the check amount, and the date paid. After writing

the checks and sending out the payments, Cardi packages all the documents (fish purchase documents

and miscellaneous invoices) and delivers them to Kendrick’s office.

Summary of Interview with DJ Khalid, Payroll Clerk, Baltimore Store

DJ maintains the payroll records. Each employee fills out his or her timecard each day. At the end of

each month, each store supervisor collects employee timecards, checks them for accuracy, and sends

them by overnight delivery to DJ at the Baltimore store. If a timecard looks to be incorrect, the

supervisor asks the employee to correct it. DJ then prepares the payroll checks and sends them to the

addresses designated for each employee. Once the checks are mailed, DJ mails the timecards and copies

of the checks to Kendrick.

8

Summary of Interview with Kendrick Lamar, Owner, Boneless Seafood

Individual stores track their own purchases, inventories, sales, etc. They send all the hard copy

documents to Kendrick, via the accounts payable or payroll clerks when appropriate. Kendrick prepares

his accounting reports from those documents as well as the deposit information he receives from the

banks and the cash disbursement information he receives from the accounts payable and payroll clerks.

Samples from data tables in Excel

budget_sales

cash

cash_disb_fishers

cash_receipts

9

customers

employees

fish_inv

fishers

order_sales

payroll

10

purchases_fish

purchases_misc

returns

stores

timecards

trucks

vendors_misc