A note on the use of spreadsheet optimisers in real estate analysis

Modern Portfolio Theory (MPT) has been advocated as a more rational approach to the construction of real estate portfolios. The application of MPT can now be achieved with relative ease using the powerful facilities of modern spreadsheet, and does not necessarily need specialist software. This capability is to be found in the use of an add-in Tool now found in several spreadsheets, called an Optimiser or Solver. The value in using this kind of more sophisticated analysis feature of spreadsheets is increasingly difficult to ignore. This paper examines the use of the spreadsheet Optimiser in handling asset allocation problems. Using the Markowitz Mean-Variance approach, the paper introduces the necessary calculations, and shows, by means of an elementary example implemented in Microsoft's Excel, how the Optimiser may be used. Emphasis is placed on understanding the inputs and outputs from the portfolio optimisation process, and the danger of treating the Optimiser as a Black Box is discussed.

Computing Markowitz efficient frontiers using a spreadsheet optimiser

Markowitz showed that assets can be combined to produce an 'Efficient' portfolio that will give the highest level of portfolio return for any level of portfolio risk, as measured by the variance or standard deviation. These portfolios can then be connected to generate what is termed an 'Efficient Frontier' (EF). In this paper we discuss the calculation of the Efficient Frontier for combinations of assets, again using the spreadsheet Optimiser. To illustrate the derivation of the Efficient Frontier, we use the data from the Investment Property Databank Long Term Index of Investment Returns for the period 1971 to 1993. Many investors might require a certain specific level of holding or a restriction on holdings in at least some of the assets. Such additional constraints may be readily incorporated into the model to generate a constrained EF with upper and/or lower bounds. This can then be compared with the unconstrained EF to see whether the reduction in return is acceptable. To see the effect that these additional constraints may have, we adopt a fairly typical pension fund profile, with no more than 20% of the total held in Property. The paper shows that it is now relatively easy to use the Optimiser available in at least one spreadsheet (EXCEL) to calculate efficient portfolios for various levels of risk and return, both constrained and unconstrained, so as to be able to generate any number of Efficient Frontiers.

Errors associated with preparation of adult and paediatric parenteral nutrition in UK hospital pharmacies

Rationale: In UK hospitals, the preparation of all total parenteral nutrition (TPN) products must be made in the pharmacy as TPNs are categorised as high-risk injectables (NPSA/2007/20). The National Aseptic Error Reporting Scheme has been collecting data on pharmacy compounding errors in the UK since August 2003. This study reports on types of error associated with the preparation of TPNs, including the stage at which these were identified and potential and actual patient outcomes. Methods: Reports of compounding errors for the period 1/2004 - 3/2007 were analysed on an Excel spreadsheet. Results: Of a total of 3691 compounding error reports, 674 (18%) related to TPN products; 548 adult vs. 126 paediatric. A significantly higher proportion of adult TPNs (28% vs. 13% paediatric) were associated with labelling errors and a significantly higher proportion of paediatric TPNs (25% vs. 15% adult) were associated with incorrect transcriptions (Chi-Square Test; p<0.005). Labelling errors were identified equally by pharmacists (42%) and technicians (48%) with technicians detecting mainly at first check and pharmacists at final check. Transcription errors were identified mainly by technicians (65% vs. 27% pharmacist) at first check. Incorrect drug selection (13%) and calculation errors (9%) were associated with adult and paediatric TPN preparations in the same ratio. One paediatric TPN error detected at first check was considered potentially catastrophic; 31 (5%) errors were considered of major and 38 (6%) of moderate potential consequence. Five errors (2 moderate, 1 minor) were identified during or after administration. Conclusions: While recent UK patient safety initiatives are aimed at improving the safety of injectable medicines in clinical areas, the current study highlights safety problems that exist within pharmacy production units. This could be used in the creation of an error management tool for TPN compounding processes within hospital pharmacies.

Integrating woodland with pig production in the United Kingdom: an investigation of potential performance and interactions

Although silvopastoral systems involving pigs were once widespread in Britain, the practice has largely died out. However, recent changes in pig production techniques, consumer demands and the economic climate within which farmers operate, have led to renewed interest in both traditional and novel tree-pig systems. This paper describes a financial spreadsheet model (MAST) that was developed to: provide a means of determining financial performance of integrating finishing pigs with natural woodland; identify the likely importance of different as yet largely unresearched animal-tree interactions; and, determine which interactions warrant attention in research and management. Preliminary analysis suggests that the financial performance of this agroforestry enterprise could be superior to that of a pasture-based enterprise. The most important factors in determining incremental performance are identified as sales premia for ‘forest-reared’ pork, changes to feed conversion ratios arising from the provision of a heterogeneous microclimate, and the availability of cheaper land rents.

Integrating natural woodland with pig production in the United Kingdom: an investigation of potential performance and interactions

Although silvopastoral systems involving pigs were once widespread in Britain, the practice has largely died out. However, recent changes in pig production techniques, consumer demands and the economic climate within which farmers operate, have led to renewed interest in both traditional and novel tree-pig systems. This paper describes a financial spreadsheet model ( MAST) that was developed to: provide a means of determining financial performance of integrating finishing pigs with natural woodland; identify the likely importance of different as yet largely unresearched animal-tree interactions; and, determine which interactions warrant attention in research and management. Preliminary analysis suggests that the financial performance of this agroforestry enterprise could be superior to that of a pasture-based enterprise. The most important factors in determining incremental performance are identified as sales premia for 'forest-reared' pork, changes to feed conversion ratios arising from the provision of a heterogeneous microclimate, and the availability of cheaper land rents.

A survey of dairy effluent management in the south of Chile

A survey was carried out on 55 commercial dairy farms located in the South of Chile during 1995-97. A questionnaire was developed to obtain informed estimates of dairy effluent management on those farms. Information was analysed on an annual basis using a computer spreadsheet linking all the parameters surveyed. In addition, slurry samples were taken for analysis of dry matter content (DM). Herd size varied between 50 and 800 cows per farm. A large proportion of the total volume of effluents produced came from rainfall (46%), dirty water accounted for 29% with only 25% from cow's faeces and urine. The large volume of effluents produced resulted in a reduced storage capacity (on average of 2 months) or more frequent and higher application rates to the field. Only 37% of the farmers knew the application rates of manure and there was a wide range in the quantity used per year (12 m(3)/ha to 300 m(3)/ha). Dairy effluents were applied mainly on grass (71%) throughout the year but, mostly concentrated during the winter and spring time using only surface irrigation system. The total solids contents of effluents was very low, with 62% of the samples being <4% DM. This reflected the large volumes of clean water that the storage tanks received. The information collected has identified problems in effluent management in Chilean dairy farms where research and technology transfer will be necessary to avoid pollution problems.

The tagged microarray marker (TAM) method for high-throughput detection of single nucleotide and indel polymorphisms

The tagged microarray marker (TAM) method allows high-throughput differentiation between predicted alternative PCR products. Typically, the method is used as a molecular marker approach to determining the allelic states of single nucleotide polymorphisms (SNPs) or insertion-deletion (indel) alleles at genomic loci in multiple individuals. Biotin-labeled PCR products are spotted, unpurified, onto a streptavidin-coated glass slide and the alternative products are differentiated by hybridization to fluorescent detector oligonucleotides that recognize corresponding allele-specific tags on the PCR primers. The main attractions of this method are its high throughput (thousands of PCRs are analyzed per slide), flexibility of scoring (any combination, from a single marker in thousands of samples to thousands of markers in a single sample, can be analyzed) and flexibility of scale (any experimental scale, from a small lab setting up to a large project). This protocol describes an experiment involving 3,072 PCRs scored on a slide. The whole process from the start of PCR setup to receiving the data spreadsheet takes 2 d.

Spectral design and verification of HIRDLS filters and antireflection coatings using an integrated system performance approach

The HIRDLS instrument contains 21 spectral channels spanning a wavelength range from 6 to 18mm. For each of these channels the spectral bandwidth and position are isolated by an interference bandpass filter at 301K placed at an intermediate focal plane of the instrument. A second filter cooled to 65K positioned at the same wavelength but designed with a wider bandwidth is placed directly in front of each cooled detector element to reduce stray radiation from internally reflected in-band signals, and to improve the out-of-band blocking. This paper describes the process of determining the spectral requirements for the two bandpass filters and the antireflection coatings used on the lenses and dewar window of the instrument. This process uses a system throughput performance approach taking the instrument spectral specification as a target. It takes into account the spectral characteristics of the transmissive optical materials, the relative spectral response of the detectors, thermal emission from the instrument, and the predicted atmospheric signal to determine the radiance profile for each channel. Using this design approach an optimal design for the filters can be achieved, minimising the number of layers to improve the in-band transmission and to aid manufacture. The use of this design method also permits the instrument spectral performance to be verified using the measured response from manufactured components. The spectral calculations for an example channel are discussed, together with the spreadsheet calculation method. All the contributions made by the spectrally active components to the resulting instrument channel throughput are identified and presented.

Decision theory and real estate development: a note on uncertainty

Real estate development appraisal is a quantification of future expectations. The appraisal model relies upon the valuer/developer having an understanding of the future in terms of the future marketability of the completed development and the future cost of development. In some cases the developer has some degree of control over the possible variation in the variables, as with the cost of construction through the choice of specification. However, other variables, such as the sale price of the final product, are totally dependent upon the vagaries of the market at the completion date. To try to address the risk of a different outcome to the one expected (modelled) the developer will often carry out a sensitivity analysis on the development. However, traditional sensitivity analysis has generally only looked at the best and worst scenarios and has focused on the anticipated or expected outcomes. This does not take into account uncertainty and the range of outcomes that can happen. A fuller analysis should include examination of the uncertainties in each of the components of the appraisal and account for the appropriate distributions of the variables. Similarly, as many of the variables in the model are not independent, the variables need to be correlated. This requires a standardised approach and we suggest that the use of a generic forecasting software package, in this case Crystal Ball, allows the analyst to work with an existing development appraisal model set up in Excel (or other spreadsheet) and to work with a predetermined set of probability distributions. Without a full knowledge of risk, developers are unable to determine the anticipated level of return that should be sought to compensate for the risk. This model allows the user a better understanding of the possible outcomes for the development. Ultimately the final decision will be made relative to current expectations and current business constraints, but by assessing the upside and downside risks more appropriately, the decision maker should be better placed to make a more informed and “better”.

Statistical monitoring of student performance: an early warning system

Weekly monitoring of profiles of student performances on formative and summative coursework throughout the year can be used to quickly identify those who need additional help, possibly due to acute and sudden-onset problems. Such an early-warning system can help retention, but also assist students in overcoming problems early on, thus helping them fulfil their potential in the long run. We have developed a simple approach for the automatic monitoring of student mark profiles for individual modules, which we intend to trial in the near future. Its ease of implementation means that it can be used for very large cohorts with little additional effort when marks are already collected and recorded on a spreadsheet.

Western Chess: Endgame data

This spreadsheet contains key data about that part of the endgame of Western Chess for which Endgame Tables (EGTs) have been generated by computer. It is derived from the EGT work since 1969 of Thomas Ströhlein, Ken Thompson, Christopher Wirth, Eugene Nalimov, Marc Bourzutschky, John Tamplin and Yakov Konoval. The data includes %s of wins, draws and losses (wtm and btm), the maximum and average depths of win under various metrics (DTC = Depth to Conversion, DTM = Depth to Mate, DTZ = Depth to Conversion or Pawn-push), and examples of positions of maximum depth. It is essentially about sub-7-man Chess but is updated as news comes in of 7-man EGT computations.

Determining the cost of in-field mitigation options to reduce sediment and phosphorus loss

The Mitigation Options for Phosphorus and Sediment (MOPS) project investigated the effectiveness of within-field control measures (tramline management, straw residue management, type of cultivation and direction, and vegetative buffers) in terms of mitigating sediment and phosphorus loss from winter-sown combinable cereal crops using three case study sites. To determine the cost of the approaches, simple financial spreadsheet models were constructed at both farm and regional levels. Taking into account crop areas, crop rotation margins per hectare were calculated to reflect the costs of crop establishment, fertiliser and agro-chemical applications, harvesting, and the associated labour and machinery costs. Variable and operating costs associated with each mitigation option were then incorporated to demonstrate the impact on the relevant crop enterprise and crop rotation margins. These costs were then compared to runoff, sediment and phosphorus loss data obtained from monitoring hillslope-length scale field plots. Each of the mitigation options explored in this study had potential for reducing sediment and phosphorus losses from arable land under cereal crops. Sediment losses were reduced from between 9 kg ha−1 to as much as 4780 kg ha−1 with a corresponding reduction in phosphorus loss from 0.03 kg ha−1 to 2.89 kg ha−1. In percentage terms reductions of phosphorus were between 9% and 99%. Impacts on crop rotation margins also varied. Minimum tillage resulted in cost savings (up to £50 ha−1) whilst other options showed increased costs (up to £19 ha−1 for straw residue incorporation). Overall, the results indicate that each of the options has potential for on-farm implementation. However, tramline management appeared to have the greatest potential for reducing runoff, sediment, and phosphorus losses from arable land (between 69% and 99%) and is likely to be considered cost-effective with only a small additional cost of £2–4 ha−1, although further work is needed to evaluate alternative tramline management methods. Tramline management is also the only option not incorporated within current policy mechanisms associated with reducing soil erosion and phosphorus loss and in light of its potential is an approach that should be encouraged once further evidence is available.

Training pharmacists to deliver a complex information technology intervention (PINCER) using the principles of educational outreach and root cause analysis

Objective: To describe the training undertaken by pharmacists employed in a pharmacist-led information technology-based intervention study to reduce medication errors in primary care (PINCER Trial), evaluate pharmacists’ assessment of the training, and the time implications of undertaking the training. Methods: Six pharmacists received training, which included training on root cause analysis and educational outreach, to enable them to deliver the PINCER Trial intervention. This was evaluated using self-report questionnaires at the end of each training session. The time taken to complete each session was recorded. Data from the evaluation forms were entered onto a Microsoft Excel spreadsheet, independently checked and the summary of results further verified. Frequencies were calculated for responses to the three-point Likert scale questions. Free-text comments from the evaluation forms and pharmacists’ diaries were analysed thematically. Key findings: All six pharmacists received 22 hours of training over five sessions. In four out of the five sessions, the pharmacists who completed an evaluation form (27 out of 30 were completed) stated they were satisfied or very satisfied with the various elements of the training package. Analysis of free-text comments and the pharmacists’ diaries showed that the principles of root cause analysis and educational outreach were viewed as useful tools to help pharmacists conduct pharmaceutical interventions in both the study and other pharmacy roles that they undertook. The opportunity to undertake role play was a valuable part of the training received. Conclusions: Findings presented in this paper suggest that providing the PINCER pharmacists with training in root cause analysis and educational outreach contributed to the successful delivery of PINCER interventions and could potentially be utilised by other pharmacists based in general practice to deliver pharmaceutical interventions to improve patient safety.