You work for the CFO of the Higgs Corporation, the cellphone company whose database you have worked with in Excel, and the CEO has asked the CFO for her opinion on a number of his business ideas. You are to advise the CFO about how to best answer the CEOs questions and proposals using you Excel wizardry and FIN skills.

(It might help to re-read the first page or two of the Excel instructions -- the part that goes over the background of the fictional company, Higgs Corp., for our fictional data.)

1. Your CEO has been out on the road talking to clients, and it sounds like one or two of the customers with commercial accounts (i.e. accounts with higher premiums than typical levels, usually in the thousands of dollars) have been complaining a bit about their interactions with your companys commercial account managers. Because of this, your CEO is worried that the commercial customers have been cancelling at high levels when compared with non-commercial or normal customers. Unfortunately, your company doesnt have any kind of report that breaks out cancellation rates by normal vs. commercial customers. Can you quickly put something together that can tell the CEO whether or not he should be worried about commercial customers cancelling more often than normal customers?

2. The CEO has decided to start a direct mail program to try and prevent current customers from cancelling their policies. Each piece of direct mail costs the company $5 (these are very fancy pieces of mail), and the direct mail campaign is likely to result in about 10% fewer cancelling customers. However, you dont know in advance which customers are going to cancel, so the CEO wants to send the mail to everybody. Using data from the state of Minnesota, can you tell the CEO whether this is this a smart use of money in the current year and beyond? Instead of blanketing all of your customers with direct mail, can you think of a smarter way to maximize the value of this customer-saving mail campaign?

3. The data you have been given deals only with customer sales revenue. Right now, each customer (both new and renewing) costs the company $150 per month. You could therefore think of monthly cash flow from each customer as being sales revenue minus the $150 cost.

With this in mind, the CEO notices that each new customer loses the company money, since typically new, non-corporate customers only have to pay an introductory rate of $50 or $100 (lets say $75, on average) before their rate bumps up to a higher amount (lets say $250, on average) in the next month.His solution is that new customers should have to pay the same amount (lets say $250, on average) as renewing customers.

Your boss, the CFO, is worried that this will hurt your business, since the low introductory rate is part of how you attract new customers to your business (and the higher intro price is likely to scare off some potential new customers). Using data on Illinois customers, how can you convince the CEO that even though customers lose money at first, taking on these customers at a $75 introductory rate is still a positive NPV project for the firm? (You can assume that Higgs Corporation using a 15% annual Cost of Capital for discounting cash flows and project evaluation and a 35% corporate tax rate, if you think that info might be of any use in your analysis)

4. The CEO is still not convinced by your phenomenal analysis for question 3. So you crunched some numbers for Illinois, and it looks like raising average new customer premium to $250 per new customer per month will result in an 80% decline in the number of new customers. Will this change be a good or bad thing for the companys current year Net Customer Revenue? How would these changes potentially affect your projections for business one year from now? What can you show the CEO to convince them?

5. Your CEO is contemplating investing in a new computer that would allow you to better target potential customers in Illinois. While this new computer would cost $25,000, it would allow you to get an additional 100 new customers in Illinois every year for five years. (For simplicity, assume that you get these customers at the beginning of each year.) You could depreciate the cost of the new computer on a straight line basis over five years. It would require a working capital investment of $2,000, which could be released at the end of the project. The computer will have no salvage value at the end of the five year project. Assume that each customer costs the firm $150 per month as in question #3 above. How could you help the CEO make the decision about whether or not to purchase this computer?