3-1 CHAPTER 3 THE ART OF MODELING WITH SPREADSHEETS Review Questions 3.1-1 The long-term loan has a lower interest rate.3.1-2 The short-term loan is more flexible.They can borrow the money only in the years they need it.3.1-3 End with as large a cash balance as possible at the end of the ten years after paying off all the loans.3.2-1 Visualize where you want to finish.What should the answer look like?3.2-2 First,it can help clarify what formula should be entered for an output cell.Second,hand calculations help to verify the spreadsheet model.3.2-3 Sketch a layout of the spreadsheet.3.2-4 Try numbers in the changing cells for which you know what the values of the output cells should be.3.2-5 Relative references are based upon the position relative to the cell containing the formula.Absolute references refer to a specific cell address.3.3-1 Enter the data first.3.3-2 Numbers should be entered separately from formulas in data cells.3.3-3 With range names,the formulas and Solver dialogue box contain descriptive range names rather than obscure cell references.Use a range name that corresponds exactly to the label on the spreadsheet.3.3-4 Borders,shading,and colors can be used to distinguish data cells,changing cells,output cells,and target cells on a spreadsheet.3.3-5 Three.One for the left-hand-side,one for the inequality sign,and one for the right-hand-side.3.4-1 Try different values for the changing cells for which you can predict the correct result in the output cells and see if they calculate as expected.3.4-2 Control-on a PC(command-on a Mac).3.4-3 The auditing tools can be used to trace dependents or precedents for a given cell.名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 11 页 -3-2 Problems 3.1 1234567891011121314151617181920ABCDEFGHIJKLLT Rate7%ST Rate10%Sav ings Interest3%Start Balance1(all cash figures in millions of dollars)Minimum Cash0.5CashLTSTLTSTLTSTSav ingsMinimumYearFlowLoanLoanInterestInterestPay backPay backInterestBalanceBalance2003-87.500.000.50=0.502004-22.51-0.530.000.000.0150.50=0.502005-47.27-0.53-0.25-2.510.0150.50=0.50200635.51-0.53-0.73-7.270.0150.50=0.50200760.57-0.53-0.55-5.510.0150.50=0.50200830-0.53-0.06-0.570.0152.36=0.502009-42.59-0.53000.0709260.50=0.50201070-0.53-0.26-2.590.0154.14=0.502011-20-0.53000.1242341.74=0.502012100-0.53000.05221111.27=0.502013-0.530-7.5000.3380273.58=0.503.2 a.The COO will need to know how many of each product to produce.Thus,the decisions are how many end tables,how many coffee tables,and how many dining room tables to produce.The objective is to maximize total profit.b.Pine wood used=(3 end tables)(8 pounds/end table)+(3 dining room tables)(80 pounds/dining room table)=264 pounds Labor used=(3 end tables)(1 hour/end table)+(3 dining room tables)(4 hours/dining room table)=15 hours c.End TablesCof fee TablesDining Room TablesUnit Prof itResource Used per unit ProducedTotal UsedAv ailablePine Wood=Labor=End TablesCof fee TablesDining Room TablesTotal Prof itUnits Producedd.123456789ABCDEFGEnd TablesCof f ee TablesDining Room TablesUnit Profit$50$100$220Resource Used per unit ProducedTotal UsedAv ailablePine Wood815803000=3000Labor124200=200End TablesCof f ee TablesDining Room TablesTotal ProfitUnits Produced04030$10,600名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 11 页 -3-3 3.3 a.Top management will need to know how much to produce in each quarter.Thus,the decisions are the production levels in quarters 1,2,3,and 4.The objective is to maximize the net profit.b.Ending inventory(Q1)=Starting Inventory(Q1)+Production(Q1)Sales(Q1)=1,000+5,000 3,000=3,000 Ending inventory(Q2)=Starting Inventory(Q2)+Production(Q2)Sales(Q2)=3,000+5,000 4,000=4,000 Profit from sales(Q1)=Sales(Q1)*($20)=(3,000)($20)=$60,000 Profit from sales(Q2)=Sales(Q2)*($20)=(4,000)($20)=$80,000 Inventory Cost(Q1)=Ending Inventory(Q1)*($8)=(3,000)($8)=$24,000 Inventory Cost(Q2)=Ending Inventory(Q2)*($8)=(4,000)($8)=$32,000 c.Inv entory Holding CostGross Profit f rom SalesStartingMaximumDemand/EndingInv entoryGross ProfitInv entoryProductionProductionSalesInv entoryCostfrom SalesQuarter 1=Quarter 2=Quarter 3=Quarter 4=Net Profitd.12345678910ABCDEFGHIJKLMInv entory Holding Cost$8Gross Profit f rom Sales$20StartingMaximumDemand/EndingInv entoryGross ProfitInv entoryProductionProductionSalesInv entoryCostfrom SalesQuarter 11,0002,000=0$0$60,000Quarter 204,000=0$0$80,000Totals$0$140,000Net Profit$140,000e.123456789101112ABCDEFGHIJKLMInv entory Holding Cost$8Gross Profit f rom Sales$20StartingMaximumDemand/EndingInv entoryGross ProfitInv entoryProductionProductionSalesInv entoryCostfrom SalesQuarter 11,0003,000=0$8,000$60,000Quarter 21,0006,000=0$24,000$80,000Quarter 33,0006,000=0$8,000$160,000Quarter 41,0006,000=0$0$140,000Totals$40,000$440,000Net Profit$400,0003.4 a.Fairwinds needs to know how much to participate in each of the three projects,and what their ending balances will be.The decisions to be made are how much to participate in each of the three projects.The objective is to maximize the ending balance at the end o