90 likes | 222 Views
Excel Data Tables. DSC340 Mike Pangburn. Excel Data Table. T he single most powerful Excel feature this side of VBA for automating repetitive spreadsheet calculations. You want to be familiar with the Excel Data Table. Data Tables Excel is very powerful at making calculations.
E N D
Excel Data Tables DSC340 Mike Pangburn
Excel Data Table The single most powerful Excel feature this side of VBA for automating repetitive spreadsheet calculations. You want to be familiar with the Excel Data Table
DataTables Excelisverypowerfulat making calculations. LoanPayments: PMT(rate,nPeriods,pValue) Whatifwewanttoseehowourcalculationschangeaswechangethe parameters? Options: (i) We can manually adjust values and try to remember earlier results (ii) We can copy the formula across many cells and use different inputs for each copy (iii) We can use the best approach: the Excel Data Table Twodifferent types of DataTables: One-wayDataTables:Allows you tochangeoneparameter. Two-wayDataTables:Allows you tochangetwoparameters.
One-WayDataTables Howtocreateone-waydatatables:: Creating a Data Table (example) • WehaveaparameterstoredinQ5 And,ourformula f()usestheparameter. • Wewanttoseehowtheresultchanges. whenweuseval1,val2,val3insteadofval. • Wefirstcreateatableset-up. • Then, chooseallthecellsintheset-uptable. • Goto:MenuData->Table(inoldExcel). • Goto:MenuData->What-ifAnaylsis->DataTable (inExcel2010). Equatethetopcelltothecellcontaining the formula. DataTableOptions RowInputCell?LeaveBlank ColumnInputCell?Choosethecellcontainingval(i.e.Q5) Writedownthevaluesthatweuseinstead ofval.
Example: CarLoanPayments • Let’s create a 1-way Data Table: Car Loan Payments • Download Practice-advExcel-DataTables.xlsxfrom the course website • Weborrow$20000fromacreditortobuyacar. • Annualinterestrateis5%. • Weplantopaytheloanin36months. • OurmonthlypaymentscanbecalculatedbyPMTfunction: • MonthlyPayment=PMT(5/12,36,20000) • How do our payments change as theinterestratechanges?
Two-WayDataTables ExcelDataTablesletsustoseetheimpactofchangeinparameters. Two-wayDataTables:Allowstochangetwoparameters. HowtocreateDataTable (example) Howtocreatetwo-waydatatables: • WehavetwoparametersstoredinR5andR6. And,ourformulausesbothoftheseparameters. • Wewanttoseehowtheresultchanges whenweuseval1,val2,val3insteadofval, andpara1,para2insteadofpara. • Wefirstcreateatableset-up. • Then, chooseallthecellsintheset-uptable. • Goto:MenuData->Table(inoldExcel). • Goto:MenuData->What-ifAnaylsis->DataTable. (in Excel 2010) Write down the values that we use instead of para. Equatethetop-leftcelltothe cellcontaining the formula. DataTableOptions RowInputCell?Choosethecellcontainingpar(i.e.R6) ColumnInputCell?Choosethecellcontainingval(i.e.R5) Writedownthevaluesthatweuseinstead ofval.
Two-WayDataTables:Examples • Let’screatea couple two-waydatatables as examples: • MortgagePayments • MarketingProblem • Again we will use Practice-advExcel-DataTables.xlsxfrom the course website
Example 2: MortgagePayments Weborrow$300000fromacreditortobuyahouse. Annualinterestrateis5%. Weplantopaytheloanin120months. OurmonthlypaymentscanbecalculatedbyPMTfunction: MonthlyPayment=PMT(5/12,120,300000) How do our payments change relative to changes to both the interestrateand/orpaymentperiod?
Example 3: MarketingProblem Westartabusinesswithaninitialinvestmentof$20,000. Weincur$2toproduceagood. Weneedtosetapriceforourproduct,sayp. Wealsoneedtodecideonouradvertisingbudget,sayA. GivenpandA,totaldemandforourproductis: 2000+4 −20∗p WhichpandAmaximizeourprofit?