The owner of Northern Ontario Logging is seeking to bid

The owner of Northern Ontario Logging is seeking to bid on plots she plans to cut this year. You have been asked to perform some analysis of the risk, reward, and break-even points based on how many plots she purchases, the average production (board feet) per plot, and the price she pays per plot. Historically, the distribution % of the type of trees on these plots is consistent and the price for each type of tree has been estimated for the season.

Each worksheet in the workbook contains the basic data described above. Using appropriate Excel tools/functions/formulas, you are required to perform the analysis specifically assigned to each worksheet, as indicated below.

1-The owner had to purchase new machinery to harvest the wood for this season. Determine what the Average Board Feet per Plot must be, if the owner bids on 200 plots at $10,000 each, so the Gross Profit matches the $250,000 cost of machinery

2-You need to perform an analysis of Total Revenue and Gross Profit based on different Averages of Board Feet per Plot.

A-Using an appropriate Table, show how Total Revenue and Gross Profit will change with changes in Average Board Feet per Plot, ranging from 1,000 to 1,300 in increments of 30.

B-Apply appropriate formatting to the Table.

C-Highlight in yellow color, Gross Profit closest to $1.2 million. Also, highlight the corresponding Average Board Feet per Plot and the Total Revenue.

3- A- Using an appropriate Table show how Gross Profit will change with changes in the Number of Plots and Cost Per Plot. Change Cost Per Plot from $5,500 to $14,500 in increments of $1,500. Change the Number of Plots, from 75 to 125 in increments of 25.

B-Apply appropriate formatting to the Table.

C-Highlight in yellow color, the Number of Plots and the Cost Per Plot required to produce a minimum Gross Profit.

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

A-In the worksheet defines appropriate names in order to facilitate the analysis. You need to consider the following three sets of circumstances relating to the prices of the different types of trees.

B-Pine = $11.00, Poplar = $13.00, Spruce = $12.00

C-Pine = $12.00, Poplar = $14.00, Spruce = $13.00

D-Pine = $8.00, Poplar = $10.00, Spruce = $8.00

E-Generate a Summary Report that displays Total Revenue for each set of circumstances.

5-Optional Question for Bonus Marks to earn a maximum total of 100 marks? You wish to find the maximum Gross Profit based on the Number of Plots purchased, Cost per Plot, and Average Board Feet per Plot. Calculate the maximum Gross Profit when the Number of Plots is not greater than 200, Cost per Plot is at least $5,000, and Average Board Feet per Plot is no more than 1,200. Solve the problem but keep the original values in the worksheet. Generate an Answer Report

THE TABLE IN THE WORKSHEET IS: