CHAPTER 3 THE CONCEPTUAL PAPER WORKSHEET To solve

CHAPTER 3 THE CONCEPTUAL PAPER WORKSHEET To solve the Break-Even Model Through a table of relevant data, finding the answer where PROFIT is exactly zero. l Through a graphical representation of the relevant data, finding the answer from the graph. l Through a system of linear simultaneous equations, finding the answer by solving the system of linear equations. l

CONCEPTUAL PAPER WORKSHEET (CPW) To highlight the important modelling and package handling issues l The construction of a CPW is useful in three ways : l To clarify any academic modelling issues. l To determine the size of the worksheet and the range of cells that will be used. l To identifies the range of package facilities that will need to be activated in constructing the model through a worksheet. l

WHAT IS A WORKSHEET ? l A large two dimensional piece of paper l l l COLUMNS---LETTER LABELS ROWS--- NUMBER LABELS CELL/BOX---combination of COLUMN and ROW Example Table 3. 1 On WORKSHEET, the user enters instructions that enable the package to perform relevant data manipulations. Two software packages l The SPREADSHEET package EXCEL l l To be controlled through MENUS The STATISTICAL package MNITAB l To be controlled through COMMANDS

User inputs of CPW l A clear specification of the academic issues (THE THEORETICAL MODEL ) Is the problem properly defined? l Is the model properly structured? l Are all the variables required specified? l Are the variables of interest properly defined? l l Explicit recognition of the package handling facilities(BUILDING A WORKSHEET )

SECTION I: CREATING A TABLE OF DATA Four inputs into the Break-Even model SELLING PRICE per unit (p) , l FIXED COSTS (FC) , l VARIABLE COST per unit (VC) , and l RANGE of OUTPUTS l q---quantity to produce l To determine SIZE of the worksheet to be created. l

BASIC INPUTS l A 1—A 23 (Table 3. 2) l l l Cell A 9: FORMULA : =A 8+10 '=' means that this cell input is a formula and not a label or a number COPY the IDEA CONTAINED in A 9 INTO A 10 and then ALL THE WAY THROUGH TO A 23. TOTAL REVENUE COLUMN l l B 7—TR cell B 8: FORMULA : =$B$3*A 8 l l PRICEx. QUANTITY $--RELATIVE and an ABSOLUTE CELL ADDRESS. X is replaced by * COPY the idea contained in B 8 into the cells B 9 THROUGH TO B 23

TOTAL COST COLUMN C 7—TC l C 8: FORMULA : =$B$4+$B$5*A 8 l l l TC = FC + VCq COPY the idea contained in C 8 into the cells C 9 THROUGH TO C 23 PROFIT D 7—PROFIT l D 8: FORMULA : =B 8 -C 8 l Copy the FORMULA in cell D 8 into D 9 THROUGH TO D 23 l

FILE HANDLING FACILITIES , FILE/SAVE l FILE/OPEN l SUMMRY DATA ENTRY : entering both labels/names and formula l COPYING : to copy a formula to other l FILE HANDLING : to save and retrieve the worksheet l

SECTION II: WORKSHEET GRAPHS What kind of graph is to be constructed? l pie charts, bar graphs , X-Y graph, … What is to measured along the X-axis? l Q– quantity produced What is to be measured up the Y-axis? l TR , TC and PROFIT Labelling the Lines (TR , TC and PROFIT )

Graph Titles l Break-Even Analysis Scaling of Axes

SECTION III: GENERATING A NUMERICAL SOLUTION Matrix A and Matrix b l l E 1—E 4: TR F 1—F 4: TC G 1—G 4: q Matrix A is in the range E 2 to G 4

H 1—H 4: RHS l Matrix b is in the range H 2 to H 4 l Where to put the answer? l J 1—J 4: ANSWER To generate the answer by the following steps: inform the package as to the whereabouts of the matrix A. l inform the package that we would like to form the inverse of A , A-1. l

inform the package where in the spreadsheet we would like to store the inverse matrix A-1 l inform the package as to the whereabouts of the matrix b l inform the package that we would like to perform the multiplication A-1 b l inform the package where we would like the answer to be displayed l