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.
Explain how you might use Excel to automate the process of separating your list as you did in Part A.
A - To find out how much support you have been inquired for mobile devices using Excel, you can follow these steps:
1. Open Excel and create a new worksheet.
2. Copy and paste the log of inquiries into the worksheet. Make sure each column represents the type of device, issue, and customer's name.
3. Use the "Filter" function in Excel to filter the data based on the type of device. In this case, you would filter for mobile devices.
4. Once the data is filtered, look at the total number of rows to see how many inquiries you have for mobile devices. This will give you an idea of the demand.
5. To identify the number of devices per customer, you can create a pivot table. Select the data and go to the "Insert" tab, then click on "PivotTable."
6. In the PivotTable Field List, drag the "Customer's Name" column to the "Rows" section and the "Type of Device" column to the "Values" section.
7. The pivot table will now show you the number of devices per customer. You can use this information to identify which customers have multiple devices and may require more support.
B - To use Excel to make addressing envelopes faster for the survey, you can use the Mail Merge feature. Here's how:
1. Create an Excel spreadsheet with two columns: one for the customer's name and one for their address.
2. Open a Word document and create the survey content, leaving placeholders for the customer's name and address.
3. Go to the "Mailings" tab in Word and click on "Start Mail Merge." Choose "Step by Step Mail Merge Wizard."
4. In the Mail Merge pane on the right side, select "Labels" as the document type.
5. Click on "Use an existing list" and browse for your Excel spreadsheet with customer names and addresses.
6. Click on "Next" and select the appropriate label size for your envelopes.
7. In the next step, click on "More Items" to insert the customer's name and address placeholders into the survey content.
8. Review the preview of the labels and make any necessary adjustments.
9. Finally, click on "Finish & Merge" and choose "Print Documents." This will generate the merged survey documents with the customer's name and address automatically inserted onto each envelope.
C - To automate the process of separating your list of inquiries every month, you can use Excel's "Filters" and "AutoFilter" features. Here's how:
1. Open your Excel worksheet with the log of inquiries.
2. Go to the "Data" tab and click on "Filter" or "AutoFilter," depending on your Excel version.
3. Excel will add drop-down arrows next to each column header.
4. Click on the arrow next to the "Type of Device" column and select the mobile device you want to filter for.
5. Excel will filter the data and only display the inquiries related to that device.
6. To save this filtered list, you can either copy and paste it into a new worksheet or use the "Save as" function to save it as a separate file.
7. You can repeat these steps every month to automate the process of separating your list of inquiries.
D - To evaluate the impact on your workforce based on different numbers of clients, you can use Excel to calculate the required man hours. Follow these steps:
1. Create an Excel worksheet and list the number of clients in one column (e.g., 30, 40, 50).
2. In the next column, calculate the estimated man hours using a formula. For example, if you anticipate a 20% increase in man hours for every 15 clients, you can use the formula "=CEILING(A2/15,1)*20%" (assuming the number of clients is in column A).
3. Extend the formula to the rest of the clients in the column.
4. In another column, calculate the total man hours by adding the original man hours required plus the additional man hours.
5. Use Excel's conditional formatting to highlight any increase in man hours that exceeds a certain threshold or limit you have set.
6. This will give you a visual representation of the impact on your workforce based on the number of clients, allowing you to gauge the resources needed for your new strategy.