Fluent Essays

PART-1 Please go through Examples 1.2 (  at page 12) and 1.4 (Sam’s Bookstore at page 20) from our textbook (in attachment ) and create Excel files as described in examples. Show all the work they implemented and get familiar with some nice features of Excel. Each example will be 20 POINTS Page : 38-41, 46-50 in attachment  PART-2 For each of below questions I provide the solution values. So for each question you need to:`z a) Understand problem b) Decide on decision variables c) Form your objective function d) Form your constraints e) Bring parts b, c and d all together to create a linear programming model f) Transfer this model into Excel g) Use solver to find optimum solution h) Interpret the results and tell me what would be your and interpretations recommendation based on this solution.  Deliverable: Create a single excel file and name it as Assignment week1_ yourname_lastname For each question use a separate Excel worksheet not different files. Name worksheet-1 as Example-1, worksheet-2 as Example-2, Worksheet-3 as Q1 and worksheet-4 as Q2 Show all of your model and interpretation in the same worksheet.  Please do not copy and paste from other students. Please remember that everybody’s design will be different and there is no chance that two student’s models would be exactly same.  Please also make sure that you used SOLVER for optimum solution.  DUE DATE : Please submit your excel file before our first class on Sept 10, 2021 before our next class. Late assignments will not be accepted. Question 1 30 POINTS A company produces two types of tables, T1 and T2. It takes 2 hours to produce the parts of one unit of T1, 1 hour to assemble and 2 hours to polish. .It takes 4 hours to produce the parts of one unit of T2, 2.5 hour to assemble and 1.5 hours to polish. Per month, 7000 hours are available for producing the parts, 4000 hours for assembling the parts and 5500 hours for polishing the tables. The profit per unit of T1 is $90 and per unit of T2 is $110. How many of each type of tables should be produced in order to maximize the total monthly profit?   Answer 1 The maximum profit is $273000 . Hence the company needs to produce 2300 tables of type T1 and 600 tables of type T2 in order to maximize its profit. Question-2 30 POINTS John has $20,000 to invest in three funds F1, F2 and F3. Fund F1 is offers a return of 2% and has a low risk. Fund F2 offers a return of 4% and has a medium risk. Fund F3 offers a return of 5% but has a high risk. To be on the safe side, John invests no more than $3000 in F3 and at least twice as much as in F1 than in F2. Assuming that the rates hold till the end of the year, what amounts should he invest in each fund in order to maximize the year end return?  Answer_2 The return R is maximum at 603.33. For maximum return, John has to invest $11333 in fund F1, $5667 in fund F2 and $3000 in fund F3. 1-3b Cost Projections In the following example, a company wants to project its costs of producing products, given that material and labor costs are likely to increase through time. We build a simple model and then use Excel’s charting capabilities to obtain a graphical image of projected costs. The model in Figure 1.6 is still not the last word on this example. As shown in later examples, you can create data tables to see how sensitive profit is to the inputs, the demand, and the order quantity. You can also create charts to show results graph- ically. But this is enough for now. You can see that the model in Figure 1.6 is now much more readable and flexible than the original model in Figure 1.2. 1 2     C h a p t e r 1     I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 A B C D E F NCAA t-shirt sales Input egnaRselbairav names used Fixed order cost $750 Demand ='Model 5'!$B$10 Variable cost $8 Discount_price ='Model 5'!$B$7 Selling price $18 Fixed_order_cost ='Model 5'!$B$4 Discount price $6 Order ='Model 5'!$B$13 Selling_price ='Model 5'!$B$6 Uncertain variable Variable_cost ='Model 5'!$B$5 0051dnameD Decision variable 0541redrO Output variables Costs Fixed cost $750 Variable costs $11,600 Revenues Full-price shirts $26,100 Discount-price shirts $0 $13,750tiforP Figure 1.6 Model with Category Labels and Color Coding EXAMPLE 1.2 PROJECTING THE COSTS OF BOOKSHELVES AT WOODWORKS The Woodworks Company produces a variety of custom-designed wood furniture for its customers. One favorite item is a bookshelf, made from either cherry or oak. The company knows that wood prices and labor costs are likely to increase in the future. Table 1.1 shows the number of board-feet and labor hours required for a bookshelf, the current costs per board-foot and labor hour, and the anticipated annual increases in these costs. (The top row indicates that either type of bookshelf requires 30 board-feet of wood and 16 hours of labor.) Build a spreadsheet model that enables the company to experiment with the growth rates in wood and labor costs so that a manager can see, both numerically and graphically, how the costs of the book- shelves increase in the next few years. resource Cherry Oak Labor Required per bookshelf 30 30 16 Current unit cost $5.50 $4.30 $18.50 Anticipated annual cost increase 2.4% 1.7% 1.5% Table 1.1 Input Data for Manufacturing a Bookshelf Objective To learn good spreadsheet practices, to create copyable formulas with the careful use of relative and absolute addresses, and to create line charts from multiple series of data. 09953_ch01_ptg01_001-036.indd 12 04/03/19 10:51 PM Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202 1-3 Introduction to Spreadsheet Modeling    1 3 Solution The completed spreadsheet model appears in Figure 1.7 and in the file Bookshelf Costs Finished.xlsx. You can develop it with the following steps. 1. Inputs. You should usually enter the inputs for a model in the upper-left corner of a work- sheet as you can see in the shaded ranges in Figure 1.7. We have used our standard conven- tion of coloring inputs—the numbers from the statement of the problem—blue. You can develop your own convention, but the input cells should be distinguished in some way. Note that the inputs are grouped logically and are explained with appropriate labels. You should always document your spreadsheet model with descriptive labels. Also, note that by entering inputs explicitly in input cells, you can refer to them later in Excel formulas. Always enter input values in input cells and then refer to them in Excel formulas. Do not bury input values in formulas. Figure 1.7 Bookshelf Cost Model Relative and Absolute Addresses in Formulas Relative and absolute addresses are used in Excel formulas to facilitate copying. A dollar sign next to a column or row address indicates that the address is absolute and will not change when copying. The lack of a dollar sign indi- cates that the address is relative and will change when copying. After you select a cell in a formula, you can press the F4 key repeatedly to cycle through the relative/absolute possibilities: =B4 (both column and row relative); =$B$4 (both column and row absolute); =B$4 (column relative, row absolute); and =$B4 (column absolute, row relative). Excel Tip Always try to organize your spreadsheet model so that you can copy formulas across multiple cells. 2. Design output table. Plan ahead for how you want to structure your outputs. We created a table where there is a row for every year in the future (year 0 corresponds to the current year), there are three columns for projected unit costs (columns B–D), and there are two columns for projected total bookshelf costs (columns E and F). The headings reflect this design. This isn’t the only possible design, but it works well. The important point is that you should have some logical design in mind before you dive in. 3. Projected unit costs of wood. The dollar values in the range B19:F25 are calculated from Excel formulas. Although the logic in this example is straightforward, it is still important to have a strategy in mind before you enter formulas. In particular, you should always try to design your spreadsheet so that you can enter a single formula and then copy it. This saves work and avoids errors. For the costs per board-foot in columns B and C, enter the formula =B9 in cell B19 and copy it to cell C19. Then enter the general formula 5B19*(11B$10) in cell B20 and copy it to the range B20:C25. We assume you know the rules for absolute and relative addresses (dollar sign for absolute, no dollar sign for relative), but it takes some planning to use these so that copying is possible. Make sure you understand why we made row 10 absolute but column B relative. Typing dollar signs in formulas for absolute references is inefficient. Press the F4 key instead. Press the Fn key and the F4 key (together) on Mac keyboards. 09953_ch01_ptg01_001-036.indd 13 04/03/19 10:51 PM Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202 4 Projected unit labor costs. To calculate projected hourly labor costs, enter the formula =B13 in cell D19. Then enter the formula 5D19*(11B$14) in cell D20 and copy it down column D. 5 Projected bookshelf costs. Each bookshelf cost is the sum of its wood and labor costs. By a careful use of absolute and relative addresses, you can enter a single formula for these costs—for all years and for both types of wood. To do this, enter the formula 5B$5*B191B$6*$D19 in cell E19 and copy it to the range E19:F25. The idea here is that the units of wood and labor per bookshelf are always in rows 5 and 6, and the projected unit labor cost is always in column D, but all other references are relative to allow copying. 6 Chart. A chart is a valuable addition to any table of data, especially in the busi- ness world, so charting in Excel is a skill worth mastering. We illustrate some of the possibilities here, but we urge you to experiment with other possibilities on your own. Start by selecting the range E18:F25—yes, including the labels in row 18. Next, click the Line dropdown list on the Insert ribbon and select the Line with Markers type. You instantly get the line chart you want, with one series for Cherry and another for Oak. Also, when the chart is selected (that is, it has a border around it), you see two Chart Tools tabs, Design and Format. There are also three useful buttons to the right of the chart. (These three buttons were intro- duced in Excel 2013, and the two tabs condense the tools in the three tabs from Excel 2007 and 2010.) The most important button is the Select Data button on the Design ribbon. It lets you choose the ranges of the data for charting in case Excel’s default choices aren’t what you want. (The default choices are based on the selected range when you create the chart.) Click Select Data now to obtain the dialog box in Figure 1.8. On the left, you control the series (one series or multiple series) being charted; on the right, you control the data used for the horizontal axis. By selecting E18:F25, you have the series on the left correct, including the names of these series (Cherry and Oak), but if you didn’t, you could select one of the series and click the Edit button to change it. The data on the horizontal axis are currently the default 1, 2, and so on. To use the data in column A, click the Edit button on the right and select the range A19:A25. Then you can experiment with various formatting options to make the chart even better. For example, we rescaled the vertical axis to start at $300 rather than $0 (right-click any of the numbers on the vertical axis and select Format Axis), and we added a chart title at the top and a title for the horizontal axis at the bottom. 1 4     C h a p t e r 1     I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s The many chart options are easily accessible from the Chart Tools tabs that are visible when a chart is selected. Don’t be afraid to experiment with them to produce professional-looking charts. Figure 1.8 Select Data Source Dialog Box The Select Data Source dialog box in Excel for Mac has a different layout, but the options are basically the same. The three buttons to the right of the chart don’t appear in Excel for Mac. 09953_ch01_ptg01_001-036.indd 14 04/03/19 10:51 PM Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202 Using the Model for What-If Questions The model in Figure 1.7 can now be used to answer many what-if questions. In fact, many models are built for the purpose of permitting experimentation with various scenarios. The important point is that the model has been built in such a way that a manager can enter any desired values in the input cells, and all outputs, including the chart, will update automatically. 1-3 Introduction to Spreadsheet Modeling    1 5 1-3c Breakeven Analysis Many business problems require you to find the appropriate level of some activity. This might be the level that maximizes profit (or minimizes cost), or it might be the level that allows a company to break even—no profit, no loss. The following example illustrates a typical breakeven analysis. EXAMPLE 1.3 BREAKEVEN ANALYSIS AT QUALITY SWEATERS The Quality Sweaters Company sells hand-knitted sweaters. The company is planning to print a catalog of its products and undertake a direct mail campaign. The cost of printing the catalog is $20,000 plus $0.10 per catalog. The cost of mailing each catalog (including postage, order forms, and buying names from a mail-order database) is $0.15. In addition, the company plans to include direct reply envelopes in its mailings and incurs $0.20 in extra costs for each direct mail envelope used by a respondent. The average size of a customer order is $40, and the company’s variable cost per order (primarily due to labor and material costs) averages about 80% of the order’s value—that is, $32. The company plans to mail 100,000 catalogs. It wants to develop a spreadsheet model to answer the following questions: 1. How does a change in the response rate affect profit? 2. For what response rate does the company break even? Objective To learn how to work with range names, to learn how to answer what-if questions with one-way data tables, to introduce Excel’s Goal Seek tool, and to learn how to document and audit Excel models with cell comments and Excel’s formula auditing tools. Solution The completed spreadsheet model appears in Figure 1.9. (See the file Breakeven Analysis Finished.xlsx.) First, note the clear layout of the model. The input cells are colored blue, they are separated from the outputs, headings are boldfaced, several headings are indented, numbers are formatted appropriately, and a list to the right spells out all range names we have used. (See the next Excel Tip on how to create this list.) Also, following the convention we use throughout the book, the decision variable (number mailed) is colored red, and the bottom-line output (profit) is colored gray. Creating Range Names To create a range name for a range of cells (which could be a single cell), highlight the cell(s), click in the Name Box just to the left of the Formula Bar, and type a range name. Alternatively, if a column (or row) of labels appears next to the cells to be range-named, you can use these labels as the range names. To do this, highlight the labels and the cells to be named (for example, A4:B5 in Figure 1.9), click Create from Selection on the Formulas ribbon, and make sure the appropriate box in the resulting dialog box is checked. The labels in our example are to the left of the cells to be named, so the Left column box should be checked. This is a quick way to create range names, and we did it for all range names in the example. Note that if a label contains any “illegal” range-name characters, such as a space, the illegal characters are converted to underscores. Excel Tip Adopt some layout and formatting conventions, even if they differ from ours, to make your spreadsheets readable and easy to follow. 09953_ch01_ptg01_001-036.indd 15 04/03/19 10:51 PM Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202 Figure 1.16 Precedents of Total_revenue Cell 1 2 3 4 5 6 7 8 9 10 11 12 13 EDCBA Quality Sweaters direct mail model Catalog ledoMstupni of responses Fixed cost of printing $20,000 Response %8etar Variable cost of printing mailing $0.25 Number of 0008sesnopser Decision ledoMelbairav of revenue, costs, and profit Number latoT000001deliam 000,023$euneveR Fixed cost of printing $20,000 Order inputs Total variable cost of printing mailing $25,000 Average latoT04$redro variable cost of orders $257,600 Variable cost per order $32.20 Total 006,203$tsoc 004,71$tiforP 1-3d Ordering with Quantity Discounts and Demand Uncertainty In the following example, we again attempt to find the appropriate level of some activity: how much of a product to order when customer demand for the product is uncertain. Two important features of this example are the presence of quantity discounts and the explicit use of probabilities to model uncertain demand. Formula Auditing Toolbar The formula auditing toolbar allows you to see dependents of a selected cell (which cells have formulas that reference this cell) or precedents of a given cell (which cells are referenced in this cell’s formula). You can even see dependents or precedents that reside on a different worksheet. In this case, the auditing arrows appear as dashed lines and point to a small spreadsheet icon. By double-clicking the dashed line, you can see a list of dependents or precedents on other worksheets. These tools are especially useful for understanding how someone else’s spreadsheet works. Excel Tool 2 0     C h a p t e r 1     I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s EXAMPLE 1.4 ORDERING WITH QUANTITY DISCOUNTS AT SAM’S BOOKSTORE Sam’s Bookstore, with many locations across the United States, places orders for all of the latest books and then distributes them to its individual bookstores. Sam’s needs a model to help it order the appropriate number of any title. For example, Sam’s plans to order a popular new hardback novel, which it will sell for $30. It can purchase any number of this book from the publisher, but due to quantity discounts, the unit cost for all books it orders depends on the number ordered. If the number ordered is less than 1000, the unit cost is $24. After each 1000, the unit cost drops: to $23 for at least 1000 copies; to $22.25 for at least 2000; to $21.75 for at least 3000; and to $21.30 (the lowest possible unit cost) for at least 4000. For example, if Sam’s orders 2500 books, its total cost is $22.25(2500) 5 $55,625. Sam’s is uncertain about the demand for this book—it estimates that demand could be anywhere from 500 to 4500. Also, as with most hardback novels, this one will eventually come out in paperback. Therefore, if Sam’s has any hardbacks left when the paperback comes out, it will put them on sale for $10, at which price it believes all leftovers will be sold. How many copies of this hardback novel should Sam’s order from the publisher? 09953_ch01_ptg01_001-036.indd 20 04/03/19 10:51 PM Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202 1-3 Introduction to Spreadsheet Modeling    2 1 Objective To learn how to build in complex logic with IF formulas, to get help about Excel functions, to learn how to use lookup functions, to see how two-way data tables provide answers to more extensive what-if questions, and to learn about Excel’s SUMPRODUCT function. Solution The profit model appears in Figure 1.17. (See the file Quantity Discounts Finished.xlsx.) The order quantity and demand in the Order_quantity and Demand cells are trial values. (Comments in these cells are a reminder of this.) You can enter any values in these cells to test the logic of the model. The Order_quantity cell is colored red because the company can choose its value. In contrast, the Demand cell is colored green to indicate that this input value is uncertain and is being treated explicitly as such. Also, a table is used to indicate the quantity discounts cost structure. You can use the following steps to build the model. 1 A B C D E F G H I J K Ordering decision with quantity egnaRstnuocsid names used: 2 3 4 5 6 7 8 9 10 11 12 !$ledoM=tsoC B$18 ytitnauQstupnI discount structure 9$E$:5$D$!ledoM=pukooLtsoC Unit cost - see table to tAthgir least Unit 21$B$!ledoM=dnameDtsoc Regular 6$B$!ledoM=ecirp_revotfeL00.42$003$ecirp Leftover 9$B$!ledoM=ytitnauq_redrO00.32$000101$ecirp 53$J$:53$B$!ledoM=seitilibaborP52.22$0002 Decision variable 91$B$!ledoM=tiforP57.12$0003 Order 5$B$!ledoM=ecirp_ralugeR03.12$00040052ytitnauq 71$B$!ledoM=euneveR Uncertain quantity Units_sold_at_leftover_price =Model!$B$16 Units_sold_at_regular_price =Model!$B$150002dnameD 13 14 15 16 17 18 19 Profit model Units sold at regular price 2000 Units sold at leftover price 500 000,56$euneveR 526,55$tsoC 573,9$tiforP Figure 1.17 Sam’s Profit Model 1. Inputs and range names. Enter all inputs and name the ranges as indicated. The Create from Selection shortcut was used to name all ranges except for CostLookup and Probabilities. For these latter two, you can select the ranges and enter the names in the Name Box—the “manual” method. 2. Revenues. The company can sell only what it has, and it sells any leftovers at the discounted sale price. Therefore, enter the following formulas in cells B15, B16, and B17: 5MIN(Order_quantity,Demand) 5IF(Order_quantity>Demand, Order_quantity-Demand,0) 5Units_sold_at_regular_price*Regular_price1Units_sold_at_leftover_price*Leftover_price The logic in the first two of these cells is necessary to account correctly for the cases when the order quantity is greater than demand and when it is less than or equal to demand. You could use the following equivalent alternative to the IF function in cell B16: 5 MAX(Order_quantity-Demand,0) fx Button and Function Library Group To learn more about an Excel function, click the fx button next to the Formula bar. This is called the Insert Function button, although some people call it the Function Wizard. If there is already a function, such as an IF function, in a cell and you then click the fx button, you will get help on this function. If you select an empty cell and then click the fx button, you can choose a function to get help on. (The same help is available from the Function Library group on the Formulas ribbon.) Excel Tool The fx button in Excel for Mac opens a Formula Builder pane to the right, but the functionality is essentially the same as in Excel for Windows. 09953_ch01_ptg01_001-036.indd 21 04/03/19 10:51 PM Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202 2 2     C h a p t e r 1     I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s 3. Total ordering cost. Depending on the order quantity, you can find the appropriate unit cost from the unit cost table and multiply it by the order quantity to obtain the total ordering cost. This can be accomplished with a complex nested IF for- mula, but a better way is to use the VLOOKUP function. Specifically, enter the formula 5VLOOKUP(Order_quantity,CostLookup,2)*Order_quantity in cell B18. The VLOOKUP part of this formula says to compare the order quantity to the first (leftmost) column of the table in the CostLookup range and return the corresponding value in the second column (because the third argument is 2). VLOOKUP The VLOOKUP function is one of Excel’s most useful functions. To use it, first create a vertical lookup table, with values to use for comparison listed in the left column of the table and corresponding output values in as many columns to the right as you like. (See the CostLookup range in Figure 1.17 for an example.) Then the VLOOKUP function takes three or four arguments: (1) the value you want to compare to the values in the left column of the table; (2) the lookup table range; (3) the index of the column you want the returned value to come from, where the index of the left column is 1, the index of the next column is 2, and so on; and optionally (4) TRUE (for an approximate match, the default) or FALSE (for an exact match). If you omit the last argument, the values in the left column of the table must be entered in ascending order. (See online help for more details.) If the last argument is TRUE or is omitted, Excel scans down the leftmost column of the table and finds the last entry less than or equal to the first argument. (In this sense, it finds an approximate match.) There is also an HLOOKUP function that works exactly the same way, except that the lookup table is arranged in rows, not columns. Excel Function 4. Profit. Calculate the profit with the formula 5Revenue-Cost Two-Way Data Table The next step is to create a two-way data table for profit as a function of the order quantity and demand (see Figure 1.18). To create this table, first enter a link to the profit with the formula 5Profit in cell A22, and enter possible order quantities and possible demands in column A and row 22, respectively. (We used the same values for both order quantity and demand, from 500 to 4500 in increments of 500. This is not necessary—the demand could change in increments of 100 or even 1—but it is reasonable. Perhaps Sam’s is required by the publisher to order in multiples of 500.) Then select Data Table from the What-If Analysis dropdown list on the Data ribbon, and enter the Demand cell as the Row Input cell and the Order_quantity cell as the Column Input cell. A two-way data table allows you to see how a single output varies as two inputs vary simultaneously. 21 A B C D E F G H I J Data table of profit as a function of order quantity (along side) and demand (along top) 22 23 24 25 26 27 28 29 30 $9,375 500 1000 1500 2000 2500 3000 3500 4000 4500 500 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 1000 -$3,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 1500 -$9,500 $500 $10,500 $10,500 $10,500 $10,500 $10,500 $10,500 $10,500 2000 -$14,500 -$4,500 $5,500 $15,500 $15,500 $15,500 $15,500 $15,500 $15,500 2500 -$20,625 -$10,625 -$625 $9,375 $19,375 $19,375 $19,375 $19,375 $19,375 3000 -$25,250 -$15,250 -$5,250 $4,750 $14,750 $24,750 $24,750 $24,750 $24,750 3500 -$31,125 -$21,125 -$11,125 -$1,125 $8,875 $18,875 $28,875 $28,875 $28,875 4000 -$35,200 -$25,200 -$15,200 -$5,200 $4,800 $14,800 $24,800 $34,800 $34,800 31 4500 -$40,850 -$30,850 -$20,850 -$10,850 -$850 $9,150 $19,150 $29,150 $39,150 Figure 1.18 Profit as a Function of Order Quantity and Demand 09953_ch01_ptg01_001-036.indd 22 04/03/19 10:51 PM Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Copyright 2020 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-202 1-3 Introduction to Spreadsheet Modeling    2 3 Two-Way Data Table A two-way data table allows you to see how a single output cell varies as you vary two input cells. Unlike a one- way data table, only a single output cell can be used. To create this type of table, enter a reference to the output cell in the top-left corner of the table, enter possible values of the two inputs below and to the right of this corner cell, and select the entire table. Then select Data Table from the What-If Analysis dropdown on the Data ribbon, and enter references to the cells where the original two input variables live. The Row Input cell corresponds to the values along the top row of the table, and the Column Input cell corresponds to the values along the left-most column of the table. When you click OK, Excel substitutes each pair of input values into these two input cells, recalculates the spreadsheet, and enters the corresponding output value in the table. Excel Tool SUMPRODUCT The SUMPRODUCT function takes two range arguments, which must be exactly the same size and shape, and it sums the products of the corresponding values in these two ranges. For example, the formula 5SUMPRODUCT(A10:B11,E12:F13) is a shortcut for a formula involving the sum of four products: 5A10*E12 1A11*E13 1B10*F12 1B11*F13. This is an extremely useful function, especially when the rang- es involved are large, and it is used repeatedly throughout the book. (Actually, the … PART-1 Please go through Examples 1.2 ( at page 12) and 1.4 (Sam’s Bookstore at page 20) from our textbook and create Excel files as described in examples. Show all the work they implemented and get familiar with some nice features of Excel. Each example will be 20 POINTS Page : 38-41, 46-50 in attachment PART-2 For each of below questions I provide the solution values. So for each question you need to:`z a) Understand problem b) Decide on decision variables c) Form your objective function d) Form your constraints e) Bring parts b, c and d all together to create a linear programming model f) Transfer this model into Excel g) Use solver to find optimum solution h) Interpret the results and tell me what would be your and interpretations recommendation based on this solution. Deliverable: Create a single excel file and name it as Assignment week1_ yourname_lastname For each question use a separate Excel worksheet not different files. Name worksheet-1 as Example-1, worksheet-2 as Example-2, Worksheet-3 as Q1 and worksheet-4 as Q2 Show all of your model and interpretation in the same worksheet. Please do not copy and paste from other students. Please remember that everybody’s design will be different and there is no chance that two student’s models would be exactly same. Please also make sure that you used SOLVER for optimum solution. DUE DATE : Please submit your excel file before our first class on Sept 10, 2021 before our next class. Late assignments will not be accepted. Question 1 30 POINTS A company produces two types of tables, T1 and T2. It takes 2 hours to produce the parts of one unit of T1, 1 hour to assemble and 2 hours to polish. .It takes 4 hours to produce the parts of one unit of T2, 2.5 hour to assemble and 1.5 hours to polish. Per month, 7000 hours are available for producing the parts, 4000 hours for assembling the parts and 5500 hours for polishing the tables. The profit per unit of T1 is $90 and per unit of T2 is $110. How many of each type of tables should be produced in order to maximize the total monthly profit? Answer 1 The maximum profit is $273000 . Hence the company needs to produce 2300 tables of type T1 and 600 tables of type T2 in order to maximize its profit. Question-2 30 POINTS John has $20,000 to invest in three funds F1, F2 and F3. Fund F1 is offers a return of 2% and has a low risk. Fund F2 offers a return of 4% and has a medium risk. Fund F3 offers a return of 5% but has a high risk. To be on the safe side, John invests no more than $3000 in F3 and at least twice as much as in F1 than in F2. Assuming that the rates hold till the end of the year, what amounts should he invest in each fund in order to maximize the year end return? Answer_2 The return R is maximum at 603.33. For maximum return, John has to invest $11333 in fund F1, $5667 in fund F2 and $3000 in fund F3. Sheet1 Fixed Cost 750 Variable Cost 8 Selling Price 18 Price after certain time 6 Estimated Demand 1500 Order Quantity 1520 NET PROFIT 14210 COSTS 12910 Fixed Cost 750 Variable Cost 12160 REVENUES 27120 Full price tshirts 27000 Discounted price tshirts 120 PROFIT 14210 ORDERING NCAA T-SHIRTS It is March, and the annual NCAA Basketball Tournament is down to the final four teams. Randy Kitchell is a T-shirt vendor who plans to order T-shirts with the names of the final four teams from a manufacturer and then sell them to the fans. The fixed cost of any order is $750, the variable cost per T-shirt to Randy is $8, and Randy’s selling price is $18. However, this price will be charged only until a week after the tournament. After that time, Randy figures that interest in the T-shirts will be low, so he plans to sell all remaining T-shirts, if any, at $6 each. His best guess is that demand for the T-shirts during the full-price period will be 1500. He is thinking about ordering 1450 T-shirts, but he wants to build a spreadsheet model that will let him experiment with the uncertain demand and his order quantity. How should he proceed? Objective To build a spreadsheet model in a series of stages, with all stages being correct but each stage being more readable and flexible than the previous stages. Sheet2 INPUT VARIABLES Fixed cost 20000 printing cost per catalog 0.1 postage, getting ustomer inf, order form) 0.15 return mail 0.2 average revenue 40 cost of product and ops 32 Quantity planned 100000 RESPONSE RATE 2.00% TOTAL PRINTING and SENDING COST 45000 Return mail cost 400 Revenue 80000 Cost of production 64000 Response Rate Profit TOTAL REVENUE 80000 -29400 TOTAL COST 109400 1% -37200 PROFIT -29400 2.00% -29400 3% -21600 4.00% -13800 5% -6000 6.00% 1800 7% 9600.0000000001 8.00% 17400 9% 25200 10.00% 33000 11% 40800 12.00% 48600 13% 56400 14.00% 64200.0000000001 15% 72000 16.00% 79800 17% 87600 BREAKEVEN ANALYSIS AT QUALITY SWEATERS The Quality Sweaters Company sells hand-knitted sweaters. The company is planning to print a catalog of its products and undertake a direct mail campaign. The cost of printing the catalog is $20,000 plus $0.10 per catalog. The cost of mailing each catalog (including postage, order forms, and buying names from a mail-order database) is $0.15. In addition, the company plans to include direct reply envelopes in its mailings and incurs $0.20 in extra costs for each direct mail envelope used by a respondent. The average size of a customer order is $40, and the company’s variable cost per order (primarily due to labor and material costs) averages about 80% of the order’s value—that is, $32. The company plans to mail 100,000 catalogs. It wants to develop a spreadsheet model to answer the following questions: 1. How does a change in the response rate affect profit? 2. For what response rate does the company break even? Objective To learn how to work with range names, to learn how to answer what-if questions with one-way data tables, to introduce Excel’s Goal Seek tool, and to learn how to document and audit Excel models with cell comments and Excel’s formula auditing tools. 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 0.11 0.12 0.13 0.14000000000000001 -37200 -29400 -21600 -13800 -6000 1800 9600.0000000000582 17400 25200 33000 40800 48600 56400 64200.000000000058 Sheet3 Dev. Cost 15 Gross margin for year 1 1.5 Peak year 8 Increase rate(1-8) 0.06 Decrease rate(9-20) 0.05 Discount rate 0.075 Year Gross margin 1 $1.50 2 $1.59 3 $1.69 4 $1.79 5 $1.89 6 $2.01 $3.06 $3.06 5.00% 6.00% 7.00% 8.00% 9.00% 10.00% $3.06 $0.03 4.00% 5.00% 6.00% 7.00% 8.00% 7 $2.13 1 -4.0127075944 1 -4.0127075944 -4.0127075944 -4.0127075944 -4.0127075944 -4.0127075944 -4.0127075944 1 -4.0127075944 -4.0127075944 -4.0127075944 -4.0127075944 -4.0127075944 -4.0127075944 8 $2.26 2 -2.902061497 2 -3.003029324 -2.902061497 -2.8010936699 -2.7001258429 -2.5991580158 -2.4981901888 2 -3.2049649781 -3.103997151 -3.003029324 -2.902061497 -2.8010936699 -2.7001258429 9 $2.14 3 -1.8221265608 3 -2.0305321688 -1.8221265608 -1.6118685773 -1.3997582182 -1.1857954836 -0.9699803735 3 -2.4417862582 -2.2370854012 -2.0305321688 -1.8221265608 -1.6118685773 -1.3997582182 10 $2.04 4 -0.7742358529 4 -1.0957935806 -0.7742358529 -0.4470444035 -0.1141683624 0.2244431407 0.5688409758 4 -1.7222113515 -1.4117684567 -1.0957935806 -0.7742358529 -0.4470444035 -0.1141683624 11 $1.93 5 0.2400921865 5 -0.1995293852 0.2400921865 0.6911088106 1.1537251185 1.6281475999 2.1145846027 5 -1.0453966072 -0.627958678 -0.1995293852 0.2400921865 0.6911088106 1.1537251185 12 $1.84 6 1.2191327126 6 0.6573934564 1.2191327126 1.8000305047 2.4006000606 3.0213639113 3.6628539749 6 -0.4106163532 0.1143087265 0.6573934564 1.2191327126 1.8000305047 2.4006000606 13 $1.75 7 2.1609310029 7 1.4739427014 2.1609310029 2.8768329192 3.6226755001 4.3995136644 5.2084307072 7 0.1827350814 0.814868332 1.4739427014 2.1609310029 2.8768329192 3.6226755001 14 $1.66 8 3.0632762614 8 2.2489051488 3.0632762614 3.9182597826 4.8156488695 5.7573014176 6.7451418268 8 0.7351408423 1.4734162925 2.2489051488 3.0632762614 3.9182597826 4.8156488695 15 $1.58 9 3.923672382 9 2.9808692723 3.923672382 4.9206397794 5.9746246014 7.0886084399 8.2657060109 9 1.2469584959 2.0895012231 2.9808692723 3.923672382 4.9206397794 5.9746246014 16 $1.50 10 4.7393053226 10 3.6682059864 4.7393053226 5.8798341413 7.0940329616 8.3863707857 9.7615554749 10 1.7184164145 2.6625140894 3.6682059864 4.7393053226 5.8798341413 7.0940329616 17 $1.42 18 $1.35 19 $1.28 $3.06 5.00% 6.00% 7.00% 8.00% 9.00% 10.00% 20 $1.22 SUM 34.5433635555 1 -4.0127075944 -4.0127075944 -4.0127075944 -4.0127075944 -4.0127075944 -4.0127075944 SUM of NPV $18.06 2 -3.003029324 -2.902061497 -2.8010936699 -2.7001258429 -2.5991580158 -2.4981901888 Dev cost $15.00 3 -2.0305321688 -1.8221265608 -1.6118685773 -1.3997582182 -1.1857954836 -0.9699803735 NET PROFIT/LOSS $3.06 4 -1.0957935806 -0.7742358529 -0.4470444035 -0.1141683624 0.2244431407 0.5688409758 DECISION SUGGESTION YOU MAY INVEST since NPV of cashflows is positive 5 -0.1995293852 0.2400921865 0.6911088106 1.1537251185 1.6281475999 2.1145846027 6 0.6573934564 1.2191327126 1.8000305047 2.4006000606 3.0213639113 3.6628539749 7 1.4739427014 2.1609310029 2.8768329192 3.6226755001 4.3995136644 5.2084307072 8 2.2489051488 3.0632762614 3.9182597826 4.8156488695 5.7573014176 6.7451418268 9 2.9808692723 3.923672382 4.9206397794 5.9746246014 7.0886084399 8.2657060109 10 3.6682059864 4.7393053226 5.8798341413 7.0940329616 8.3863707857 9.7615554749 CALCULATING NPV AT ACRON Acron is a large drug company. At the current time, the beginning of year 0, Acron is trying to decide whether one of its new drugs, Niagra, is worth pursuing. Niagra is in the final stages of development and will be ready to enter the market one year from now. The final cost of development, to be incurred at the beginning of year 1, is $15 million. Acron estimates that the demand for Niagra will gradually grow and then decline over its useful lifetime of 20 years. Specifically, the company expects its gross margin (revenue minus cost) to be $1.5 million in year 1, then to increase at an annual rate of 6% through year 8, and finally to decrease at an annual rate of 5% through year 20. Acron wants to develop a spreadsheet model of its 20-year cash flows, assuming its cash flows, other than the initial development cost, are incurred at the ends of the respective years. (To simplify the model, taxes are ignored.) Using an annual discount rate of 7.5% for the purpose of calculating NPV, the drug company wants to answer the following questions: 1. Is the drug worth pursuing, or should Acron abandon it now and not incur the $15 million development cost? 2. How do changes in the model inputs change the answer to question 1? Sheet4                       WASHER                    DRYER      CAPACITY Production              2 hrs                          3 hrs       120 Assembly                2 hrs                            1 hr 80 Packaging               4  hrs                           4 hrs 400 PROFITS 60 Washer Dryers CAPACTY Prod 2 3 120 Asse 2 1 80 Packja 4 4 400 Profit 60 70 NUMBERS TO RPODUCE 30 20 PROFIT 3200 USED CAPACITY Production 120 <= 120 Assembly 80 <= 80 Packaging 200 <= 400 We are producing washers and dryers. For the production each machine requires some processing hours in Production, Assembly, and Packaging departments. For each washer and dryer required hours are given as:                             WASHER                    DRYER      Production              2 hrs                          3 hrs       Assembly                2 hrs                            1 hr Packaging               4  hrs                           4 hrs Currently the company has weekly capacity of 120 hours of Production Hours , 80 Assembly hours and 400 Packaging hours. Each washer can be sold  with 60 SAR profit and each Dryer can be sold with 70 SAR profit. a) What is the problem we should be focusing here? b) What would you do? c) What is the max amount of profit this company can make based on current conditions? d) Create LP model on paper e) Create model in Excel f) Use solver to solve it g) Interpret the results and help decision makers with your comments