You work for a large U.S. computer assembly & sales company (like Dell) and are assigned to manage a phone service center located in a distant country where labor is cheaper. As you can see from the Employee table in the accompanying Access file, your crew currently consists of 6 technicians of various levels (1, 2, or 3). The Tech Cost table shows you the meaning and the companys cost per minute of each level.

The service center helps customers who call in with questions/problems about whichever one of the companys 4 Product Lines s/he purchased during the warranty period. You are evaluated by how well you maintain the quality of the service center while controlling and minimizing costs.

With a sample of 383 actual calls in the Service Provided table, you could compare that to the 12-row (4 Product Lines times 3 Tech Levels) Estimated Time table of how many minutes, and consequently dollars, should have been expended per call by Product Line and/or by Tech Level.

Fortunately, you once sent a basket of exotic fruit from the country where you are currently assigned to the chief systems analyst in the home office. Thus, having compiled the data into the tables, you feel you can ask him to create queries to help you analyze the data. He distilled the data into 5 queries he thinks would be helpful. (The other queries were necessary to build the work-in-process to create the finished goods of the 5 queries.)

(Query-based approaches to analyzing transaction data will become more common as businesses take advantage of the wealth of data available to them for solving business problems and exploiting new opportunities. And accountants will prepare queries to analyze transaction-level data to develop insights about business operations.)

I dont believe you need to create or modify any tables, queries, forms, or reports for this assignment. Thats why I didnt feel you needed much time for it. In fact, for this assignment, you will simply email me a Word document.

You will be graded on your critical thinking skills in solving the warranty service center managers problems. Remember that you have no control over what or how many of which products are produced or sold, nor their quality. The new customers calling may be simply having some problems setting up their computers, unrelated to product quality. You dont know the gross margins on the products and therefore have no status for suggesting a change in product design or mix.

All you can control is the current employees at the call center. Additional training may or may not produce cost savings greater than training costs, and its too general a suggestion anyway. Your suggestions must be based on specifically on a careful analysis of what you can infer from the particular info the queries reveal.

Do the overruns seem to be caused mainly by certain product(s) or certain tech level(s)? Perhaps there is a creative way to assign certain tech levels (which tech levels?) to certain products (which products?) in a manner with the greatest likelihood of reducing total actual minutes.

In support of your narrative, you will copy & paste relevant queries from Access into your Word doc. How is this done? Go to Access, with all tables & queries closed, click once on the query you want to copy on the list of objects in the left panel to highlight/select it. Then click Copy. Then go to your Word doc, and with the cursor placed where you want the query to appear in the document, click paste.

SumOfMinutes 1 - Difference in Minutes by Product ID ProductID - SumOfEstwa - SumOfMinutes - Difference 1 1200 1767 -567 2 1200 1162 38 3 1200 1155 45 4 1200 1783 -583 2 - Difference in Minutes by Tech Level TechLevel - SumOfEstWarmin 2400 2 1600 3 800 - Difference 3140 -740 1766 -166 961 -161 Record: 1 of 3 No Filter Search Record: K + 1 of 4 No Filter Search X 3. Difference in Minutes ProductID TechLevel 1 Difference - -589 1 15 4 - Difference in Dollars by Product ID ProductID Est Dollars Actual Dollars 1 $2,000.00 $2,538.00 2 $2,000.00 $2,225,00 3 $2,000.00 $2,231.00 4 $2,000.00 $2,561.00 1 Difference, ($538.00) ($225.00) ($231.00) ($561.00) 2 7 218 -97 -83 2 2 3 SumOfEstWe Sum OfMinu - 1 600 1189 2 400 385 3 200 193 1 600 382 2 400 497 3 200 283 1 600 373 2 400 488 3 200 294 1 600 1196 2 400 396 3 200 191 227 -88 3 3 4 4 -94 -596 4 Record: 1 of 4 No Filter Search 4 4 9 Record: 6 of 12 No Filter Search O X 5 - Difference in Dollars by Tech Level TechLevel Est Dollars - Actual Dollars - 1 $2,400.00 $3,140.00 2 $3,200.00 $3,532.00 3 $2,400.00 $2,883.00 Difference ($740.00) ($332.00) ($483.00) SumOfMinutes 1 - Difference in Minutes by Product ID ProductID - SumOfEstwa - SumOfMinutes - Difference 1 1200 1767 -567 2 1200 1162 38 3 1200 1155 45 4 1200 1783 -583 2 - Difference in Minutes by Tech Level TechLevel - SumOfEstWarmin 2400 2 1600 3 800 - Difference 3140 -740 1766 -166 961 -161 Record: 1 of 3 No Filter Search Record: K + 1 of 4 No Filter Search X 3. Difference in Minutes ProductID TechLevel 1 Difference - -589 1 15 4 - Difference in Dollars by Product ID ProductID Est Dollars Actual Dollars 1 $2,000.00 $2,538.00 2 $2,000.00 $2,225,00 3 $2,000.00 $2,231.00 4 $2,000.00 $2,561.00 1 Difference, ($538.00) ($225.00) ($231.00) ($561.00) 2 7 218 -97 -83 2 2 3 SumOfEstWe Sum OfMinu - 1 600 1189 2 400 385 3 200 193 1 600 382 2 400 497 3 200 283 1 600 373 2 400 488 3 200 294 1 600 1196 2 400 396 3 200 191 227 -88 3 3 4 4 -94 -596 4 Record: 1 of 4 No Filter Search 4 4 9 Record: 6 of 12 No Filter Search O X 5 - Difference in Dollars by Tech Level TechLevel Est Dollars - Actual Dollars - 1 $2,400.00 $3,140.00 2 $3,200.00 $3,532.00 3 $2,400.00 $2,883.00 Difference ($740.00) ($332.00) ($483.00)