1. (TCO 9, 2, 3, 7) You have worked for an electronics company for the past three years. You have been assigned the responsibility to create a workbook presenting profits and losses at the company's end of year meeting. It is critical for the workbook to include a documentation sheet along with sheets indicating profits and losses from four locations (i.e., Chicago, Dallas, Sacramento, and Tampa Bay). Given this information discuss your approach to address each question below. A - The final analysis includes profit and losses, What If analysis, and Scenarios. What types of recommendations could you provide in the documentation sheet based on this information? Also, what approach could you use to summarize all four locations in one sheet? B - You will need to customize the spreadsheet so organization is critical. What approach would you use to ensure the workbook is setup correctly for your presentation? C - There are many ways to depict the yearly trends for each location. Please explain at least two types of charts you could use to present yearly profits and losses? D - You have to rely on a few employees within the organization to complete your workbook. To do this the workbook has to be sent electronically to each employee. How would you protect the integrity of the formulas and structure if the workbook is sent to several employees to enter data? Also, what approach would you use to track the revisions sent by each employee. (Points: 40) 2. (TCO 4, 8, 7, 6) You have been tasked with planning a 100 year company anniversary/reunion for the company where you work. The HR department has an EXCEL spreadsheet that lists all current and prior employees in good standing with the company. The list includes email and physical mailing addresses for all employees who the HR department believes to be current and accurate. There is also a code to indicate whether the employee is currently working or retired. If they are currently working the code is Work for working and if they are retired it is Retr for retired. The list also includes a number of vendors and large customers who will be invited as well as employees. The codes for these groups are Vend and Cust respectively. In addition, you want to send out "courtesy" invitations to certain people that have been identified by HR for various reasons as not expected to attend. You want to use different phrasing on those invitations. HR has already provided a code for these people in addition to the other coding this code is Cour. Respond fully to the following questions regarding this task: A - Explain how you will use Excel to find in your list the people you are targeting for courtesy invitations. Be specific. B - You have used your computer skills to develop a beautiful set of Invitations. They include custom invitations for current employees, former employees, vendors, large customers and a Courtesy Invitation, you intend to mail an invitation to each group as appropriate. However, you dread hand addressing the envelopes and customizing the invitations. Explain how you might use Excel to make this go faster. C - Management has indicated that they want to make this a yearlong celebration with a number of similar events located at branch offices around the country. Explain how you might use Excel to automate the process of separating your list as you did in Part A. D - Your list is extensive, and mailing letters is expensive. Cost is $15 for every 50 letters mailed. You want to gauge the impact on your income statement of mailing 50, 100, 200, and 400 letters. Explain how you might evaluate this using your Excel tools. (Points: 40) 3. (TCO 1, 2, 4) John owns a company that creates small kitchen spice cabinets. You are his new office employee and he has asked you to create a spreadsheet that will help him calculate the expense associated with selling these cabinets. He has two employees that build these cabinets on site. The costs of the spice cabinets are 10.00 for a small cabinet and $20.00 for a large cabinet. John pays his two employees $3.00 per small cabinet and $4.00 per large cabinet. Respond to the following questions in regard to this task: A -Explain the formatting and design of this worksheet to include the title(s), column headings, and formulas to calculate the cost for John to produce each size spice cabinet. Discuss how you would show John the workings of this spreadsheet so that he understands it completely in your absence. B -John gives a discount on cabinets if you buy 2 or more in a single order. Explain how the spreadsheet will be set up to reflect this discount option? C -You want to set up the worksheet to display the color of green in the cell where a discount is applied. Please explain the process you will use to make sure this discount is applied automatically. D -John asks you to explain to him how much more money would he make if he sold more smaller cabinets than large ones John also wants to know how many cabinets he will need to sell to make a 20% profit? Explain how you would show John these different scenarios so that he understands it. (Points: 40) 4. (TCO 1, 5, 3) You've recently been promoted to manager assistant at LMO Production Company (LMO, for short). LMO manufactures only one product the gadget. It comes in three sizes, the mini-gadget, the micro-gadget and the magna-gadget. All three are difficult to manufacture, and consequently, the company closely monitors rejected units. The company has four locations, each of which produces all three of the gadget line of products. Your new assignment is to automate the weekly production reports so that you can easily calculate total production for the entire company each week. The mini-gadget is priced at $2.25 per unit. The micro-gadget is priced at $5.75 per unit. The magna-gizmo is priced at $7.00 per unit. The unit cost for all reject gadgets is $1.75. Respond fully to the following questions regarding this task: A - For your first task in this new position, you have been ask to develop a worksheet for each manufacturing facility manager to submit each month. It should show by week, the total production and revenue, and total scrap and cost for each product. If the number of reject units for a product exceeds 5 percent of the production of that unit, the number of rejects should show in red font. 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 - The managers will e-mail their weekly reports to you at close of business on Friday of each week. You will then produce the summary report for the first Monday of the month meeting. Explain the process for doing this. Give a sample formula to total the number of rejects produced by the entire company in a week. C - Each month, 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 header with the date and company name to each page. What is the easiest way to do this? Explain the process. D - Describe the graph that you would create to accompany the report in Part C. Your manager expects the report to be professionally formatted so that they are able to later share it with their upper managers once you present it to them for review. Include what professional formatting considerations you will use to make important facts stand out. (Points: 40) 5. (TCO 2, 10) You are employed by Investment and Savings Company as an investment specialist. The bank has begun a new marketing campaign aimed at account holders with substantial balances in their accounts. The goal of the campaign is to provide the select customers with investment incentives in an effort to grow the brokerage operations of the bank. As an investment specialist you are dedicated in building long-term relationships with your clients, and you want to be able to answer their questions quickly if they call after receiving the promotional information. You process certificate of deposits (CDs) for the bank's preferred customers. The interest rate varies depending on the term of the deposit. The bank issues CDs for 12, 18, 36, 48, and 60 month terms. Once you know the term, you use the associated rate to calculate the estimated interest earned and create a comparison for your customers. Respond fully to the following questions regarding this task: A - You have set up an Excel worksheet to determine the interest earned at the end of each term based on the customers deposit amount. It is set up so that you enter the amount of deposit, and it finds and returns the correct interest payment for each term at maturity. Based on this you are able to quickly tell the customer the difference in investment gain for each term. Describe the Excel tools used for these tasks, and the information required to obtain the interest payment for each term. B - You would like to build a list of customers who have opened a CD. You know that this list will grow over time. However, you would like to start by adding the customer's personal information and CD account information. You want to be able to pull up a customer's account information by either their home address, or by phone number. Although this can be done using Excel, your IT department has recommended using a database such as Microsoft Access. What would be the benefit of doing this with a database instead of a spreadsheet and how would it meet your needs? C -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 - You have decided that you would like to be able to pull a list of your clients with CD's due to mature within the next 30 days and attempt to get them to renew the term. Explain how you would obtain a list of CDs with maturity dates due in the next 30 days, and then print the information for your use in making these calls. (Points: 40)