Homework answers / question archive / 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

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

Accounting

Share With

---

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 that 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 for 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 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 four years of this new product.

She wants to account for increases year-over-year for gross revenue, labor costs, material costs, and overhead costs in this model. Again, these are difficult to estimate, but she thinks that 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. Beyond calculating net profit for four years, she also must justify this decision to the company’s board. She would like to find the NPV using a 5% discount rate.

 

1a - Brianna would like to know at what point the NPV would hit $5 million, based on a change to the discount rate. Use Goal Seek 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) on your worksheet.

1b - Make sure to reset the discount rate to the base value, then move on to Task 3 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.