Question

Create a spreadsheet to model the following: 1. [20pts] You are the product manager of a toy company thinking of launching a new product, the indoor fort building kit Links to an external site.. Your critics say these are just overpriced cardboard boxes, but you know this product will be successful and you seek out initial investors. In year 0 (right now), you will incur a cost of $4 million to build and staff a production factory. In year 1, you expect to sell 80,000 kits at a unit price of $25 each. The price of $25 will remain unchanged through years 1 to 5. Unit sales are expected to grow by the same percentage (g) each year. During years 1 to 5, you incur two types of costs: variable costs and fixed SG&A (selling, general, and administrative) costs. Each year, variable costs equal half of revenue. During year 1, SG&A costs equal 40% of revenue. This percentage is assumed to drop 2% per year, so during year 2, SG&A costs will equal 38% of revenue, and so on. Your goal is to have profits for years 0 to 5 sum to 0 (ignoring the time value of money). This will ensure that the $4M investment in year 0 is paid back by the end of year 5. What annual percentage growth rate g is needed to pay back the plant cost by the end of year 5?

Answer 1

To pay back the plant cost by the end of year 5, the profits for years 0 to 5 need to sum to 0. We can calculate the required annual growth rate by breaking down the costs and revenues for each year. By solving an equation that sums the profits for each year, we can determine the annual growth rate needed to achieve the desired result.

Step-by-step explanation:

To pay back the plant cost by the end of year 5, the profits for years 0 to 5 need to sum to 0. We can break down the costs and revenues for each year to calculate the required annual growth rate (g).

1. Year 0: Cost = $4 million
2. Year 1: Revenue = 80,000 kits × $25 per kit
   Variable costs = 50% of revenue
   SG&A costs = 40% of revenue
3. Years 2 to 5: Revenue = Revenue from previous year × (1 + g)
   Variable costs = 50% of revenue
   SG&A costs = SG&A costs from previous year × (1 - 2%)

We can use the above information to set up an equation to solve for the annual growth rate (g). By summing the profits for each year and setting it equal to 0, we can solve for g. Once we find g, we can determine the annual growth rate needed to pay back the plant cost by the end of year 5.