For this part, assume you are entering information about transactions into their relational database. You will be entering financial information, but you will also need to consider the other types of information Big Marker would want to know about that event. You will also utilize what you have learned to identify internal controls specific to each transaction.

The transactions are as follows:

a. Billed 30 communities for monthly dues of $600 (each).

b. Borrowed $10,000 from First National Bank with a 6-month, 4% note payable.

c. Created a Chief Financial Officer position and established a support team for that function. The CFO’s annual salary will be $150,000; each support team member will earn $60,000 annually.

d. Purchase new computer servers from Dell, $40,000.

e. Reimbursed employee for business expenses: Supplies, $500; Food, $250; and Travel, $1,500.

For each of the transactions, you should answer the following questions:

1. Which business process (from chapters 12-14) is most closely associated with each transaction?

2. What is the most appropriate journal entry (if any) to record the transaction? For each of the accounts you use, identify the element (asset, liability, equity, revenue or expense).

3. What forms/documents would be used to process this transaction?

4. For each transaction suggest at least two additional pieces of information you would want to capture in a relational database.

5. For each transaction, suggest two internal controls you would implement. The controls should be specific to each transaction and you should justify/explain your response.

According to the producer price index database maintained by the Bureau of Labor Statistics, the average cost of computer equipment fell 4.8 percent between 2012 and 2013. Let’s see whether these changes are reflected in the income statement of Computer Tycoon Inc. for the year ended December 31, 2013.

	2013			2012
Sales Revenue	$	105,000		$	127,500
Cost of Goods Sold		62,500			73,500
Gross Profit		42,500			54,000
Selling, General, and Administrative Expenses		36,500			38,000
Interest Expense		550			500
Income before Income Tax Expense		5,450			15,500
Income Tax Expense		1,500			5,500
Net Income	$	3,950		$	10,000

Required:
1-a.	Compute the gross profit percentage for each year. (Round your answers to 1 decimal place.)

1-b.	Assuming that the change from 2012 to 2013 is the beginning of a sustained trend, is Computer Tycoon likely to earn more or less gross profit from each dollar of sales in 2014?

More Gross Profit Less Gross Profit

2-a.	Compute the net profit margin for each year. (Round your answers to 1 decimal place.)

2-b.	Did Computer Tycoon do a better or worse job of controlling expenses in 2013 relative to 2012?
	Better Job Worse Job

3-a.	Computer Tycoon reported average net fixed assets of $54,700 in 2013 and $45,600 in 2012. Compute the fixed asset turnover ratios for both years. (Round your answers to 2 decimal places.)

3-b.	Did the company better utilize its investment in fixed assets to generate revenues in 2013 or 2012?
	2012 2013

4-a.	Computer Tycoon reported average stockholders’ equity of $54,500 in 2013 and $41,300 in 2012. Compute the return on equity ratios for both years. (Round your answers to 1 decimal place.)

4-b.	Did the company generate greater returns for stockholders in 2013 than in 2012?
	Yes No

1. A cosmetic product retailer needs to create a database to keep track of the information for its business operations. The company has a web site that posts all its products. The product information includes product ID, product name, description, and unit price. The company also needs to keep track of customers’ information, including customer names, their shipping addresses, and the email address. The company creates an account for each customer for identification and tracking purpose. A customer can purchase multiple products with different quantities in one order. The company’s products have many prospective customers. The company needs to keep track of information for all orders it received, including the order date, invoice number, and information about products purchased in an order such as IDs of products, and quantities, etc. Company’s products are stocked in several warehouses. So company also needs to keep track of the information about each warehouse such as its name, address, manager, telephone, etc. a. Create an E/R model for this scenario. In your E/R model you need to show the names of entities, names of attributes, keys and the name(s) of relationship(s). Also indicate maximum and minimum cardinalities You may hand-draw the ER model and insert its image here.

A new database needs to be developed. You are required to draw the related Entity Relationship Diagram (ERD) that includes the following information:

(a) Employee (the company has several employees)

(b) Department (each employee is assigned to one department)

(c) Payroll (each employee receives his/her pay for the period)

(d) Customer (employees sell products to customer)

(e) Products (the company offers several products)

Note that an employee can sell products to several customers and that every customer can be served by multiple employees. Include relationship types only if necessary and specify suitable primary keys for all elements. Include all primary keys and all foreign keys in the ERD.

Use PowerPoint to draw the ERD and then upload the file. Alternatively, you can use another software to draw the ERD and submit a PDF file.

Question 2. The following tables provide some example data that will be kept in the database. Write the INSERT commands necessary to place the following data in the tables that were created in Question 1. Alternatively provide the text files (copy and pasted into your final report) and the open/insert from file commands..

Table: actor

act_id |      act_fname       |      act_lname       | act_gender

    101 | James                | Stewart              | M

    102 | Deborah              | Kerr                 | F

    103 | Peter                | OToole               | M

    104 | Robert               | De Niro              | M

    105 | F. Murray            | Abraham              | M

    106 | Harrison             | Ford                 | M

    107 | Nicole               | Kidman               | F

    108 | Stephen              | Baldwin              | M

    109 | Jack                 | Nicholson            | M

    110 | Mark                 | Wahlberg             | M

    111 | Woody                | Allen                | M

    112 | Claire               | Danes                | F

    113 | Tim                  | Robbins              | M

    114 | Kevin                | Spacey               | M

    115 | Kate                 | Winslet              | F

    116 | Robin                | Williams             | M

    117 | Jon                  | Voight               | M

    118 | Ewan                 | McGregor             | M

    119 | Christian            | Bale                 | M

    120 | Maggie               | Gyllenhaal           | F

    121 | Dev                  | Patel                | M

    122 | Sigourney            | Weaver               | F

    123 | David                | Aston                | M

    124 | Ali                  | Astin                | F

Table: movie_cast

act_id | mov_id |              role

    101 |    901 | John Scottie Ferguson

    102 |    902 | Miss Giddens

    103 |    903 | T.E. Lawrence

    104 |    904 | Michael

    105 |    905 | Antonio Salieri

    106 |    906 | Rick Deckard

    107 |    907 | Alice Harford

    108 |    908 | McManus

    110 |    910 | Eddie Adams

    111 |    911 | Alvy Singer

    112 |    912 | San

    113 |    913 | Andy Dufresne

    114 |    914 | Lester Burnham

    115 |    915 | Rose DeWitt Bukater

    116 |    916 | Sean Maguire

    117 |    917 | Ed

    118 |    918 | Renton

    120 |    920 | Elizabeth Darko

    121 |    921 | Older Jamal

    122 |    922 | Ripley

    114 |    923 | Bobby Darin

    109 |    909 | J.J. Gittes

    119 |    919 | Alfred Borden

Table: movie

mov_id |                     mov_title                      | mov_year | mov_time |    mov_lang     | mov_dt_rel | mov_rel_country

    901 | Vertigo                                            |     1958 |      128 | English         | 1958-08-24 | UK

    902 | The Innocents                                      |     1961 |      100 | English         | 1962-02-19 | SW

    903 | Lawrence of Arabia                                 |     1962 |      216 | English         | 1962-12-11 | UK

    904 | The Deer Hunter                                    |     1978 |      183 | English         | 1979-03-08 | UK

    905 | Amadeus                                            |     1984 |      160 | English         | 1985-01-07 | UK

    906 | Blade Runner                                       |     1982 |      117 | English         | 1982-09-09 | UK

    907 | Eyes Wide Shut                                     |     1999 |      159 | English         |            | UK

    908 | The Usual Suspects                                 |     1995 |      106 | English         | 1995-08-25 | UK

    909 | Chinatown                                          |     1974 |      130 | English         | 1974-08-09 | UK

    910 | Boogie Nights                                      |     1997 |      155 | English         | 1998-02-16 | UK

    911 | Annie Hall                                         |     1977 |       93 | English         | 1977-04-20 | USA

    912 | Princess Mononoke                                  |     1997 |      134 | Japanese        | 2001-10-19 | UK

    913 | The Shawshank Redemption                           |     1994 |      142 | English         | 1995-02-17 | UK

    914 | American Beauty                                    |     1999 |      122 | English         |            | UK

    915 | Titanic                                            |     1997 |      194 | English         | 1998-01-23 | UK

    916 | Good Will Hunting                                  |     1997 |      126 | English         | 1998-06-03 | UK

    917 | Deliverance                                        |     1972 |      109 | English         | 1982-10-05 | UK

    918 | Trainspotting                                      |     1996 |       94 | English         | 1996-02-23 | UK

    919 | The Prestige                                       |     2006 |      130 | English         | 2006-11-10 | UK

    920 | Donnie Darko                                       |     2001 |      113 | English         |            | UK

    921 | Slumdog Millionaire                                |     2008 |      120 | English         | 2009-01-09 | UK

    922 | Aliens                                             |     1986 |      137 | English         | 1986-08-29 | UK

    923 | Beyond the Sea                                     |     2004 |      118 | English         | 2004-11-26 | UK

    924 | Avatar                                             |     2009 |      162 | English         | 2009-12-17 | UK

    926 | Seven Samurai                                      |     1954 |      207 | Japanese        | 1954-04-26 | JP

    927 | Spirited Away                                      |     2001 |      125 | Japanese        | 2003-09-12 | UK

    928 | Back to the Future                                 |     1985 |      116 | English         | 1985-12-04 | UK

    925 | Braveheart                                         |     1995 |      178 | English         | 1995-09-08 | UK

Table: director

dir_id |      dir_fname       |      dir_lname

    201 | Fred                 | Caravanhitch

    202 | Jackie               | Claytonburry

    203 | Greene               | Lyon

    204 | Miguel               | Camino

    205 | George               | Forman

    206 | Antartic             | Scott

    207 | Stanlee              | Carbrick

    208 | Bryon                | Sanger

    209 | Roman                | Polanski

    210 | Paul                 | Thomas Anderson

    211 | Woody                | Allen

    212 | Hayao                | Miyazaki

    213 | Frank                | Darabont

    214 | Sam                  | Mendes

    215 | James                | Cameron

    216 | Gus                  | Van Sant

    217 | John                 | Boorman

    218 | Danny                | Boyle

    219 | Christopher          | Nolan

    220 | Richard              | Kelly

    221 | Kevin                | Spacey

    222 | Andrei               | Tarkovsky

    223 | Peter                | Jackson

Table: movie_direction

dir_id | mov_id

    201 |    901

    202 |    902

    203 |    903

    204 |    904

    205 |    905

    206 |    906

    207 |    907

    208 |    908

    209 |    909

    210 |    910

    211 |    911

    212 |    912

    213 |    913

    214 |    914

    215 |    915

    216 |    916

    217 |    917

    218 |    918

    219 |    919

    220 |    920

    218 |    921

    215 |    922

    221 |    923

Table: genres

gen_id |      gen_title

   1001 | Action

   1002 | Adventure

   1003 | Animation

   1004 | Biography

   1005 | Comedy

   1006 | Crime

   1007 | Drama

   1008 | Horror

   1009 | Music

   1010 | Mystery

   1011 | Romance

   1012 | Thriller

   1013 | War

Table: movie_genres

mov_id | gen_id

    922 |   1001

    917 |   1002

    903 |   1002

    912 |   1003

    911 |   1005

    908 |   1006

    913 |   1006

    926 |   1007

    928 |   1007

    918 |   1007

    921 |   1007

    902 |   1008

    923 |   1009

    907 |   1010

    927 |   1010

    901 |   1010

    914 |   1011

    906 |   1012

    904 |   1013

Table: rating

mov_id | rev_id | rev_stars | num_o_ratings

    901 |   9001 |      8.40 |        263575

    902 |   9002 |      7.90 |         20207

    903 |   9003 |      8.30 |        202778

    906 |   9005 |      8.20 |        484746

    924 |   9006 |      7.30 |

    908 |   9007 |      8.60 |        779489

    909 |   9008 |           |        227235

    910 |   9009 |      3.00 |        195961

    911 |   9010 |      8.10 |        203875

    912 |   9011 |      8.40 |

    914 |   9013 |      7.00 |        862618

    915 |   9001 |      7.70 |        830095

    916 |   9014 |      4.00 |        642132

    925 |   9015 |      7.70 |         81328

    918 |   9016 |           |        580301

    920 |   9017 |      8.10 |        609451

    921 |   9018 |      8.00 |        667758

    922 |   9019 |      8.40 |        511613

    923 |   9020 |      6.70 |         13091

Table: reviewer

rev_id |            rev_name

   9001 | Righty Sock

   9002 | Jack Malvern

   9003 | Flagrant Baronessa

   9004 | Alec Shaw

   9005 |

   9006 | Victor Woeltjen

   9007 | Simon Wright

   9008 | Neal Wruck

   9009 | Paul Monks

   9010 | Mike Salvati

   9011 |

   9012 | Wesley S. Walker

   9013 | Sasha Goldshtein

   9014 | Josh Cates

   9015 | Krug Stillo

   9016 | Scott LeBrun

   9017 | Hannah Steele

   9018 | Vincent Cadena

   9019 | Brandt Sponseller

   9020 | Richard Adams

University ITS maintains a database about the requests for software that staff members make for their units before each teaching period (e.g. Semester 1, Semester 2). A unit may need several items of software, installed in multiple labs. Labs are housed across the various buildings of the university, with each lab having a unique room number (e.g. Lab 245.3.062 is located in building number 245, name Science & Computing). Each piece of software is requested individually for a lab and is given a request ID so that its progress can be tracked, from initial request, install, testing (both functionality and user acceptance) to final deployment in the lab. Basic information about staff members, buildings, labs, and the software is also kept.

The schema for this database is as follows: (note that primary keys are shown underlined, foreign keys in bold).

BUILDING (BuildingNo, BuildingName)

LAB (RoomNo, Capacity, BuildingNo)

STAFF (StaffNo, StaffName, Email, Phone)

SOFTWARE( SoftwareName, Version, MediaLocation)

REQUEST (RequestID, StaffNo, SoftwareID, RoomNo, RequestDate, TeachingPeriod, Progress)

Provide relational algebra queries to find the following information NOTE:  You can use the symbols , , *, etc or the words ‘RESTRICT’, PROJECT’, etc as you prefer.  Use nested brackets or intermediate relations, as you prefer.  You do not need to try to make efficient queries – just correct ones.  Where you use a join, you should always show the join condition.  Use the information provided in the question, not a shortcut – for example, if the question refers to the ‘Law’ building, do not use the Law building number ‘465’ in your criteria.

a. List the name and email of all staff.

b. List the room number and capacity of all labs located in the Science & Computing building with a capacity of over 30.

c. List the names and versions of all the software requested for the labs in Science & Computing in Semester 2.

d. List all the labs (room number) where Microsoft Visio is NOT requested for Semester 2.

e. List the software name, version, room number, building name and staff name of all software requests logged for semester 2.

f. List of emails of all staff who have NOT submitted any software requests for Semester 2.

g. List the names of software requested for labs in the Law building, requested by staff member S2019876, or both.

h. List all the requests (RequestID, StaffNo, RoomNo) for Oracle SQL Developer that have progressed to ‘user acceptance testing’ or ‘deployed’.

i. List the labs whose progress status is listed as ‘deployed’ for ALL software requests to that lab.

j. List all the labs in the Law building, and the names of software requested for each of them (if any).

In each chapter of this book, we use a database for a fictitious company, Performance Lawn Equipment (PLE), within a case exercise for applying the tools and techniques introduced in the chapter.33 To put the database in perspective, we first provide some background about the company, so that the applications of business analytic tools will be more meaningful.

33 The case scenario was based on Gateway Estate Lawn Equipment Co. Case Study, used for the 1997 Malcolm Baldrige National Quality Award Examiner Training course. This material is in the public domain. The database, however, was developed by the author.

PLE, headquartered in St. Louis, Missouri, is a privately owned designer and producer of traditional lawn mowers used by homeowners. In the past 10 years, PLE has added another key product, a medium-size diesel power lawn tractor with front and rear power takeoffs, Class I three-point hitches, four-wheel drive, power steering, and full hydraulics. This equipment is built primarily for a niche market consisting of large estates, including golf and country clubs, resorts, private estates, city parks, large commercial complexes, lawn care service providers, private homeowners with five or more acres, and government (federal, state, and local) parks, building complexes, and military bases. PLE provides most of the products to dealerships, which, in turn, sell directly to end users. PLE employs 1,660 people worldwide. About half the workforce is based in St. Louis; the remainder is split among their manufacturing plants.

In the United States, the focus of sales is on the eastern seaboard, California, the Southeast, and the south central states, which have the greatest concentration of customers. Outside the United States, PLE’s sales include a European market, a growing South American market, and developing markets in the Pacific Rim and China. The market is cyclical, but the different products and regions balance some of this, with just less than 30% of total sales in the spring and summer (in the United States), about 25% in the fall, and about 20% in the winter. Annual sales are approximately $180 million.

Both end users and dealers have been established as important customers for PLE. Collection and analysis of end-user data showed that satisfaction with the products depends on high quality, easy attachment/dismount of implements, low maintenance, price value, and service. For dealers, key requirements are high quality, parts and feature availability, rapid restock, discounts, and timeliness of support.

PLE has several key suppliers: Mitsitsiu, Inc., the sole source of all diesel engines; LANTO Axles, Inc., which provides tractor axles; Schorst Fabrication, which provides subassemblies; Cuberillo, Inc, supplier of transmissions; and Specialty Machining, Inc., a supplier of precision machine parts.

To help manage the company, PLE managers have developed a “balanced scorecard” of measures. These data, which are summarized shortly, are stored in the form of a Microsoft Excel workbook (Performance Lawn Equipment) accompanying this book. The database contains various measures captured on a monthly or quarterly basis and used by various managers to evaluate business performance. Data for each of the key measures are stored in a separate worksheet. A summary of these worksheets is given next:

Dealer Satisfaction, measured on a scale of 1–5 (1 = poor, 2 = less than average, 3 = average, 4 = above average, and 5 = excellent). Each year, dealers in each region are surveyed about their overall satisfaction with PLE. The worksheet contains summary data from surveys for the past 5 years.

End-User Satisfaction, measured on the same scale as dealers. Each year, 100 users from each region are surveyed. The worksheet contains summary data for the past 5 years.

2014 Customer Survey, results from a survey for customer ratings of specific attributes of PLE tractors: quality, ease of use, price, and service on the same 1–5 scale. This sheet contains 200 observations of customer ratings.

Complaints, which shows the number of complaints registered by all customers each month in each of PLE’s five regions (North America, South America, Europe, the Pacific, and China).

Mower Unit Sales and Tractor Unit Sales, which provide sales by product by region on a monthly basis. Unit sales for each region are aggregated to obtain world sales figures.

Industry Mower Total Sales and Industry Tractor Total Sales, which list the number of units sold by all producers by region.

Unit Production Costs, which provides monthly accounting estimates of the variable cost per unit for manufacturing tractors and mowers over the past 5 years.

Operating and Interest Expenses, which provides monthly administrative, depreciation, and interest expenses at the corporate level.

On-Time Delivery, which provides the number of deliveries made each month from each of PLE’s major suppliers, number on time, and the percent on time.

Defects After Delivery, which shows the number of defects in supplier-provided material found in all shipments received from suppliers.

Time to Pay Suppliers, which provides measurements in days from the time the invoice is received until payment is sent.

Response Time, which gives samples of the times taken by PLE customer-service personnel to respond to service calls by quarter over the past 2 years.

Employee Satisfaction, which provides data for the past 4 years of internal surveys of employees to determine their overall satisfaction with their jobs, using the same scale used for customers. Employees are surveyed quarterly, and results are stratified by employee category: design and production, managerial, and sales/administrative support.

In addition to these business measures, the PLE database contains worksheets with data from special studies:

Engines, which lists 50 samples of the time required to produce a lawn-mower blade using a new technology.

Transmission Costs, which provides the results of 30 samples each for the current process used to produce tractor transmissions and two proposed new processes.

Blade Weight, which provides samples of mower-blade weights to evaluate the consistency of the production process.

Mower Test, which lists test results of mower functional performance after assembly for 30 samples of 100 units each.

Employee Retention, data from a study of employee duration (length of hire) with PLE. The 40 subjects were identified by reviewing hires from 10 years prior and identifying those who were involved in managerial positions (either hired into management or promoted into management) at some time in this 10-year period.

Shipping Cost, which gives the unit shipping cost for mowers and tractors from existing and proposed plants for a supply-chain-design study.

Fixed Cost, which lists the fixed cost to expand existing plants or build new facilities, also as part of the supply-chain-design study.

Purchasing Survey, which provides data obtained from a third-party survey of purchasing managers of customers of Performance Lawn Care.

Elizabeth Burke has recently joined the PLE management team to oversee production operations. She has reviewed the types of data that the company collects and has assigned you the responsibility to be her chief analyst in the coming weeks. To prepare for this task, you have decided to review each worksheet and determine whether the data were gathered from internal sources, external sources, or have been generated from special studies. Also, you need to know whether the measures are categorical, ordinal, interval, or ratio. Prepare a report summarizing the characteristics of the metrics used in each worksheet.

According to the producer price index database maintained by the Bureau of Labor Statistics, the average cost of computer equipment fell 8.1 percent between 2012 and 2013.

Required:

1. Conduct a horizontal analysis by calculating the year-over-year changes in each line item, expressed in dollars and in percentages for the income statement of Computer Tycoon Inc. for the year ended December 31, 2013. (Decreases should be indicated by a minus sign. Round your percentage answers to 1 decimal place.)

COMPUTER TYCOON, INC. Income Statements For the Year Ended December 31 Change in 2013 2012 Dollars Percentage Sales Revenue $103,000 $124,500 % Cost of Goods Sold 61,500 72,700 % Gross Profit 41,500 51,800 % Selling, General, and Administrative Expenses 36,300 37,600 % Interest Expense 530 490 % Income before Income Tax Expense 4,670 13,710 % Income Tax Expense 1,000 5,300 % Net Income $3,670 $8,410 %

2-a. Conduct a vertical analysis by expressing each line as a percentage of total revenues. (Round your percentage answers to 1 decimal place.)

	COMPUTER TYCOON, INC. Income Statements For the Year Ended December 31 2013 2012 Sales Revenue $103,000 % $124,500 % Cost of Goods Sold 61,500 % 72,700 % Gross Profit 41,500 % 51,800 % Selling, General, and Administrative Expenses 36,300 % 37,600 % Interest Expense 530 % 490 % Income before Income Tax Expense 4,670 % 13,710 % Income Tax Expense 1,000 % 5,300 % Net Income $3,670 % $8,410 %

2-b. Excluding income tax, interest, and operating expenses, did Computer Tycoon earn more profit per dollar of sales in 2013 compared to 2012?

	Yes
	No

The Advanced Tech Company has a project to design an integrated information database for a major bank. Data for the project are given in the Table. Indirect project costs amount to $300 per day. The company will incur a $150 per day penalty for each day the project lasts beyond day 14. What would be the least cost to achieve this desired result?

Activity	Normal Time (days)	Normal Cost ($)	Crash Time (days)	Crash Cost ($)	Immediate Predecessor(s)
A	6	1,000	5	1,200	—
B	4	800	2	2,000	—
C	3	600	2	900	A, B
D	2	1,500	1	2,000	B
E	6	900	4	1,200	C, D
F	2	1,300	1	1,400	E
G	4	900	4	900	E
H	4	500	2	900	G