2. (TCO 4, 8, 7, 6) You own a small computer service company with only 2 employees. You provide different types of services to regular customers and companies. You have noticed an increase of demand for troubleshooting and support of mobile devices. However, you only support desktop, laptop, networking, and printing devices. Your current staff doesn't have the skills or experience to troubleshoot this type of devices such as mobile phones (iPhone, Android), tablets, or eBook readers. You have kept a log of each of these inquiries for "mobile device" troubleshooting and support. The list includes the type of device, issue, and customer's name. You feel this is an opportunity for your business and you want to make sure you can serve your customers well and expand your services. A - Explain how you will use Excel to find out how much support you have been inquired for mobile devices. Include how to identify the number of devices per customer. Be specific. B - You wish to survey your customers to find out what type of devices they own and their needs. To accomplish this you have created a survey and you must send it via mail to more than 50 customers. However, you dread hand addressing the envelopes. Explain how you might use Excel to make this go faster. C - If you get the results you expect to get from contacting your customers, you want to be able to do this every month. Explain how you might use Excel to automate the process of separating your list as you did in Part A. D - You are anticipating your man hours will increase by 20 percent over the next month for every 15 customers your support. You need to hire new employees so you want to gauge the impact on your workforce by including 30, 40, or 50 clients as part of your new strategy. 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 have just been hired as the staff accountant for the Angler’s Deep Sea Fishing Charter Inc. (ADSFCI). The company caters to customers who seek the thrill of catching large Ocean fish. ADSFCI has a fleet of three different types of fishing boats, which it charters for four, six and eight hour fishing expeditions. The first boat is a 20-foot Lake and Bay Flats Boat that rents for $400.00 for four hours, $600.00 for six hours and $800.00 for eight hours. The second is an Action Craft Coastal Bay 2310 that rents for $550.00 for four hours, $650.00 for six hours and $750.00 for eight hours. The third boat is a Yellowfin 32 Ft Center Console that rents for $800.00 for four hours, $900.00 for six hours and $1000.00 for eight hours. As the staff account, you will be responsible to keep the financial records for the number of weekly charters, type of boat rented, and the time at sea. You will calculate the hourly charge based on the hours logged and emailed to you by the Captain after each chartered trip. You will also calculate the weekly cost of maintenance for each of the boats separately to include fuel, oil, cleaning and insurance. The cost to provide routine maintenance on each boat is a fixed at: $5.85 for a gallon of fuel, each boat holds 50 gallons of fuel. $4.10 for a quart of oil, each boat holds 5 quarts of oil. $4.00 an hour for boat cleaning. $6.00 per trip insurance cost for the boat and crew. The ADSFCI is normally very busy during the week with charters going out several times daily and more often on weekends, which increases maintenance time and costs. You want to use Excel to track the revenues received from the charters and compare it to the cost of maintenance for profitability for each of the boats per charter on a weekly basis. Please respond in detail to the questions below to regarding this task: A - You have decided to develop a worksheet for each boat with the rental hours at sea and maintenance costs for submission each week to your boss. It should show total hours at sea, total revenue and total maintenance costs for each boat for the week. If the maintenance cost for a boat exceeds 4 percent of the income generated from that boat, the deficit should show in red font. B - Each Friday the Captains will email you their hours at sea. You will calculate the totals on each worksheet for each boat and then you will produce the summary report. Explain the process for doing this. Give a sample formula that will total the revenue received from the Action Craft Coastal Bay 2310 that was at sea for 17 hours, minus the maintenance cost for the week. The boat burned 17.5 gallons of fuel and used .25 quarts of oil and took 2.3 hours to clean. C - Each week, you will present the combined report to your boss, who wants to see both the summary and the individual sheets for each boat. You want to add a header with the date and your 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. Include formatting considerations 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)