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 aria 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: 1. [80 points] Build the spreadsheet model to solve this problem. - Make sure you follow guidelines for good spreadsheet design including proper cell referencing, good flowllayout, etc. Your grade on this portion of the HW reflects obtaining the correct calculations AND also for following those guidelines 1. [ 80 points] Build the spreadsheet model to solve this problem. - Make sure you follow guidelines for good spreadsheet design including proper cell referencing, good flow/layout, etc. Your grade on this portion of the HW reflects obtaining the correct calculations AND also for following those guidelines and creating a well-designed and easy-to-use worksheet. - Make sure you carefully read the flow of information provided above to ensure correct calculations along the way (including the possibility of $0 taxes - even if that is not possible in this scenario yet). - I will not collect influence diagrams, written calculations, or other materials that can help with formulating the "black box" portion of the model, but please use these tools if necessary to facilitate your spreadsheet building process. - Use Excel functions to help you, as necessary (hint: Excel has a built-in NPV function). If you are not familiar with NPV in general, or how to use the Excel NPV function, see the Excelisms module or search online for the explanations. - For the sake of simplicity, place all values in $000 s on the spreadsheet. - We will revisit this problem on a future HW. Doing things correctly (especially referencing) now will save you time later in the semester. 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 cu'stomers. 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, selling and administrative (S\&A) costs need to he included These are harder to
