In Spreadsheet modeling and decision analysis, optimizing a timber harvest. I need to know the complete formula
Fantastic news! We've Found the answer you've been seeking!
Question:
In Spreadsheet modeling and decision analysis, optimizing a timber harvest. I need to know the complete formula for the net flow column and if there is a formula for the link column and if so, what it is.
Spreadsheet modeling and decision analysis:
| Optimizing a Timber Harvest | ||||||||||||
| Build | ||||||||||||
| Flow | From | To | Road? | Cost | Link | Area | Harvest? | Value | Supply/Demand | Net Flow | ||
| 0 | 1 | 4 | 0 | $9.00 | 0 | 1 | 0 | $15.00 | $0.00 | 0 | ||
| 0 | 2 | 0 | 0 | $0.00 | 0 | 2 | 0 | $7.00 | $0.00 | 0 | ||
| 0 | 3 | 2 | 0 | $13.00 | 0 | 3 | 0 | $10.00 | $0.00 | 0 | ||
| 0 | 3 | 6 | 0 | $12.00 | 0 | 4 | 0 | $12.00 | $0.00 | 0 | ||
| 0 | 4 | 5 | 0 | $14.00 | 0 | 5 | 0 | $8.00 | $0.00 | 0 | ||
| 0 | 5 | 2 | 0 | $15.00 | 0 | 6 | 0 | $17.00 | $0.00 | 0 | ||
| 0 | 5 | 8 | 0 | $13.00 | 0 | 7 | 0 | $14.00 | $0.00 | 0 | ||
| 0 | 6 | 3 | 0 | $12.00 | 0 | 8 | 0 | $18.00 | $0.00 | 0 | ||
| 0 | 6 | 9 | 0 | $13.00 | 0 | 9 | 0 | $13.00 | $0.00 | 0 | ||
| 0 | 7 | 4 | 0 | $15.00 | 0 | 10 | 0 | $12.00 | $0.00 | 0 | ||
| 0 | 8 | 5 | 0 | $13.00 | 0 | 11 | 0 | $10.00 | $0.00 | 0 | ||
| 0 | 8 | 9 | 0 | $12.00 | 0 | 12 | 0 | $11.00 | $0.00 | 0 | ||
| 0 | 9 | 6 | 0 | $13.00 | 0 | 0 | -- | -- | $0.00 | 0 | ||
| 0 | 9 | 8 | 0 | $12.00 | 0 | |||||||
| 0 | 10 | 11 | 0 | $11.00 | 0 | Road cost | 0 | |||||
| 0 | 11 | 8 | 0 | $12.00 | 0 | Timber Value | $0.00 | |||||
| 0 | 12 | 9 | 0 | $10.00 | 0 | Profit | $0.00 | |||||
| Step 1 | ||||||||||||
| Put in values for transportation from one location to the other and the area. | ||||||||||||
| Step 2 | ||||||||||||
| Add cost of building each section of road in thousands | ||||||||||||
| Step 3 | ||||||||||||
| Add decision variables to determine which area is most profitable. | ||||||||||||
| Cells B5:b21 are the flow from one area to another. | ||||||||||||
| Cells E5:E21 will indicate if the road should be built or not. | ||||||||||||
| Cells J5:J17 will show if an area should be harvested or not. | ||||||||||||
| Step 4 | ||||||||||||
| Cells G5:G21 will calculate the link | ||||||||||||
| Step 5 | ||||||||||||
| In cells, L5:L16 is the value of the harvested area times if the area should be harvested. L17 is the sum. | ||||||||||||
| Step 6 | ||||||||||||
| Column M is the Net flow using the sumif function. | ||||||||||||
| So, if the sum of D5:D21 is equal to I5, minus is the sumif | ||||||||||||
| of sum of C5:C21 is equal to I5 | ||||||||||||
| Step 7 | ||||||||||||
| In cell K19 I calculated the road cost using sumproduct. In cell K21 the profit is calculated by finding the | ||||||||||||
| difference between K20 and K19. | ||||||||||||
| Step 8 | ||||||||||||
| Next, I used Solver to find a solution. Cell K21 is the objective cell that we want to maximize by changing | ||||||||||||
| cells B5:B21, E5:E21 and J5:J16. | ||||||||||||
Expert Answer:
Based on the information provided the formulas for the Net Flow column and the Link column can ... View the full answer
Related Book For 
Financial Accounting and Reporting a Global Perspective
ISBN: 978-1408076866
4th edition
Authors: Michel Lebas, Herve Stolowy, Yuan Ding
Posted Date:
