attached the files needed below
HomeHarbor, Inc. — Creating Something New August 2, 2021 It had been a busy summer for Bobbie Bertel and Sue Larson. While most of their MBA classmates had spent the last couple of months in paid internships, Bertel and Larson had focused all of their time and energy on building a business plan for their promising company, HomeHarbor, Inc. Now, with the summer winding down, the two friends felt tired and poor, but satisfied. Larson reviewed the files on her computer – product descriptions, market research, staffing estimates, development costs, competitive assessments, pilot program feedback, and interview notes with prospective customers. Gathering all of this information had taken virtually all of Bertel and Larson’s time since the spring. In fact, at that very moment Bertel was at an insurance industry conference in New York City gathering the last piece of crucial data – how much would companies pay for HomeHarbor’s product? Bertel had promised to call tonight after she left the conference. Larson was anxious to hear from her co-founder. Although most of the pieces to the puzzle were now in hand, Bertel and Larson still had to fit everything together into a credible forecast of the company’s operating cash flow. Larson glanced at her cell phone wondering what Bertel had learned. Overview of HomeHarbor Bertel and Larson met shortly after the start of classes in the first year of their MBA program. Although the two had dissimilar backgrounds – Bertel had worked in the insurance industry for several years while Larson had been in software product management – they had become fast friends. They realized they had mutual interests, including a desire to start their own company. Over the course of the first year of classes, they revisited the topic of starting a company several times and discussed a variety of ideas. Finally, they struck on one that interested them both – a data service that would allow insurance firms to make more informed underwriting decisions on newly or recently constructed homes. Background on the Home Insurance Market Homeowner’s insurance was first introduced into the United States in the early 1950’s. The early home insurance market was somewhat ad-hoc, with little standardization of coverage or policies across insurance providers. In the early 1970’s the industry moved toward greater standardization in an effort to make it easier for consumers to purchase adequate levels of insurance for their homes. Despite these efforts, an estimated 60% of homes in the United States are under-insured by an average of 17%.1 In the United States, homeowner’s insurance is regulated primarily at the state level. Historically, insurance providers use several factors in estimating homeowner’s insurance premiums, including the home’s location, the types of coverage, and the dollar amount of insurance. The last factor is based on the estimated cost to repair or rebuild the home, and is often poorly estimated. Rather than use detailed information about the construction characteristics of the insured home, many homeowners and insurers instead rely on rough estimates of the cost to rebuild based on square footage. Such estimates are rife with error and result in (1) homeowners being either under- or over- insured and (2) insurance providers either overcharging or undercharging for coverage. HomeHarbor provides value by reducing or even eliminating these inefficiencies. HomeHarbor’s Product and Target Market HomeHarbor’s data service helps homeowners and insurance companies make informed decisions about the appropriate insurance coverage to obtain and premiums to charge. Bertel and Larson determined that there is an abundance of data on a home’s construction materials, design, and features that are maintained by local government agencies, taxing authorities and building departments. Those agencies are increasingly digitizing this data, particularly for homes that are less than 10 years old. HomeHarbor’s service integrates this data with other useful information, including building material costs and local contractor costs, and presents it to users in an intuitive interface with built-in analytical tools. This allows insurance providers to develop much better pricing models and gives homeowners a realistic basis on which to decide how much coverage they need to obtain. Bertel and Larson conducted a pilot study over the summer based in Ithaca, NY. This pilot accomplished two goals. First, it established that the HomeHarbor technology was feasible. Second, it established a legal framework that allows HomeHarbor to provide homeowners and insurance providers with access to this data through the HomeHarbor secure website. As a concession to the local government authorities, individuals and insurance providers can only access the data on a home if the home’s legal owner grants such access. Since the HomeHarbor service helps the home’s legal owner and their designated insurance providers, homeowners that participated in the pilot rarely refused to grant insurance providers with access to the data. The insurer pays for the HomeHarbor service in the form of an annual licensing fee for each county in which they operate. In addition to paying the licensing fee, the insurer must receive access to the data on the home from the homeowner. The homeowner can access the data free of charge. A large portion of HomeHarbor’s demand during the pilot came directly from homeowners asking their insurance providers to use the service. This helped to drive organic demand for HomeHarbor’s service and led many insurance providers to view the HomeHarbor service as a cost of sales rather than as an operating cost. Many companies, including insurers, are more willing to spend money on services that they perceive as increasing sales rather than merely increasing costs. Bertel and Larson conducted the Ithaca pilot on a beta version of the HomeHarbor technology. With the pilot complete, the founders plan to update the technology to provide users with more features, including the ability to download data, run and store insurance coverage scenarios, and dynamically evaluate different insurance pricing models. They will eventually allow homeowners to use the website to pay their insurance, file insurance claims, pay property taxes, contest property tax changes, and notify the appropriate agencies of any changes to the property. Potential Partnership Admittedly, Larson felt a little nervous about the uncertainty surrounding their personal income. She recently discussed the HomeHarbor business model with a family friend who was also an angel investor. He offered to join the business as a limited partner for one year. He would guarantee an annual salary of $75,000 for each founder, regardless of the firm’s actual performance. In return, he would keep 85% of any cash flow that the company generated beyond $300,000 per year. The partnership payments would work as follows: · If HomeHarbor did not generate at least $150,000 in cash flow, the partner would pay enough to HomeHarbor so that there is $150,000 in cash flow (i.e. enough to cover all of the residual costs including a $75,000 salary for each founder). · If HomeHarbor generates between $150,000 and $300,000 in cash flow, there is no exchange of funds between the company and the partner. · If HomeHarbor generates more than $300,000 in cash flow, HomeHarbor will pay the partner 85% of anything above $300,000. For example, if HomeHarbor generates -$100,000 in cash flow (i.e. it loses $100,000 in cash), then the partner will pay $250,000. If HomeHarbor generates +$100,000 in cash flow, then the partner will pay $50,000. If HomeHarbor generates $200,000 in cash flow, then the partner pays nothing. If HomeHarbor generates $700,000, then HomeHarbor pays the partner $340,000 (i.e. 85% of $400,000, the cash flow above $300,000). Next Steps Bertel and Larson already have offers to return to their previous employers. However, they have decided that they will both stay with their nascent startup if they believe that there is at least a 75% chance that the company will be cash flow positive in its first year, excluding their own salaries and without the partnership. As Larson considered her financial future, the ring of her cell phone interrupted her reverie. Bobbie was on the line with good news from the conference. After meeting and speaking with several dozen insurance company representatives, Bertel estimated that companies would be willing to pay between $2,000 and $4,000 per county per year for the HomeHarbor offering. After some discussion, the two friends agreed that the final negotiated price would likely vary for each company and follow a continuous uniform distribution in this range. Larson hung up the phone. Now that she had a good range for pricing, she felt that she could develop a reasonable cash flow forecast for the company’s first year of operations. She also felt that she could model whether it was in their best interest to take on the limited partner. Settling into her chair, Larson went to work. Simulation Assignment Instructions: Read the HomeHarbor case. You must conduct a simulation analysis on the decision faced by Bertel and Larson. Your submission should consist of a .zip file that contains (1) an Excel file with your simulation analysis and (2) an executive summary in either Microsoft Word or PDF. You must conduct your analysis using native Excel, not in a different tool such as @Risk. Your Excel file should consist of the following: 1. A single worksheet named “Simulation” which contains 5,000 simulations of HomeHarbor’s cash flow available to the founders in the first year. 2. A single worksheet named “Results” which contains all of the calculations and graphs to answer part 3 (below) based on your simulations. You should strive to make your paper clear, relevant, insightful, and concise. The executive summary should be 800 – 1200 words long. This is approximately 2-3 pages (excluding any results tables) using 12 point font and 1-inch margins. This length is a guideline... you may go over or under as needed, if you address all the points in the case effectively. Your executive summary should address the following: 1. A succinct outline of the facts of the case, including an assessment of the business issue and why it is important. 2. Present the formula for HomeHarbor’s forecasted cash flow (excluding the angel investment) and provide a description of all of the variables that you are employing in your simulation of the cash flow. 3. Provide the following information based on your 5,000 simulations. For the sake of simplicity, base your analysis on pre-tax cash flows. For parts a) through e), ignore the partnership opportunity. For parts f) and g), assume the founders accept the partnership opportunity. 1. A histogram that shows the shape of the probability distribution of HomeHarbor’s cash flow in the first year if Bertel and Larson start the company. Structure your bins sizes so the histogram provides useful information in this context (you should