Understanding Excel Functions for Mortgages and Political Campaign Visualization

1. CQI: Excel recap +Absolute addressesColbert bumpElection Visualization due. EXCEL help due. Homework: prepare to VOTE Postings PROPOSALS. Presentations

2. Recap on mortgages • Mortgage: pay (fixed) amount for whole term of mortgage. • Some goes towards interest • Some goes towards paying off the debt • The Excel pmt, ipmt, ppmt, cumprinc provide information • Look at posted example, different times, and the happy and unhappy story

3. Formulas • pmt computes fixed payment, same throughout the time period of loan • ppmt and ipmt computes amount towards principal and amount of interes • cumprinc computes cumulative amount paid towards principal • Use Excel function list or google help

4. Homework problems • This was an exercise in exploring Excel. • The future value of an investment such as retirement savings (FV) • Answer(s) ??? • Purchasing decision: pay a lot now or stretch out payments (PV) • Answer(s) ???

5. Relative Addressing • default – what we have done: • When a formula is copied from one column to another • =C3*C18 from column C to column D, it becomes =D3*D18 • =B2-C5 from column C to column D, it becomes =C2-D5 • Similarly for rows • Recall that this is what we wanted in cookies, diet & exercise, mortgages

6. Absolute Addressing • When you want addresses to stay the same, use $ before letter or number or both • = $C$2*C15 copied from column C to column D becomes=$C$2*D15 • This has a role to play and you will see it. • I have not used it because I have demonstrated ????

7. Naming • Names of cells or ranges are absolute. • Used names in • Midterm for ranges for frequency • Diet and exercise • Mortgage

8. But …. • If the spreadsheet has values for which you want one dimension to be copied relatively and one not, naming will not work • =A$2*A5 if copied from A3 to B3 will become=B$2*B5 and if copied to B4 will become=B$2*B6

9. Recall cookie lab • Change to columns corresponding to days • Each day, eat different cookies • Calculate costs, calories • At start of each day, have calorie goal and for each cookie type, compute proportion of that day’s calories. • NEED: reference to row with calorie goals to be absolute, but can make column reference relative.

10. Advice • IF AND WHEN you need this type of thing, just remember that naming and absolute addressing are possibilities and work it out! • Do check after copy and paste operations. • VERY GENERAL ADVICE: many computer tools have default settings. Relative addressing is the default. Need to check that that is what you want.

11. Colbert Bump • Several years ago, Stephen Colbert claimed that being on a show gave candidates (and others) a distinct bump. • How to test this?

12. Colbert bump study by James Fowler • http://jhfowler.ucsd.edu/colbert_bump.pdf

13. Presidential Elections • What is (was) a swing state / battleground state? • History: shows roles of different states: http://www.nytimes.com/interactive/2012/10/15/us/politics/swing-history.html

14. GOTV • Campaigns divided into 3 phases: • ID • Persuasion • Get out the vote • Getting ‘your’ voters to the polls more critical than persuasion. This is not a good thing. • Campaigns focus on getting out people they have previously identified as voting for their candidate • “The 1s”

15. Previous election • Note: I emailed them and they are working on similar map for 2012. This is a big effort. • Stanford Spatial map of precincts • http://faculty.purchase.edu/jeanine.meyer/precincts2008.html

16. Visualizations: 2012 election results http://2012electoralcollegecalculator.com/blog/u-s-presidential-election-result-2012-11-09/

17. More maps • http://www.dailykos.com/story/2012/07/10/1106266/-Electoral-College-Map-Porn-Cartogram-Peep-Show

18. Comments on visualizations? • Which were appealing / informative / ? • Did the fact that NY probably will NOT be a swing state in 2016 make you less interested in voting?

19. Electoral college • What is the electoral college? • To put it another way, how do we elect the president?

20. Answer • Each state has a number of electoral college votes equal to the number of senators (2) plus the number of representatives. • So it is roughly proportional to population, but not quite. Small states get somewhat more. • Washington, DC has 3 votes • All but 2 states have winner-take-all. • Maine and Nebraska have some winner take all and some by Congressional District. • 538 votes

21. What do you think? • Is the electoral college a good thing? What is the alternative? • Is it the reason that most of the action is only in the swing states? • “Candidates running to be president of Ohio.” Not quite true.

22. NYC mayor’s race • Recent article looking back at primary: issue of definition and distributionhttp://www.nytimes.com/2013/10/29/opinion/edsall-when-class-trumps-identity.html?hp&rref=opinion&_r=0 • Let’s find latest poll on next week’s race.

23. My prediction • Registered voter (public information) versus actual voter may be issue, but… • The polls will not be faulted if DiBlasio wins, even if by a reduced margin. • What do you think?

24. Why vote? • Participate in the democratic process • Practice / get started for lifetime participation • Refute claim that young people are apathetic • People have died for the right to vote!

25. Remember • … to vote the whole ballot • NYS Propositions • Casinos • Land use • Retirement age for judges • … • Judges • In NYC: mayor race, other racesIn Westchester: county executive, legislator(s) and local races

26. Casinos • New casinos, in addition to those on reservations and at race tracks • Let’s look at posting on support for casinos. • Pro? • Con?

27. Why? • Do I / we spend time on elections?

28. Reasons • Lots of mathematics • Lots of quantitative reasoning • Lots of visualizations • Way to promote civic engagement • It is a topic I am interested in… • [Not original with me, but I can’t find the source] Don’t limit yourself to things (e.g., courses) that you are interested in. Do things to make yourself more interesting.

29. Visualizations • http://ilovecharts.tumblr.com/ • http://www.theatlantic.com/business/archive/2012/04/how-we-pay-taxes-11-charts/255954/ • Diagrams that changed the world: http://www.brainpickings.org/index.php/2012/12/21/100-diagrams-that-changed-the-world/

30. More • Collection: http://chartsbin.com/graph?sortby=chart_modified_date

31. Sports • Old World Series championships: http://www.statcrunch.com/5.0/viewreport.php?reportid=7108 • World Cup: http://www.tableausoftware.com/public/gallery/history-world-cup

32. Visualizations, cont. • Recommendation: look up work of Edmund Tufte • Need to work. • Books, posters, talks

33. Space shuttle Challenger • Background: • 0-rings used for rocket booster • Political pressure to make equipment around the country • Pressure to have flight with teacher aboard • critical decision made to go ahead with flight even though weather was predicted to be cold • Tufte claims that the presentation was (prime) cause of wrong decision being made. • For more discussion: see http://www.onlineethics.org/cms/12709.aspx

34. Diagram shown

35. Diagram shown

36. Note • Information on temperature versus tile damage was known but the so-called crayons in a box showed flights in chronological order not sorted by damage or temperature or … • Certain engineers did argue against the flight but decision made to go ahead.

37. Substitute diagram

38. Presentations • Get your proposal in • You cannot present (earn zero points) if no proposal • At presentation, bring in “1-pager” • Abstract 100 to 200 words. Formal English describing presentation • Works cited: proper format (this means more than just web address: author, organization, title, date viewed) • Most important diagram, chart or picture

39. Successful presentation • Be prepared • Be interested in topic • If you aren’t, we won’t be • Keep audience in mind and • … get audience involved • make us work….

40. Quantitative aspects • Definitions • What’s the difference? • Context, comparisons, categories • (As appropriate): denominators, distribution, data source, dimension • Do make comparisons. • Do consider counter-arguments.

41. Homework • Proposals • Presentations • Postings • Vote • Postings • Election results including turnout