User Manual
Contents
1. the formula is evaluated as 1 3 10 This notion is particularly important when evaluating formulas for midpoints that require summing operations on an array of lot numbers In Example 3 the following array formula in cell A9 yields the total cost of a lot if the first unit is 1 the last unit is 8 the slope is 92 b 1203 and T is 10 2007 Technomics Inc Copyright notice on page 1 applies 10 gt Technomics b 0 1203 oi oN A E U ej The formula in cell A9 is B2 SUM A1 A8 B5 and is evaluated as 10 190 0 1203 he 89 1203 68 Compare Example 3 with the following Example 4 Example 4 appears at first to be the same or a similar formula but notice it is evaluated entirely different by Excel because of the missing cells in between the first unit 1 and the last unit 8 The formula in cell A9 is B2 SUM A1 A2 B5 and is evaluated as 10 100 a 9 0 1203 18 Remember Excel will not assume that every number in the array between the first and last unit numbers is an integer that needs to be evaluated Excel simply evaluates every cell in between the defined array parameters 2007 Technomics Inc Copyright notice on page 1 applies 112. first unit of the lot the last unit of the lot and depending on the requested formula the slope of the learning curve First display the input output and insertion options of the calculator by clicking Show Calculator The First Unit Last Unit and S ope input boxes accept valid cell ranges from the active workbook To enter an address activate the input box and either type in the cell address or click on the desired cell within the spreadsheet The cell address must refer to one cell only To enter a named range simply type the name in the address input box Calculating the Once the inputs have been entered click Calculate to evaluate the Midpoint selected formula The midpoint answer will appear in the box to the right of the Calculate button Before evaluating the midpoint formula the calculator will validate the inputs and will provide feedback if any inputs are not acceptable The following error messages may appear near the First Unit Last Unit and Slope address input boxes if a valid number or address is not entered Input Error Value The input box requires a cell address If an address is provided then the referenced cell is either empty or does not contain a valid number The cell address or named range is not valid The range refers to more than one cell Div by zero The midpoint formula cannot be evaluated if the slope parameter is equal to 1 The following error messages may appear in the calculation o
3. the Formula Text Box A7 Technomics For Example selecting Parameter Free Approximation ROT in the Formula List will present Last First 1 2 First 1 Equations beginning with si hie 7 directly into an Excel spreadsheet the formula will be evaluated by in the Formula Text Box If this formula text is copied and pasted Formula Text Excel The formula will yield a NAME error unless First and Last Box canbe are replaced by numbers or are previously defined names directly However if Approximation with Known Slope Corrected is evaluated by ae selected in the Formula List the following appears in the Formula xcel Text Box Last First 1 a 1 x LApproximated Sum Correction Terms a l b In this case there is no single formula that can be inserted into an Excel spreadsheet to yield the answer The answer is calculated using a Visual Basic for Applications VBA routine and thus cannot be represented within an Excel cell Copy Formula Button Copies the text string in the Formula Text Box to the Windows Clipboard Show Calculator Button Expands the interface to reveal the inputs output and spreadsheet insertion options for the selected midpoint formula Exit Button Hides the calculator The calculator is cleared from memory when exiting Excel About Button Provides copyright distribution rights and additional information about the calculator Formula Input FO
4. when either the Mean or Rule Checkbox of Thumb parameter free approximation has been selected If checked the calculator will use recommended alternative estimating equations to estimate the first lot midpoint If left unchecked the calculator will use the standard subsequent lot midpoint estimating equations For example suppose the first unit is 1 the last unit is 25 and the slope is 99 If the Parameter Free Approximation ROT formula is selected the midpoint is estimate as 12 5 This estimate is calculated using the standard rule of thumb equation Last First 1 2 First 1 If First Lot is checked the midpoint estimate is now calculated using the alternative first lot formula Last 3 1 or 9 333 If the first unit is 2 or larger the First Lot checkbox must be left unchecked The calculator returns an error if the First Lot option is checked and the first unit is not equal to 1 4 The Midpoint Calculator is able to export the selected formula or CALCULATOR midpoint estimate directly into the active worksheet To export OUTPUT the answer or the formula enter the target address in the Output Address input box A cell address can be entered into the Output Address box by either selecting the target cell in the active worksheet typing the cell address or typing a valid named range that refers to a single cell Once an address has been entered select either the nsert Answer or Insert Formula option and clic
5. RMULA INPUTS Options First Unit Last Unit Slope Range input boxes for inputting the cell addresses of the first unit last unit and the slope if required First Lot Checkbox Indicates whether or not the calculator should calculate the midpoint as a first lot midpoint Some formulas calculate first lot midpoints differently than subsequent lot 2007 Technomics Inc Copyright notice on page 1 applies 4 A7 Technomics midpoints If this option is available then the selected formula has a different method for calculating first lot midpoints If this option is not available then the selected formula calculates all midpoints the same regardless of lot number Calculation CALCULATION OPTIONS Options Calculate Button Updates the calculator output Reset Button Clears all formula inputs output and insertion options Midpoint Text Box Provides the calculated midpoint answer True Midpoint Error Text Box Provides the error associated with the calculated midpoint answer If possible the calculator will compare the selected midpoint estimate with the true algebraic midpoint Formula OUTPUT OPTIONS Output Options Output Address Accepts a cell address to output either the calculated answer or the selected midpoint formula Insert Answer Option When selected the calculator will insert the calculated midpoint answer into the cell address provided in the Output Address box Insert Formula Option When selected the c
6. alculator will insert the selected formula into the cell address provided in the Output Address box By default the formula will reference the addresses provided in the first Unit Last Unit and Slope boxes If valid addresses are not provided in the First Unit Last Unit or Slope boxes then the required inputs of the inserted formula are replaced with text variables Note that this option is only available for midpoint formulas that can be represented within spreadsheet cells using inherent Excel functions it is not available for midpoint estimates that require calculation through VBA routines Absolute Address Checkbox When checked this replaces any relative addresses provided for First Unit Last Unit and Slope with 2007 Technomics Inc Copyright notice on page 1 applies 5 A7 Technomics absolute or anchored addresses Insert Button Inserts either the calculated answer or the complete formula into an Excel spreadsheet according to the selected output options 3 Using the Midpoint Calculator simply requires selecting the USING THE desired equation from the Formula List inputting the first unit CALCULATOR last unit and slope if necessary and clicking Calculate Selecting the To select the desired midpoint formula scroll through the Formula Formula List and click on the formula you wish to use The equation will appear to the right of the Formula List and a short description of the formula will appear below the li
7. if the lot has less than 10 units one third for a first lot with 10 or more units All other lots use the formula cuiator lt lt Formula Inputs Calculator Output Frstunit sn First gt 0 Midpoint Waiting eee Calculate First Lot i Insert Options Output Address Insert formula Absolute addresses B f Insert answer Insert Figure 3 Expanded interface of the calculator The main interface consists of the following buttons and options FORMULA SELECTION Formula List Contains the list of all available midpoint formulas for use in the calculator Use the scrollbar to view the complete list To activate a formula click the formula s name in the list An image of the selected equation will appear to the right of the list Formula Description Provides a brief description of the selected midpoint formula and its required inputs For more detailed explanation on the derivation and theory behind each formula please see Appendix I Formula Text Box Provides a text version of the selected midpoint formula In most cases the formula in the Formula Text Box is a text string that Excel will recognize and evaluate as a valid formula provided the variables Last First and b are replaced with valid numbers In other cases it is not possible to create a single equation for the midpoint formula that can be evaluated in Excel In these latter cases a more general descriptive formula is given in
8. k Insert Insert Answer will insert the estimated midpoint into the cell specified in the Output Address box Insert Formula will insert an Excel formula into the target output cell that references the input cells defined in the First Unit Last Unit and Slope address input boxes If the Absolute Addresses checkbox is checked the nsert Formula option will replace any relative addresses provided for First Unit Last Unit and Slope with absolute or anchored addresses If any of the cell references required for the formula are missing or invalid the required input will be replaced by a variable placeholder _FIRST LAST or SLOPE 2007 Technomics Inc Copyright notice on page 1 applies 9 A7 Technomics Appendix Using Excel Array Formulas to Calculate Midpoints An array formula operates on an array or series of input numbers They are typed into cells like normal formulas except to enter them the user presses Ctrl Shift Enter As an example consider the following simple array formula example that sums the squares of the numbers in cells Al A2 and A3 The formula in cell A4 is SUM A1 A3 2 Notice that the formula s array argument spans cells Al through A3 and all three of Al A2 and A3 have numbers in the cells This formula evaluates as 1 22 32 14 Now consider the next example The formula in cell A4 in the second example is still SUM A1 A3 2 However because cell A2 is empty
9. lculator its use and tips and tricks on comparing and contrasting different midpoint formulas 1 GETTING STARTED Installing and starting the Midpoint Calculator The Midpoint Calculator is a Microsoft Excel Add In designed for use with Excel 2000 and 2003 To install the add in first save the x a file to your hard drive Under Excel s Tools menu select Add Ins browse to the saved x a file and place a checkmark next to Midpoint Calculator Click OK and the Midpoint Calculator will install If the installation is successful the Midpoint Calculator will be accessible from Excel s Tools menu see Figure 1 Shared Workspace Share Workbook Track Changes Protection t Online Collaboration Goal Seek Scenarios Formula Auditing Solver Macro Adobe PDF Add Ins F AutoCorrect Options Customize Options cc Figure 1 Midpoint Calculator in Excel s Tools menu To uninstall the calculator select Add Ins uncheck Midpoint 2007 Technomics Inc Copyright notice on page 1 applies A7 Technomics 2 INTERFACE OVERVIEW Calculator and click OK The Midpoint Calculator will uninstall and remove itself from Excel s Tools menu The Midpoint Calculator s interface on start up is given below in Figure 2 This initial condensed view shows the Formula List the Formula Description and the Formula Text Box For
10. le of Figures Figure 1 Midpoint Calculator in Excel s Tools menu 00 ce eeeeesseeeeseeeeeneeeeeeeeeaees 3 Figure 2 Condensed interface of the calculator 0 ee eeseeeesneeeeseeeeeseeeeeeeeesseeseaees 4 Figure 3 Expanded interface of the calculator 0 0 eee eeescecesseeeesececeseeeceseeeesseeeeaees 5 Fig re 4 2 ee midpoint error OUI Uo ioaicse seis erage eaicseval nenni r eser ESEE EENE ES 10 2007 Technomics Inc This material may not be reproduced without express written consent from Technomics Inc You may not modify publish transmit participate in the sale of reproduce create derivative works from distribute display remove the copyright or trademark notice from any copies of the material or in any way exploit all or any part of the material including use as part of any library archive or similar service without prior written permission from Technomics Inc ii A7 Technomics he Technomics Midpoint Calculator is designed to be a practical day to day learning curve analysis tool as well as a midpoint theory learning tool The practical tool quickly calculates the midpoint of a lot and inserts either the answer or the formula directly into the spreadsheet The theoretical learning tool provides a derivation and formulation of various midpoint formulas along with explanations of how each formula compares and contrasts to other midpoint formulas This manual provides an introduction to the Midpoint Ca
11. mula List Technomics Midp Jint Calculator Approximation with Known Slope Asher O Approximation with Known Slope Corrected Parameter Free Approximation Mean Parameter Free Approximation AGM O ParameterFree Approximation ROT Slope free rule of thumb formula The midpoint of the first lot is estimated by Last 3 1 Subsequent lot midpoints are estimated as the number of prior units plus half the current lot size Last First 1 2 First 1 Copy Formula Show aako ai as Formula Description Formula Text Box Figure 2 Condensed interface of the calculator Clicking on Show Calculator expands the form to show the calculator inputs outputs and insertion options for the selected midpoint formulas Figure 3 2007 Technomics Inc Copyright notice on page 1 applies A7 Technomics Formula Selection Options 2007 Technomics Inc Copyright notice on page 1 applies Technomics Midpoint Calculator True Algebraic Midpoint Approximation with Known Slope Asher Approximation with Known Slope Corrected Parameter Free Approximation Mean Parameter Free Approximation AGM An approximation if the learning curve is not known calculated by taking the average of the arithmetic and geometric means of First and Last for all lots other than the first For the first lot a rule of thumb is used whereby the midpoint is one half the lot size
12. st The T seals The Formula Text Box provides a text version of the selected Text Boy formula Ifthe formula in the Formula Text Box begins with an equal sign then the formula may be copied and pasted directly into an Excel spreadsheet cell In order for Excel to properly evaluate the formula the variables First Last and b need to be replaced with either valid cell addresses or the appropriate values A NAME error will occur if the first Last or b variables are not defined as numbers cell addresses or named ranges Clicking Copy Formula will copy the text in the Formula Text Box to the Windows Clipboard Note The single cell formula used to evaluate the True Algebraic Midpoint is an Excel array formula In order for an array formula to evaluate correctly the numbers in the series must be fully Ay enumerated in contiguous cells between the first unit and the last unit In other words the number of cells being evaluated by the formula must equal the number of units in the lot and they should all be in the same row or column If the array formula operates on the incorrect number of cells the formula will return an incorrect answer If you are not familiar with Excel array formulas please refer to the example in the Appendix for more information 2007 Technomics Inc Copyright notice on page 1 applies 6 A7 Technomics Providing In order to calculate the midpoint you will need to provide the Inputs
13. t N The Science of Informed Decision Making Midpoint Calculator User Manual A7 Technomics Table of Contents Table or TIETE eee ee en ee ee eee il SEEN SEATS a tecasc cy E E E ce EEEN EA TO N EEI AE 1 Installing and Starting the Midpoint Calculator l NES ACS CIC ise E E E E E N 2 POM S EUO OD TONS aiee n E E E E EEE 3 POM AT npu t OPO WS sesiis es rnani R ARa E E E ERE E 4 CU ONS I OS eag A E E AE E A T 5 Formuli QUT PUL OPTIONS sacrcnssicecontionsccnes e ee ean a A a R eN E 5 Usne TW CAC UOT sas E T E EEE E E AE 6 SRE OIA EE EEE E E E OE E OAE ye saee 6 Thed ornnula TOKE DOR raui rnern aTr EE ESETE EEEE EEDE 6 POV T TIS e E E E E E E E S 7 CAI CUIGE AS NE Mid pOT seisa inenika OAREN R a N EEA 7 TOEN OT TOT EE EE tons E E E 8 HATES EE E LE EEEE E EEE N T E I E E 9 Gale OL OU DUE orii e Er ETE AN S A E EO O E EET 9 Appendix Using Excel Array Formulas to Calculate MIdpOInts ccccccccsssssceeeseeeees 10 2007 Technomics Inc This material may not be reproduced without express written consent from Technomics Inc You may not modify publish transmit participate in the sale of reproduce create derivative works from distribute display remove the copyright or trademark notice from any copies of the material or in any way exploit all or any part of the material including use as part of any library archive or similar service without prior written permission from Technomics Inc A7 Technomics Tab
14. utput box if an input is not valid 2007 Technomics Inc Copyright notice on page 1 applies 7 A7 Technomics Calculation Reason Error First First 0 First 0 First unit First unit number cannot be zero cannot be zero Last unit number cannot be zero First unit number must be greater than zero Last unit number must be greater than zero First gt Last First unit number must be less than last unit number number but the first unit number is not 1 Slope lt 0 Slope must be greater than 0 Slope 0 Slope cannot be 0 Asher 50 Slope cannot be equal to 50 when evaluating the Asher and corrected Asher formulas When the Asher or corrected Asher approximations are selected a Asher True True Midpoint Error calculation will appear below the midpoint Midpoint Error cwer Figure 4 Midpoint 10 098 True midpoint error 2 99003 Figure 4 True midpoint error output The true midpoint error is the error of the current midpoint estimate relative to the true algebraic midpoint or Estimated Midpoint Algebraic Midpoint Algebraic Midpoint Error In the example given in Figure 4 the estimated midpoint using Asher s approximation is 10 098 while the actual algebraic midpoint is 10 128 This leads to a relative error of 0 003 2007 Technomics Inc Copyright notice on page 1 applies 8 A7 Technomics First Lot The First Lot checkbox is enabled
Download Pdf Manuals
Related Search