Brianna Wilkins is the president and CEO of Solid Sound, a company that manufactures and sells a line of gear for the home entertainment market, including televisions, stereo systems, and home theater systems. Respected throughout the industry for bringing many high-quality, innovative products to market, Brianna is considering adding a new speaker system to her product offerings. The speaker market has changed dramatically over the past decade. Originally, highfidelity aficionados knew that to reproduce sound covering the fullest range of frequencies, a speaker system had to be large and heavy. The trouble was that such a system was too large for anything but the biggest rooms, and consumer were reluctant to spend thousands of dollars and give up valuable room space to get the excellent sound these speakers could reproduce. The trend has changed during the past several years. Consumers still want good sound, but they want it from smaller boxes. Brianna's R\&D team has been working on new wireless speaker system that would allow for multiple configurations, from a stand-alone single speaker, to the use of several types of speakers to simulate a home theater system, but at a lower cost and with more versatility. For example, you could disconnect one of the wireless speakers from your TV in the home theater environment and use it in the kitchen or outside, etc. Consumers and critics are thrilled with the music from early prototypes, claiming these speakers have the best balance of sound and size. Brianna is extremely encouraged by these early reviews, but wants analyze the financials before bringing this new system to market. After consulting with economists, market analysts, employees in her own company, and employees from other companies, Brianna is confident that revenues for these speakers in the first year would be around $6 million. She must also figure a small percentage of speakers will be damaged in transit or returned by dissatisfied customers. These returns and allowances (R\&As) are usually calculated as 2% of gross revenue; net revenue is the gross minus the R\&As. Brianna believes the first year labor costs will be $915,100; materials costs will be $915,350; and overhead costs will be $1,536,120. Additionally, materials costs will be $915,350; and overhead costs will be $1,536,120. Additionally, selling and administrative (S\&A) costs need to be included. These are harder to estimate, but the industry standard has been 18% of net revenue. Finally, the tax rate for profits is 30%, but if the company is operating at loss, then they pay no taxes. Brianna would like to create a spreadsheet model for the next four years of this new product. Her model must account for year-over-year increases in gross revenue, labor costs, material costs, and overhead costs. Again, these are difficult to estimate, but she thinks gross revenue will increase by 9% per year, labor costs by 4% per year, material costs by 6% per year, and overhead costs by 3% per year. However, she assumes the R\&A, S\&A, and tax rates will remain unchanged over the course of four years. In addition to calculating net profit for four years, she also must justify this decision to the company's board. She needs to find the NPV using a 5% discount rate. Tasks: 2. [5 points] Brianna would like to know at what point the NPV would hit $5 million, based on a change in the discount rate. Use Goal Seek (to adjust the discount rate) to find the point at which the NPV hits $5 million. - What is the discount rate at which this NPV occurs? Write the answer to this question somewhere (easy to find / clearly label) on your worksheet. - Then (after recording your answer): Make sure to reset the discount rate to the base value before moving on to Task 3 3. [15 points] Brianna is concerned about fluctuations in the gross revenue growth rate and the tax rate. Create a two-input Data Table for NPV, with the following ranges: gross revenue growth rate between 6% and 12% in 1% increments, and the tax rate between 28% and 34% in 1% increments