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

30. Save and close the file. Upload your solution file, i.e., e06_log_chap_LastFirst, as an answer to this question. (Make sure that you upload the right file. Until the end of the lab time you are allowed to upload unlimited times. Your last uploaded file will be the one graded.)

File Home Insert Draw Page Layout Formulas Data Review View Help Search Share Comments .A Insert. Delete Format Calibri General Conditional Format as Cell Formatting Table Styles Sort & Find &Ideas Filter Select . $ . %, Editing Clipboxard Fonit Ideas Gross Profit 13 Miller Tree Service Plots Cost Per Plot Average Board Feet Pine % Pine Price Poplar 96 Poplar Price Spruce % Spruce Price Total Cost Gross Profit Net Profit 100 75,000 10000.00 10% 7.00 Average Board Feet 50% 12.00 9.00 7,500,000.00 10,300,000.00 2,800,000.00 Analysis Results +100% File Home Insert Draw Page Layout Formulas Data Review View Help Search Share Comments .A Insert. Delete Format Calibri General Conditional Format as Cell Formatting Table Styles Sort & Find &Ideas Filter Select . $ . %, Editing Clipboxard Fonit Ideas Gross Profit 13 Miller Tree Service Plots Cost Per Plot Average Board Feet Pine % Pine Price Poplar 96 Poplar Price Spruce % Spruce Price Total Cost Gross Profit Net Profit 100 75,000 10000.00 10% 7.00 Average Board Feet 50% 12.00 9.00 7,500,000.00 10,300,000.00 2,800,000.00 Analysis Results +100%