Excel Timed Tutorial #2

4. (TCOs 1, 3, 5) You’ve just joined the staff of the XYZ Manufacturing Company (XYZ, for short). XYZ manufactures only one product, the gizmo. It comes in two sizes, the mini-gizmo and the magna-gizmo. Both are difficult to manufacture, and consequently, the company closely monitors rejected units. The company has three locations each of which produces both the mini and the magna-gizmos. You are automating the weekly production reports so that you can easily calculate total production for the entire company each week.
The mini-gizmo is priced at $5 per unit. The magna-gizmo is priced at $8 per unit. The unit cost for a reject mini-gizmo is $2. The cost for a reject magna-gizmo is $3. Respond fully to the following questions regarding this task:
A – (TCO 1) You have decided to develop a worksheet for each manufacturing facility manager to submit each week. It should show total production and revenue, and total scrap and cost for each product for the week. If a facility drops below 5,000 units of production on the mini-gizmo, the production number should be highlighted in yellow. Explain how you will set this up so that the managers enter only the number of units for each product’s production and rejects.
B – (TCO 5) The managers will e-mail their weekly reports to you on Monday of the following week. You will then produce the summary report. Explain the process for doing this. Give a sample formula to total the number of magna-gizmos produced for the entire company for the week.
C – (TCO 5) Each week, you will present the combined report to your boss, who wants to see both the summary and the individual sheets for each location. You want to add a custom footer with the date and the company name to each page. What is the easiest way to do this? Explain the process.
D – (TCO 3) Describe the graph that you would create to accompany the report in Part C. Include formatting considerations to make important facts stand out. (Points : 40)

5. (TCOs 2, 10) Congratulations on your new position as loan officer at the Commercial Bank and Trust Company. You are dedicated to building long-term relationships with your clients, and you want to be able to answer their questions quickly if they call after obtaining a loan. The bank’s system is a little slow, so you have started your own list of information frequently needed by your client. You are aware that it is extremely important that the customer’s information be protected.
You process automobile loans for the bank’s customers. The interest rate varies depending on the customer’s credit score. Once you know the credit score, you use the associated rate to calculate the loan payment. You then store the customer’s information including the payment date and the payment amount in your list for quick reference. Respond fully to the following questions regarding this task:
A – (TCO 2) You have set up an Excel worksheet to determine the loan payment. It is set up so that you enter the credit score, and it finds and returns the correct interest rate. Then, you enter the interest rate and other required information to get the actual payment. Describe the Excel tools used for these tasks and the information required to obtain the payment.
B – (TCO 10) You know that your list of information on the loans will quickly become very long. Also, you want to be sure that you are not repeating information, and that you can t easily give out wrong information. You would like to be able to view one single record at a time to keep this from happening. A friend has suggested that you build your list in Access. What would be the benefit of doing this, particularly in the banking environment?
C – (TCO 10) You want to use the Access form wizard to create an input form for your list. Describe the process of creating the form, and the special tools you might use to make it easier to use and less prone to errors.
D – (TCO 10) You have decided that you would like to contact your customers by phone at least once a year to maintain the relationship. Explain how you would obtain a list of loans with anniversary dates in a particular month, and then print the information for your use in making these calls. Be specific! (Points : 40)