Bayside Logging

You are an owner of a logging company looking to bid on the plots you plan to cut this year. You need to perform some analysis of the risk, reward, and break$-$even points based on how many plots you purchase, the average production per plot, and the cost you pay per plot. Historically, the distribution of the type of trees on these plots are consistent and the price for each type of tree have been estimated for the season.

Range Names

You need to configure the range of input data to simplify the creation of the formulas used by Excel$$s Analysis tools.

a$.$ Open Assignment $2$ data file and save as Assignment $2\_$LastFirst.

b$.$ Open the Breakdown worksheet and create from Selection new named ranges for the existing inputs in cells B$2$:C$14.$

c$.$ Assign the name Number$\_$of$\_$Plots to cell C$3.$

d$.$ Edit the formula in cell C$14$ to use the named ranges instead of the cell references.

Goal Seek

You had to purchase new machinery to harvest the wood for this season. You need to determine how many board feet you need to average to cover the cost of the new machinery.

e$.$ Use Goal Seek to determine what the Average Board Feet must be if you bid on $200$ plots at $$150,000$ each to cover your $$2\mathrm{.}5$ million machinery cost.

f$.$ Answer Question $1$ on the Questions worksheet.

One$-$Variable Data Table

You need to perform some analysis on Gross and Net Profit based on different averages of board feet per plot.

g$.$ Complete a series of substitutions starting in cell F$4,$ starting at $10000$ in increments of $500$ and a max of $13000,$ going down vertically.

h$.$ Enter the references for Gross Profit and Net Profit, respectively, in cells G$3$ and H$3.$

i$.$ Complete the one$-$variable data table.

j$.$ Format the results to Accounting Number Format with Zero decimal places.

k$.$ Change cells G$4$ and H$4$ to display Gross Profit and Net Profit, respectively.

l$.$ Answer Question $2$ on the Questions worksheet.

Two$-$Variable Data Table

You need to look at how the number of plots and cost per plot can affect your net profit.

m$.$ Complete a series of substitutions starting in cell G$14,$ starting at $75$ in increments of $25$ for a max of $175,$ going across horizontally.

n$.$ Complete a series of substitutions starting at cell F$15,$ starting at $55000$ in increments of $15000$ for a max of $145000,$ going down vertically.

o$.$ Complete the two$-$variable data table by adding the reference for Net Profit in cell F$14.$

p$.$ Format the results to Accounting Number Format with Zero decimal places.

q$.$ Change cell F$14$ to display Cost per Plot.

r$.$ Merge and center cells G$13$:K$13$ and add a heading Number of Plots.

s$.$ Answer Question $3$ on the Questions worksheet.

Scenario Manager

Prices for each wood can change, so you need to perform a what$-$if analysis if the prices move up or down from the current estimates.

t$.$ Create a scenario named Best Case using the following pricing:

$$ Pine $=$ $$11\mathrm{.}00$

$$ Poplar $=$ $$13\mathrm{.}00$

$$ Spruce $=$ $$12\mathrm{.}00$

u$.$ Create a scenario named Most Likely using the following pricing:

$$ Pine $=$ $$12\mathrm{.}00$

$$ Poplar $=$ $$14\mathrm{.}00$

$$ Spruce $=$ $$13\mathrm{.}00$

v$.$ Create a scenario named Worst Case using the following pricing:

$$ Pine $=$ $$8\mathrm{.}00$

$$ Poplar $=$ $$10\mathrm{.}00$

$$ Spruce $=$ $$8\mathrm{.}00$

w$.$ Generate the Scenario Summary using Total Cost, Gross Profit, and Net Profit.

x$.$ Format the headings to display each name instead of the range names.

y$.$ Answer Question $4$ on the Questions worksheet.

Use Solver

You are trying to find the maximum profit based on a best$-$case scenario of number of plots purchased, cost per plot, and average board feet per plot.

z$.$ Use Solver on the Breakdown worksheet to set the objective to calculate the maximum Net Profit.

aa$.$ Use Plots, Cost Per Plot, and Average Board Feet as changing variable cells.

ab$.$ Add the following constraints:

$$ Plots $<=250($maximum number of plots to bid on$)$

$$ Cost per plot $>=50000($lowest winning bid from last year$)$

$$ Average Board Feet $<=12000($highest production from last year$)$

ac$.$ Solve the problem and generate the Answer report.

ad$.$ Answer Question $5$ on the Questions worksheet.

ae$.$ Save and close the file. Based on your instructor$$s directions, submit e$06\_$exam$\_$chap$\_$LastFirst. Plots $200$

Cost Per Plot $$1,00,000$

Average Board Feet $10000\mathrm{.}00$

Pine $\%9\%$

Pine Price $$12\mathrm{.}00$

Poplar $\%38\%$

Poplar Price $$14\mathrm{.}00$

Spruce $\%53\%$

Spruce Price $$13\mathrm{.}00$

Total Cost $$2,00,00,000\mathrm{.}00$

Gross Profit $$2,65,80,000\mathrm{.}00$

Net Profit $$65,80,000\mathrm{.}00$