Instructions

Please read through the case study and construct an Excel workbook that contains your responses to the questions asked in

Part $1$ and Part $2.$ A video and Excel template are provided to help assist you on Part $1$ you will want to be sure to view and

download these. Once this part is completed, use your knowledge of CVP and Excel to construct a worksheet that addresses

Part $2.$

Part $1$

Benson, Inc. manufactures and sells camping tents. They are considering the production of an additional type of tent, the

Yukon, which will be sold for $$250.$ To produce the Yukon and its carrying case, the following materials are needed:

$1.475$ square feet of nylon material

$2.40$ feet of zippers

$3.85$ feet of titanium$-$based rods

Currently, Benson has contracts with its suppliers to purchase nylon material for $$0\mathrm{.}20/$ square foot, zippers for $$0\mathrm{.}15/$ foot,

and titanium mix for $$3\mathrm{.}50/$ pound. Benson adds water to the titanium mix and runs it through a machine to produce the

rods for the tent. On average, it takes $2\mathrm{.}5$ pounds of the titanium mix to produce $10$ feet of rods. Benson would need to

purchase an additional machine, which can be purchased for $$300,000$ and would have the capacity to manufacture $800,000$

feet of rods per year. It is estimated to have a useful life of $20$ years.

As part of the manufacturing process, Benson will hire four full$-$time line workers $($one for cutting, one for sewing, one for rod

assembly, and one for painting$).$ Each of the line workers are paid hourly at a rate of $$12/$ hour. Benson believes he can get

by with four line workers as long as production of the Yukon is less than $7,500$ tents $/$ year. At that point, he will need to add

two full$-$time line workers. Benson is open $2,100$ hours per year.

Additionally, Benson plans to hire a production supervisor and a quality control supervisor as part of the manufacturing

process. They will be paid on salary at $$55,000$ and $$62,000,$ respectively. For the remainder of the other manufacturing

costs, an Excel regression was calculated using number of tents sold as the independent variable. Here are the results:

$1.$ Insurance on the facility $=$ R$2=0\mathrm{.}95,$ y$-$int $=7,500,$ slope $=1\mathrm{.}2$

$2.$ Utilities on the facility $=$ R$2=0\mathrm{.}72,$ y$-$int $=3,850,$ slope $=6\mathrm{.}75$

Assume there is a flat income tax of $30\%.$

Questions for Part $1$:

A$.$ Please find the after$-$tax equation assuming sales of less than $7,500$ annually? What would it be for a volume of $9,000$

tents?

B$.$ What is the break$-$even point in tents? In sales dollars?

C$.$ What is his after$-$tax profit if he sells $3,700$ tents in year one? $7,500$ tents in year one?

D$.$ If he could get a $5\%$ discount on the purchase of nylon material, what would be his after$-$tax profit at a sales volume

of $7,500?$

E$.$ If his line workers demanded a $$0\mathrm{.}50/$ hour raise, what would happen to his break$-$even point in units?

F$.$ If he wants to have an after$-$tax profit of $$300,000$ while selling $7,500$ tents, what should his sales price be$?$

Part $2$ Additional information for Benson Tents

Now also assume that Benson produces two other types of tents, the Denali and the Rainier, which are sold for $$275$ and

$$280,$ respectively. In the next year, Benson expects to sell $2,500$ of the Yukon, $4,000$ of the Denali and $1,000$ of the Rainier.

Here are the materials needed to produce each other

Ft$.$ of nylon Ft$.$ of zippers Ft$.$ of rods

Denali $5254895$

Rainier $60055110$

The following is applicable to fixed costs:

$1.$ Benson needs two line workers for every $3,750$ tents produced

$2.$ The machine producing the rods has a capacity of $9,411.$ Any additional machines purchased will have the exact same

capacity and cost.

$3.$ The production supervisor has the ability to oversee $8$ line workers. Any additional line workers will require the hire

of assistant supervisor $($salary of $$45,000)$

$4.$ The quality control supervisor can oversee the production of $18,822$ tents. After that, another supervisor will need to

be hired at a salary of $$62,000.$

Questions for Part $2$:

G$.$ What is the break$-$even point in units $($broken out by tent type$)$ assuming a production of less than $7,500$ tents?

H$.$ What is the after$-$tax profit of selling $10,000$ total tents $($assuming a constant sales mix$)?$