Profitability analysis spreadsheet

Nickisch, H., Profitability Analysis Spreadsheet, Univ. of Pennsylvania, Philadelphia (2003). 

Results from the Profitability Analysis Spreadsheet - Part (al - Purchase Costs and Bare Module Factors... 

The economic evaluation of the project should be oriented to profitability analysis, as explained in Chapter 14. The estimation of capital investment and operation costs is necessary, but the figures are only intermediate steps to profitability measures. These should be compared with similar processes, or other industrial projects, on a longer-term vision based on a lifecycle analysis. The economic analysis can be done at best with a spreadsheet. 

The chapter starts by introducing the basic concepts of an economic analysis, as prices, breakdown of the capital and manufacturing costs, profit, as well as the formation of cash flow. Because the time-value of money plays a central role the next section presents some basic elements of financial methods. Two other sections deal with the detailed estimation of capital and operation costs. Simplified equations based on typically cost ratios can be used for quick estimations. These ratios are also helpful for the control of more detailed computations. The chapter ends with a more detailed description of the profitability analysis, both by traditional and modem methods. Note that the methods developed in this chapter can be applied using spreadsheets. 

The operating pressures, reflux rates, and numbers of trays are a good basis for comparison of the two sequences. It is preferable, however, to determine the capital and operating costs and to compare the sequences on the basis of profitability. For this purpose. Aspen IPE can be used to estimate the costs, as discussed in Section 16.7, and an economics spreadsheet can be used to carry out the profitability analysis, as discussed in Section 17.8. 

Be able to use Aspen IPE in the Aspen Engineering Suite and an economics spreadsheet to carry out a profitability analysis for a potential process. 

This section is provided on the CD-ROM that accompanies this book. It shows how to use purchase and installation cost estimates from Aspen IPE and other sources, together with an economics spreadsheet by Holger Nickisch (2003) to estimate profitability measures for the monochlorobenzene separation process, which was introduced in Section 4.4. In Section 16.7, Aspen IPE was used to estimate the total permanent investment for this process. The economics spreadsheet, Profitability Analysis — l.O.xls, is on the CD-ROM that accompanies this book. 

When the appropriate specifications are made. Aspen IPE computes annual operating costs, as well as a complete profitability analysis, the results of which appear in this Investment Analysis spreadsheet. These notes discuss capital cost estimation only because the spreadsheet, Profitability Analysis-l. O.xls, which is discussed in Section 17.8 of the textbook, is used to compute operating costs, working capital, and profitability measures. 

The total permanent investment is computed by the spreadsheet. Profitability Analysis-l.O.xls, discussed in Section 17.8 of the textbook. When using the Aspen IPE option, the user enters ... 

Aspen IPE also computes the total permanent investment, Ctpi, as defined in Table 16.9 of the book. However, here, the total permanent investment is computed by the spreadsheet. Profitability Analysis-l.O.xls, which is discussed in Section 17.8. When using the Aspen IPE option in the spreadsheet, the user enters the following values, which are obtained from Aspen IPE ... 

Most proprietary spreadsheets have procedures for calculating the cumulative NPW from a listing of the yearly net annual revenue (profit). Spreadsheets are useful tools for economic analysis and project evaluation. 

The activity-based analysis showed how a combination of the customer s mix of products purchas and ordering habits determined customer profitability. The company concluded that these same factors should be considered when establishing a customer discount. To effect this change, PlumbCo provided each sales representative with a laptop computer and a simple spreadsheet to support the discount decision. This spreadsheet is shown in Figures 36.1,36.2, and 36.3. On the spreadsheet, the boxed and shaded areas are for input of customer data the balance of the spreadsheet calculates automatically. 

Monte-Carlo Analysis Using CAPCOST. The CAPCOST program introduced in Chapter 7 includes spreadsheets for estimating the cash flows of a project and the evaluation of profitability criteria such as NPV and DCFROR. In addition, a Monte-Carlo simulation has also been included that allows the following variables to be investigated ... 

In Table 15-5, we show the impact of different quantity flexibility contracts on profitability for the music supply chain when demand is normally distributed, with a mean of jx = 1,000 and a standard deviation of a- = 300 (see worksheet Examplel5-4 in spreadsheet Chapterl5-examples). We assume a wholesale price of c = 5 and a retail price of p = 10. All contracts considered are such that a = (3. The results in Table 15-5 are built in two steps. We first fix a and j8 (say a = j8 = 0.2). The next step is to identify the optimal order size for the retailer. This is done using Excel by selecting an order size that maximizes expected retailer profits given a and j8. For example, when a = j8 = 0.05 and c = 5, retailer profits are maximized for an order size of 0 = 1,017. For this order size, we obtain a supplier commitment to deliver up to g = (1 + 0 05) X 1,017 = 1,068 and a retailer commitment to buy at least q = ( - 0.05) X 1,017 = 966 discs. In our analysis, we assume that the supplier produces Q = 1,068 discs and sends the precise number (between 966 and 1,068) demanded by the retailer. Such a policy results in retailer profits of 4,038 and supplier profits of 4,006. 

---