Popular in Course
Popular in Music
This 30 page Class Notes was uploaded by Elliot Larkin PhD on Monday September 21, 2015. The Class Notes belongs to APBS 1000 at Georgia State University taught by Staff in Fall. Since its upload, it has received 12 views. For similar materials see /class/209816/apbs-1000-georgia-state-university in Music at Georgia State University.
Reviews for BASSOON
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 09/21/15
Chapter 2 J Introduction to Spreadsheet Modeling pl Introduction Excel skills are critical 0 0 Spreadsheets are made up of columns rows and their intersections are called cells 0 In each cell there may be the following types of data text labels number data constants formulas mathematical equations that do all the work 0 Excel s features will provide insight into solving real business problems p I fr Basic Spreadsheet Modeling Concepts and Best Practices Most mathematical models including spreadsheet models involve inputs decision variables and outputs The model inputs are given values that are fixed The decision variables are values that a decision maker has control over oz The model outputs are the ultimate values of interest Transforming Model Inputs into Output Uncontrollable Inputs Environmental Factors Controllable Inputs Mathematical i Decision Model I Variables Spreadsheet Modeling Spreadsheet modeling is the process of entering the inputs and decision variables into a spreadsheet and then relating them appropriately by means of formulas to obtain the outputs Once a model is created there are several directions in which to proceed Sensitivity analysis to see how one or more outputs change as selected inputs or decision variables change Finding the value of a decision variable that maximizes or minimizes a particular output Create graphs to show graphically how certain parameters of the model are related 0 09 pl Spreadsheet Modeling Cont d oz Good spreadsheet modeling practices are essential Spreadsheet models should be designed with readability in mind Several features that improve readability include A clear logical layout to the overall model Separation of different parts of a model Clear headings for different sections ofthe model Liberal use of range names Liberal use of formatting features Liberal use of cell comments Liberal use of text boxes for assumptions lists or explanations pl Example 21 Building a Model Randy Kitchell is a NCAA tshirt vendor The fixed cost of any order is 750 the variable cost is 6 per shirt The selling price is 10 per shirt while the leftover will be salvaged at 4 a piece The expected demand at full price is 1500 shirts oz He wants to build a spreadsheet model that will let him experiment with the uncertain demand and his order quantity p I fr EX 21cont d Building a Model 2 The logic behind the model is simple An Excel IF function will be used 4 In this base model the profit is calculated with the formula 7506B4IFB 3gtB410B410BB4B4BB 2 This model is entirely correct but it is not very readable or flexible l EX 21cont d Building a Model The formula can be rewritten to be more flexible B3B4BQFBsgtBQ10BsBGB9B8 It can be made more readable by using range names The formula would then read FixedordercostVariabecost0rder FDemand gt Order Sellingprice0rder 10DemandSalvagevalue OrderDemand o o 0 Ex 21cont d Building a Model Randy might like to have profit broken down into various costs and revenues rather one single profit cell The profit formula would be B12B13B15B16 Range names could be used for these intermediate output cells but it is probably more work than it is worth Labels andor color coding can help a lot with readability Business Modeling EX 21cont d Building a Model oz Data tables could be used to see how sensitive profit is to the inputs the demand and the order quantity and charts to show any numerical results graphically pl Example 22 Cost Projections oz The company knows that wood prices and labor costs are likely to increase in the future and it would like to project its costs of manufacturing the bookshelves into the future The data Table 21 Requirements per bookshelf Cherr Oak Boardfeet required Labor hours required 16 1 Cherr Oak 730 43 24 17 185 15 Costs of wood Current cost per boardfoot Projected annual increase Cost of labor Current cost per labor hour Projected annual increase oz Build a spreadsheet model that allows the company to experiment with the growth rates in wood and labor costs p I fr EX 22cont d Flaming the Model The reasoning behind the model is straightforward oz First project the unit costs for wood and labor into the future Then for any year multiply the unit costs by the required numbers of boardfeet and labor hours per bookshelf Finally add the wood ad labor costs to obtain the total cost of a bookshelf EX 22cont d The Model pl EX 22cont d Developing the Model Develop the model with the following steps gt Inputs Enterthe inputs into the upper left corner ofa worksheet These can be referred to later with Excel formulas gt Design output table You need to think ahead oftime how you want to structure your outputs The important point is that you should have some logical design in mind before diving in gt Projected unit costs of wood It is important to have a strategy in mind before you enterthe formulas You should design your spreadsheet so that you can enter a single formula and then copy it whenever possible pl EX 22cont d Developing the Model For example enter the formula B9 in cell 819 and copy it to cell C19 Then enter the general formula B191B10 in cell B20 and copy it to the range B20C25 gt Projected unit labor costs To calculate projected hourly labor costs enterthe formula B13 in cell D19 Then enter the formula D191B14 in cell D20 and copy it down to column D gt Projected bookshelf costs With careful use ofabsolute and relative addresses enter a single formula for these costs for all years and for both types of wood To do this enterthe formula B5B19B6D19 in cell E19 and copy it to the range E19F25 Business Modeling o o o 0 99 Developing the Model continued gt Chart Highlight the range E19F25 and click on Excel s Chart Vl zard button This leads you through a sequence of steps You should experiment with the possibilities The model can be used to answer any whatif questions Woodworks might want to ask The model has been built in such a way that a manager can enter any desired values in the input cells and all of the outputs including the chart will update automatically Burying input numbers inside Excel formulas is bad practice p I In 24 Breakeven Analysis Many business problems require us to find the appropriate level of some activity This might be the level that maximizes profit or it might be the level that allows a company to break even no profit no loss Business Modeling E HI I xample 23 Breakeven Analysis The Great Threads Company is planning to print a brochure of its products and undertake a direct mail campaign The cost of printing the brochure is 20000 plus 010 a catalog The cost of mailing each catalog is 015 In addition the company will include direct reply envelopes in it s mailings t incurs 020 in extra cost for each direct mail envelope that is used by a respondent 4 The average size of a customer order is 40 and the company s variable cost per order averages around 80 of the order s value pl EX 23cont d Breakeven Analysis The company plans to mail 100000 catalogs It wants to develop a spreadsheet model to answer the following questions gt How does a change in the response rate affect profit gt For what response rate does a company break even gt Ifthe company estimates a response rate of 3 should it proceed with the mailing gt How does the presence of uncertainty affect the usefulness ofthe model EX 23cont d Flaming the Model A single bottom line output variable in this case profit is of most concern The logic for converting inputs and the decision variable into outputs is quite straightforward Then it must be investigated how the response rate affects the profit with a sensitivity analysis pl EX 23cont d Developing the Model oz To create this model proceed through the following steps 1 Heading and range names Be cautious not to go overboard with range names Enter input values Some ofthe values have been combined in the statement ofthe problem To document this process enter comments in a few cells Inserting comments in cells is a great way to document your spreadsheet models without making it too cluttered Model the responses Enter any reasonable value such as 8 in the Responerate cell NumbermailedResponserate in cell E5 p I fr EX 23cont d Developing the Model 4 Model the revenues costs and profits Enter the formula NumberofresponsesAverageorder in the in cell E8 Enter the formula Fixedcostofprinting VariablecostofprintingmailingNumbermailed and NumberofresponsesVariablecostperorder in cells E9 E10 and E11 Enterthe formula SUME9E11 in the cell E12 and enter the formula TotalrevenueTotalcost in the cell E13 EX 23cont d Data Table A a oneway data table is formed to show how profit varies with the response rate l Data tables are called whatif tables They illustrate what happens to selected outputs if selected inputs change From the data table it can be seen that profit changes from negative to positive when the response rate is somewhere between 5 and 6 This could be found by trial and error but it is easier to find with Excel s Goal Seek tool o o 0 Ex 23cont d Goal Seek Goal seek is useful for solving a single equation in a single unknown The unknown is called the changing cell because it is allowed to be changed to make the equation true Select the ToolsGoal Seek menu item and fill in the resulting dialog box o If the response rate is 577 Great Threads breaks even pl EX 23cont d Limitations of the Model Question 3 asks whether the company should proceed with the mailing if the response rate is only 3 The apparent answer is no because profit is negative This reasoning is taking the shortterm view To consider the long term impact of our decisions the model must incorporate the long term explicitly into the model To do this a more complex model must be built EX 23cont d Limitations of the Model Question 4 asks about the impact of uncertainty in the model It makes more sense to talk about the probability that profit will have a certain value or the probability that the company will break even Business Modeling pl 26 Decisions Involving the Time Value of Money Cash flows are received at different points in time and a company must determine a course of action that maximizes the value of cash flows The later a dollar is received the less valuable the dollar is This is useful in making decisions 100 X 11r now 100 a year from now oz The value 11r in the above equation is called the discount factor Fl ii The quantity on the left is called the present value of 100 received a year from now If money can be invested at annual rate r compounded each year then 1 received tyears from now has the same value as 11rt dollars received today that is the 1 is discounted by the discount factor raised to the 2 power By multiplying a cash flow received tyears from now by 11rt its present value then the total value of all cash flows over all years is called the net present value NPV of our cash flows pl The rate r usually called the discount rate used by major corporations generally comes from some version of the capital asset pricing model The discount factor is 1 divided by 1 plus the discount rate The NPV is the sum of all discounted cash flows
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'