Please answer each question. Use Excel if needed

Projecting bookshelf costs at Woodworks

The Woodworks Company produces a variety of custom-designed wood furniture for its customers. One favorite item is a bookshelf, made from either cherry or oak. The company knows that wood prices and labor costs are likely to increase in the future.

Woodworks knows the amount of material and labor required for each type of bookshelf. They also know the cost per unit of material, the cost per unit of labor, and the projected annual increase of these costs.

Build a spreadsheet model that enables the company to experiment with the growth rates in wood and labor costs so that a manager can see, both numerically and graphically, how the costs of the bookshelves increase in the next few years.

Modeling Assignment 2

Question 1.

If the cost of labor does not increase over the next 6 years, how much less will the cherry bookshelf cost than what it would have cost if the labor cost did increase? By default, labor cost is projected to increase by 1.5% every year.

Answer:

Question 2.

If the projected annual increase in cost per board-foot of cherry and oak wood were swapped, and the projected annual increase in labor cost remained at 1.5%, which bookshelf would cost more in year 6: Cherry or Oak?

Answer:

Question 3.

What would the projected annual increase in cost per board-foot of oak wood need to be for the cost of oak bookshelves to equal the cost of cherry bookshelves in 6 years. Assume that the projected annual increase in cost per board-foot of cherry wood remains at 2.4% and the projected annual increase of labor cost remains 1.5%.

Answer:

Inputs
Requirements per bookshelf	Cherry	Oak
Board-feet required	30	30
Labor hours required	16	16
Costs of wood	Cherry	Oak
Current cost per board-foot	$5.50	$4.30
Projected annual increase	2.4%	1.7%
Cost of labor
Current cost per labor hour	$18.50
Projected annual increase	1.5%
Output (projected costs)