Short Affiliation xvii
Preface xix
Acknowledgments xxiii
About the Companion Website xxv
Introduction xxvii
Part I Spreadsheet Development 1
1 Introduction to Part I 3
1.1 Uses for Spreadsheets 3
1.2 Why Spreadsheets for TEM? 4
1.3 Alternative Techno-Economic Modeling Platforms 5
1.4 Importance of Good Spreadsheet Development Practices 6
1.5 Spreadsheet Development as Software Development 6
1.6 Back-End and Front-End Spreadsheet Development 7
1.7 Important Terms 8
1.7.1 Introduction 8
1.7.2 Anatomy of the Excel User Interface 8
1.7.3 Anatomy of an Excel Formula 10
1.7.4 Other Important Terms 11
1.8 Key Functions 11
1.8.1 Math Functions 11
1.8.2 Statistical Functions 12
1.8.3 Lookup Functions 12
1.8.4 Logical Functions 12
1.8.5 Financial Functions 12
1.8.6 Text Functions 13
1.9 Keyboard Shortcuts 13
Reference 14
2 Back-End Development 15
2.1 Cell-Level Best Practices 16
2.1.1 Keep Formulas Short 17
2.1.2 Avoid Hard-Coding Numbers 18
2.1.3 Perform Calculations in a Consistent Unit Set 19
2.1.4 Avoid Using Off-Sheet References in Formulas 19
2.1.5 Avoid Linking Workbooks 21
2.1.6 Use Named Ranges Where Appropriate 21
2.1.7 Consider Replacing Complex Formulas with User-Defined Function 22
2.1.8 Treat Percentages as Decimals 23
2.1.9 Omit Unnecessary Symbols 23
2.2 Worksheet- and Workbook-Level Best Practices 23
2.2.1 Perform Calculations in Parallel When Possible 24
2.2.2 Build Models to Examine a Single Scenario 25
2.2.3 Perform Each Calculation Only Once 25
2.2.4 Master Tracing Formulas 27
2.2.4.1 Keep Lookup Formulas Local 28
2.2.5 Separate Workbooks into Logical Worksheets 29
2.2.6 Make Calculations and Worksheets Modular 29
2.2.7 Regularly Refactor 30
3 Front-End Development 31
3.1 General Principles of User Interface Design 31
3.2 Cell-Level and Formatting Best Practices 33
3.2.1 Differentiate Input Cells 33
3.2.2 Be Explicit with Labels and Units 33
3.2.3 Omit Unnecessary Labels 33
3.2.4 Use Appropriate Number Formatting 34
3.2.5 Turn Gridlines Off 35
3.2.6 Avoid Merging Cells 36
3.2.7 Use Consistent Cell Alignment and Indenting 36
3.2.8 Set Consistent Cell Sizes 37
3.3 Worksheet- and Workbook-Level Best Practices 38
3.3.1 Choose Table Orientation Deliberately 38
3.3.2 Use Named Ranges for Constants and Store them in a Dedicated Worksheet 38
3.3.3 Leave Some White Space 38
3.4 User Interface Best Practices 40
3.4.1 Emphasize Important Inputs and Results 40
3.4.2 Include a Dashboard 40
3.4.3 Use Graphs and Charts to Visually Represent Results 41
3.4.4 Consider the User's Screen Size and Use 41
Reference 42
4 Documentation 43
4.1 Nearby Cells 44
4.2 Cell Notes 44
4.3 Dedicated Location Within Worksheet 46
4.4 Dedicated Worksheet 47
4.5 Separate Document 47
4.6 Screencast 48
Reference 48
5 Visual Basic for Applications 49
5.1 VBA Basics 49
5.1.1 Enabling Macros 49
5.1.2 The VBA Editor 51
5.1.3 Dot Notation and the Object Model 53
5.1.4 Subroutines and Functions 53
5.1.4.1 Comments 54
5.1.5 Variables 54
5.2 Working with VBA 56
5.2.1 Scope 56
5.2.2 Loops 57
5.2.3 Conditional Statements 57
5.2.4 With Statements 58
5.2.5 Arrays and Collections 58
5.2.6 Error Handling 60
5.2.7 Debugging 63
5.3 Applications to TEM 64
5.3.1 When to Use VBA 64
5.3.2 User-Defined Functions 65
5.3.3 Macros 67
5.3.4 Improving Calculation Speed 70
5.4 AI-Assisted Coding 71
Part II Techno-Economic Modeling 73
6 Introduction to Part II 75
6.1 Methods and Accuracy 77
6.2 Anatomy of a Techno-Economic Model 78
Reference 80
7 Process Diagrams 81
7.1 Types and Use of Process Diagrams 81
7.1.1 Block Flow Diagrams (BFDs) 82
7.1.2 Process Flow Diagrams (PFDs) 82
7.1.3 Piping and Instrumentation Diagrams (P&IDs) 82
7.1.4 Hybrid Diagrams 86
7.1.5 Process Sequence Diagrams 86
7.2 Defining Model Scope 86
7.3 Best Practices 89
7.3.1 General Considerations 89
7.3.2 Organization 90
7.3.3 Labeling 91
7.4 Concluding Thoughts 93
References 94
8 Process Modeling 95
8.1 Spreadsheet Structure and Organization 95
8.1.1 Settings 96
8.1.2 Calculations 98
8.1.3 Stream Table 98
8.1.4 Results 98
8.2 The Stream Table 98
8.2.1 Stream Properties 99
8.2.1.1 Description 101
8.2.1.2 Total and Component Mass Flow Rates 101
8.2.1.3 Total and Component Mole Flow Rates 101
8.2.1.4 Volume Rate, Density, and Average Molecular Weight 101
8.2.1.5 Temperature and Pressure 102
8.2.1.6 Enthalpy Rate and Heat Capacity 103
8.3 Material Balance Calculations 104
8.3.1 Solving the Stream Table 105
8.3.2 Recycles 107
8.3.2.1 Recycle Constrained by Overall Conversion 108
8.3.2.2 Processes Constrained by Impurity Concentration 109
8.3.3 Material Rate Basis and Onstream Factor 111
8.3.3.1 Continuous Processes 111
8.3.3.2 Batch Processes 112
8.3.3.3 Other Considerations 112
8.3.4 Integrated Material Balance Verification 113
8.4 Energy Balance Calculations 113
8.4.1 Enthalpy Balances in the Stream Table 114
8.4.2 Heat Exchangers 114
8.4.2.1 Heat Exchange Between Streams 114
8.5 Special Topics 120
8.5.1 Stoichiometry 120
8.5.2 Vapor-Liquid Equilibria 122
8.5.3 Distillation 123
8.5.4 Packed Beds 124
8.5.5 Electrochemistry 125
8.6 Development Workflow 128
Reference 128
9 Equipment Sizing 129
9.1 Spreadsheet Structure and Organization 131
9.2 Utility Calculations 132
9.2.1 Fuel 135
9.2.2 Electricity 135
9.2.3 Process Water 136
9.2.4 Steam 136
9.2.5 Thermal Fluids 139
9.2.6 Cooling Water 139
9.2.7 Refrigeration 140
9.2.8 Demineralized Water 142
9.2.9 Other Utilities 142
9.3 Sizing Calculations 143
9.3.1 Vessels 143
9.3.1.1 General Sizing Calculations 143
9.3.1.2 General Utility Calculations for Vessels 144
9.3.1.3 Liquid-Filled Vessels 146
9.3.1.4 Fermenters 147
9.3.1.5 Active-Material-Packed Vessels 148
9.3.1.6 Vapor-Liquid Separation Vessels 149
9.3.2 Columns 151
9.3.2.1 Distillation Columns 151
9.3.2.2 Absorption and Stripping Columns 153
9.3.3 Fluid Moving Equipment 153
9.3.3.1 Pumps 154
9.3.3.2 Compressors 154
9.3.3.3 Blowers and Fans 155
9.3.3.4 Vacuum Pumps 157
9.3.4 Heat Exchangers 158
9.3.5 Heaters and Furnaces 159
9.3.6 Filters 159
9.3.7 Membrane Systems 160
9.3.8 Sedimentation Centrifuges 160
9.3.9 Electrochemical Cells 161
9.3.10 Unusual or Novel Equipment 161
9.4 Materials of Construction 162
9.4.1 Corrosion/Material Compatibility 162
9.4.2 Strength 163
9.4.3 Application-Specific Requirements 163
9.5 Development Workflow 163
References 164
10 Equipment Costing 165
10.1 Structure and Organization 165
10.2 Power-Law Scaling 166
10.2.1 Estimating Scaling Exponents from Data 171
10.2.2 Geometrical Underpinnings of Scaling Exponents 171
10.2.3 Breaking Down a Scaling Exponent 172
10.2.4 The Blended Scaling Exponent 173
10.2.5 Limitations of Power-Law Scaling 175
10.2.6 The Importance of Similarity 175
10.2.7 Novel or Unusual Equipment 176
10.3 Factors for Adjusting Purchase Cost 177
10.3.1 Material Factors 177
10.3.2 Pressure Factors 178
10.3.3 Miscellaneous Factors 179
10.3.4 Cost Escalation 179
10.4 Installation Factors 181
10.5 Putting It All Together 182
10.6 Development Workflow 187
References 188
11 Capital Cost Estimation 189
11.1 Important Distinctions 189
11.2 Methods for Estimating Fixed Capital 191
11.2.1 Equipment Factor Method 191
11.2.2 Lang Factor Method 194
11.2.3 Overall Plant Cost Scaling Method 194
References 195
12 Operating Costs and Revenue Estimation 197
12.1 Structure and Organization 197
12.2 Variable Operating Costs 199
12.2.1 Raw Materials 199
12.2.2 Consumables 201
12.2.3 Utilities 201
12.2.4 Waste Management 202
12.3 Operating Labor Costs 203
12.4 Other Fixed Operating Costs 205
12.4.1 Supervision and Labor Overhead 206
12.4.2 Maintenance 206
12.4.3 Local Taxes, Insurance, and Rent 206
12.4.4 Patents and Royalties 206
12.4.5 Interest on Working Capital 206
12.5 Revenue 207
References 208
13 Economic Value Estimation 209
13.1 Simple Metrics 210
13.1.1 Gross Profit and Gross Margin 210
13.1.2 Return on Investment and Simple Payback 210
13.2 Time Value of Money and Discounting 210
13.2.1 Cost of Capital 211
13.2.2 Putting a Value on the Discount Rate 212
13.2.3 Special Case: Annuities 213
13.3 Levelized Cost 213
13.4 Cash Flow Analysis 217
13.4.1 Project Lifetime 220
13.4.2 Investment and Start-up Schedules 220
13.4.3 Income Tax 220
13.4.4 Depreciation 221
13.4.5 Working Capital 223
13.4.6 Financing and Interest 223
13.5 Discounted Metrics 225
13.5.1 Net Present Value 227
13.5.2 Internal Rate of Return 228
13.5.3 Modified Internal Rate of Return 229
13.5.4 Net Present Value Percent 231
13.6 Incremental Analysis 231
References 232
14 The Dashboard 235
14.1 Dashboard Design 235
14.2 Best Practices 237
15 Top-Down Modeling 239
15.1 Top-Down Model Structure 240
15.2 Steps to Building a Top-Down Model 240
16 Model Review and Debugging 245
References 248
17 Conclusion: Working with Uncertainty 249
17.1 Uncertainty in Process Design 249
17.2 Uncertainty in the Model Inputs 251
Part III Techno-Economic Analysis 253
18 Analysis Methods 255
18.1 Cost Distribution Charts 255
18.2 Basic Sensitivity Analyses 257
18.3 Tornado Diagrams 261
18.4 Monte Carlo Method 262
18.5 Scenario Analysis 265
Reference 266
19 Techno-Economic Analysis in Practice 267
19.1 Assessing Potential for Economic Viability 267
19.2 Assessing Risk and Uncertainty 268
19.3 Guiding R&D 272
19.4 Communication 274
Part IV Case Studies 277
20 Bottom-Up Case Study 279
20.1 Process Description 279
20.2 Model Walkthrough 281
20.2.1 Process Modeling 281
20.2.2 Equipment Sizing 283
20.2.3 Equipment Costing 287
20.2.4 Capital Cost Estimation 289
20.2.5 Operating Cost and Revenue Estimation 289
20.2.6 Economic Value Metric Estimation 293
20.2.7 Dashboard 296
20.3 Model Review 296
20.3.1 Process Diagram 296
20.3.2 Dextrose Costs 296
20.3.3 Equipment Costing 298
20.4 Analysis and Discussion 299
References 301
21 Top-Down Case Study 303
21.1 Process Description 303
21.2 Model Walkthrough 305
21.2.1 Process Modeling 305
21.2.2 Capital Cost Estimation 308
21.2.3 Levelized Cost Estimation 309
21.2.4 Dashboard 312
21.3 Model Review: Comparison with Study Results 312
21.4 Analysis and Discussion 318
References 323
Index 000
