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 the downloaded e06_log_chap_data file. Save the file as e06_log_chap_LastFirst (where you need to replace Last with your Lastname and First with your Firstname).

2. Open the Analysis worksheet and create new named ranges for the existing inputs in B3:C14. (You will need to use the "Create from Selection" under "Defined Names" group of "Formulas" tab.)

3. Change the name to Number_of_Plots to cell C3 using "Name Manager" (instead of the one assigned in part 2 by default).

4. Edit the formula in C14 to use the named ranges instead of the cell references.

5. Save the file. (You can save the file by pressing the "Save" option under "File" tab of Excel ribbon or you can use the shortcut keys "Ctrl+S".)

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.

6. Change value in cell C3 to 130. Use Goal Seek to determine what the Average Board Feet must be if you bid on 130 plots at $75,000 each if you want to have a Net Profit of $2,000,000. (Press OK after Goal Seek determines the Average Board Feet.)

7. Answer Question 1 on the Results worksheet.

8. Save the file.

One-Variable Data Table

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

9. Starting in F4, complete a series of substitutions starting at 7500 in increments of 500 and a max of 10500, going down vertically.

10. Enter the references for Gross Profit and Net Profit, respectively, in the columns.

11. Complete the one-variable data table.

12. Format the results to Accounting Number Format with 0 decimal places.

13. Change G3 and H3 to display Gross Profit and Net Profit, respectively.

14. Answer Question 2 on the Results worksheet.

15. Save the file.

Two-Variable Data Table

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

16. Starting in G14, complete a series of substitutions starting at 50 in increments of 25 for a max of 150, going across horizontally.

17. Starting in F15, complete a series of substitutions starting at 45000 in increments of 15000 for a max of 135000, going down vertically.

18. Complete the two-variable data table by adding the reference for Net Profit.

19. Format the results to Accounting Number Format with 0 decimal places.

20. Change F14 to display Cost per Plot.

21. Merge and center cells G13:K13 and add a heading Number of Plots.

22. Answer Question 3 on the Results worksheet.

23. Save the file.

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.

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

• Pine = $9.00
• Poplar = $14.00
• Spruce = $11.00

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

• Pine = $7.00
• Poplar = $12.00
• Spruce = $9.00

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

• Pine = $5.00
• Poplar = $10.00
• Spruce = $7.00

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

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

29. Answer Question 4 on the Results worksheet

0o kq.chap data Excel Invet Psg. L "ynut Formuiac Dela Revin. View Add-ins IMp ACROBAT Tmm Tel m, what ynu want to do. Share n Normal Copy ,Format Palm" inked Cal Sort Fnd &Share Uplcod Check Cell Blu,. armetting Tshe Tont Miller Tree Service Average Board Feet Cost Por Plat Average Board Fect Pine Price Poplar % Poplar Prike spruce % Spruce Price 7.00 50% 7,500,000.00 10,300,000.00 2,800,00co.00 Net Profit Analysis | Resuts | 06 Jeqchap data-Exce Normal Linked Cell SortEtFnd&Share Upleod Check Cell Bru- , ,Format "enter armetting Tshe Tont Miller Tree Service in a $2 million Net Profit? | Question 2: |What is my Net Profit r i aver0gc 9000 brd-cet? Questio3: what is my Net Profit ifpurce 125 plots at$120000per Plot? 7 Question 4:What is my Net Protit in the Worst Case scenarin? 12 Aralysis 0o kq.chap data Excel Invet Psg. L "ynut Formuiac Dela Revin. View Add-ins IMp ACROBAT Tmm Tel m, what ynu want to do. Share n Normal Copy ,Format Palm" inked Cal Sort Fnd &Share Uplcod Check Cell Blu,. armetting Tshe Tont Miller Tree Service Average Board Feet Cost Por Plat Average Board Fect Pine Price Poplar % Poplar Prike spruce % Spruce Price 7.00 50% 7,500,000.00 10,300,000.00 2,800,00co.00 Net Profit Analysis | Resuts | 06 Jeqchap data-Exce Normal Linked Cell SortEtFnd&Share Upleod Check Cell Bru- , ,Format "enter armetting Tshe Tont Miller Tree Service in a $2 million Net Profit? | Question 2: |What is my Net Profit r i aver0gc 9000 brd-cet? Questio3: what is my Net Profit ifpurce 125 plots at$120000per Plot? 7 Question 4:What is my Net Protit in the Worst Case scenarin? 12 Aralysis