BIT $3434$

CHAPTER $1$ HW

INSTRUCTIONS:

$$ Upload your completed Excel workbook to Canvas by the deadline.

o Late assignments are accepted within $24$ hours of the deadline and subject to a $10\%$

penalty.

$$ Name your Excel workbook using your last name $($ex: LastName$\_$C$1$HW$)$ OR if you are working

with a group use your group number $($ex: Group##$\_$C$1$HW$)$ and submit only one file per group.

$$ Your Excel document should contain three worksheets, labeled as outlined in the assignment

$($Task $1,$ Task $2,$ Task $3)$

$$ Good spreadsheet design guidelines apply! You will be graded not only for the correct results, but

the use of spreadsheet design guidelines, including but not limited to:

o Correct calculations

o Using cell references instead of hard$-$coded values in formulas

o Grouping similar components $($fixed inputs, variable inputs, outcomes, model structure;

should be clear and easy to find things$)$

o Layout and flow of information $($left$-$to$-$right, top$-$to$-$bottom$)$

o Clear labels

$$ You do not need to submit a spreadsheet sketch or hand$-$written model formulation $($as discussed

in Phase $1),$ but it is HIGHLY RECOMMENDED that you create a document such as this to ensure

you are on the right track. This seems like extra work, but almost always results in less time spent

building the model in Excel.

$$ Make sure that your model has the correct base$-$case input values and results!

$$ Use at least $2$ decimal places for any fractional value.

The Solid Sound Company

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, high$-$fidelity

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.

$2$

After consulting with economists, market analysts, employees in her own company, and employees

from other companies, Brianna is confident that gross revenue for these speakers in the first year

would be around $$6$ million. Brianna believes the first$-$year labor costs will be $$915,000,$ materials

costs will be $$900,000,$ and overhead costs will be $$1\mathrm{.}5$ million. Additionally, selling and administrative

$($S&A$)$ costs need to be included. These are harder to estimate, but the industry standard has been

$18\%$ of gross 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 growth rates 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 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.

Tasks:

$1.[80$ points$]$ On a worksheet labeled Task $1,$ Build the spreadsheet model to solve this problem.

$$ Make sure you follow the guidelines for good spreadsheet design including proper cell

referencing, good flow$/$layout$,$ etc. Your grade on this portion of the HW reflects not only on

having the correct calculations, but 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 alon