Enterprise Sales Support Making Up for Missing Features Christina Lovan works as an independent…
Enterprise Sales Support
Making Up for Missing Features
Christina Lovan works as an independent agent for Farmers Insurance ( www.farmers.com) in O’Fallon, Illinois. As an independent agent, she manages her own office and earns her income entirely from policy sales. Farmers Insurance supports Christina’s efforts with training and access to a Web-based suite of applications that help her manage her business. This extranet includes contact, customer, and policy management systems, as well as corporate communications. The system also includes a feedback feature agents may use to report software problems or make suggestions regarding future software enhancements.
Christina enjoys working with people to help them solve their problems. However, to meet these people, Christina needs leads. She purchases leads from Farmers Insurance, Net Quote (www.netquote.com), InsureMe ( www.insureme. com ), AllQuotes ( www. allquotesinsurance.com ), and Crisscross (www.criscross. org ), a database of local residents and businesses. She asks each client for three referrals at each meeting. She is a member of Business Networking International (www.bni.com ), and she is an active member of the local chamber of commerce.
Although the software tools provided by Farmers Insurance allow Christina to track prospective customers, they do not measure information quality. Lead information is expensive to purchase or solicit. Christina needs to know the best way to spend her time and money. Until Farmers provides a system update to accommodate this need, Christina has developed her own dataquality evaluation tools using Microsoft Excel.
With a few basic Excel skills, we can duplicate Christina’s efforts. Download and save “Insurance.xls” from the MIS 10e OLC and use it to complete the following exercises. Note that the data providers used in this exercise are real, but the data in this spreadsheet are randomly generated.
a. Pending sales need Christina’s immediate attention. Help her spot these opportunities by automatically highlighting them. Select the “Sales Leads” spreadsheet and use Excel’s Conditional Formatting feature to set the cell shading color of each Status cell (Life, Auto, Home, Health) to green if the status is “Sale” (S) and red if the status is “Pending” (P).
b. Select the “Cover Sheet” spreadsheet. In cell C2, use the COUNTA function to count the total number of sales leads in the Sales Leads sheet. In cells C5 through C8, use the COUNTIF function to count the number of sales made for each type of insurance. In cells D5 through D8, divide the sales made by the number of leads to calculate the percentage of sales. Write these formulas so they calculate the correct answers even when Christina later adds more records to the list. Which product has the highest sales rate?
c. When rating the quality of her information, Christina counts whether or not a lead resulted in at least one sale. Using the IF and the OR functions, write a formula that will display “1” when at least one sale was made to a lead and “0” if the lead generated no sales. d. Ultimately, Christina needs to know which lead source results in the most sales. Create a pivot table to average the data in the “Sales” for each “Source.” Format this result as a percentage. Because a 1 indicates at least one sale and a 0 indicates no sales, the average per source indicates each source’s closing rate. Which source had the highest closing rate? Which source had the lowest closing rate?