70.2K

Verified Solution

Link Copied!

Miller Tree Service

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 Excels Analysis tools.

1. Open ExcelExam2Ch6StartFile and save as ExcelExam2Ch6_LastFirst.
2. Open the Analysis worksheet and create new named ranges for the existing inputs in B2:C14.
3. Assign the name Number_of_Plots to cell C3.
4. Edit the formula in C14 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.

5. Use Goal Seek to determine what the Average Board Feet must be if you bid on 100 plots at $75,000 each to cover your $2 million net profit.
6. Answer Question 1 on the Results 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.

7. Starting in F4, complete a series of substitutions starting at 7500 in increments of 500 and a max of 10500, going down vertically.
8. Enter the references for Gross Profit and Net Profit, respectively, in the columns.
9. Complete the one-variable data table.
10. Format the results to Accounting Number Format with Zero decimal places.
11. Change G3 and H3 to display Gross Profit and Net Profit, respectively.
12. Answer Question 2 on the Results worksheet.

Two-Variable Data Table

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

13. Starting in G14, complete a series of substitutions starting at 50 in increments of 25 for a max of 150, going across horizontally.
14. Starting in F15, complete a series of substitutions starting at 45000 in increments of 15000 for a max of 135000, going down vertically.
15. Complete the two-variable data table by adding the reference for Net Profit.
16. Format the results to Accounting Number Format with Zero decimal places.
17. Change F14 to display Cost per Plot.
18. Merge and center cells G13:K13 and add a heading Number of Plots.
19. Answer Question 3 on the Results 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.

20. Create a scenario named Best Case using the following pricing:
    • Pine = $9.00
    • Poplar = $14.00
    • Spruce = $11.00
21. Create a scenario named Most Likely using the following pricing:
    • Pine = $7.00
    • Poplar = $12.00
    • Spruce = $9.00

5. Create a scenario named Worst Case using the following pricing:
   • Pine = $5.00
   • Poplar = $10.00
   • Spruce = $7.00

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

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

25. Answer Question 4 on the Results worksheet.

Miller Tree Service Average Board Feet Plots Cost Per Plot Average Board Feet Pine % Pine Price Poplar % Poplar Price Spruce % Spruce Price Total Cost Gross Profit Net Profit 100 75,000 10000.00 10% S 7.00 50% 12.00 40% $ 9.00 $ 7,500,000.00 $ 10,300,000.00 $ 2,800,000.00 Miller Tree Service Average Board Feet Plots Cost Per Plot Average Board Feet Pine % Pine Price Poplar % Poplar Price Spruce % Spruce Price Total Cost Gross Profit Net Profit 100 75,000 10000.00 10% S 7.00 50% 12.00 40% $ 9.00 $ 7,500,000.00 $ 10,300,000.00 $ 2,800,000.00

Answer & Explanation Solved by verified expert

See Answer