Download Report

Supplemental HW Guide for Assignment 12 First of all, this homework guide is not meant to replace the lesson, but to explain a few things from the lesson in extra detail. In the homework assignment we are given a table of data that looks like this: MODEL 1: The lesson has a section called "Implementation (step-‐by-‐step instructions)." We need to follow steps 1-‐6, obtaining a spreadsheet that looks like this: And the corresponding graph that looks like this: Now we follow step 7 to fill in the "diff^2" column. The resulting spreadsheet looks like this: Now we follow step 8 to calculate the sum of the squares: Our goal is to minimize the "sum of squares" function by changing the parameters a, c, and k. We will use the "solver" function in Excel (part 9 of the lesson tells you how to install it if you don't already have it). Follow the directions in parts 9 and 10 to minimize the "sum of squares." In my example, we end up with the following: So my answers to the first three questions on the homework would be: a=.08566845, c = .66737411, and "sum of squares" = 5.6215E-‐05. NOTE: The "sum of squares" is written in scientific notation: 5.6215E-‐05 = 5.6215 x 10^(-‐5) = .000056215. Keep all the decimal places! The next question says something like "The model predicts that the breath-‐alcohol reading at t=19 will be..." To solve this, we just add a new row to the table with the new "time" value and drag the formula for B(t) down to that new row: MODEL 2: The lesson doesn't tell you anything about the steps you need to go through for model 2. They are similar, but not all of them are exactly the same. We can follow steps 1-‐6 again, this time we obtain: Notice that we changed the formula for B(t) to formula 2. This time our graph looks like this: We follow step 7 again to fill in the "diff^2" column: Now we follow step 8 and calculate the sum of squares by adding up the "diff^2" column: Here's where things differ from the first model. Before doing steps 9-‐10, we need to make a good guess as to what the parameters should be. If we just use a=1, c=1, and k=1, we will get the wrong answer. This is because in order to find the correct answer, solver needs to start pretty close to it. So here's how we fix this problem. The best answers for a and c in model 1 should be pretty close to the right answers for model 2, and we know k needs to be really small. So we will start out with this guess: a = 0.08566845 c=.66737411 k=.001 Note: You'll want to pick a and c to be your answers from part 1. So after we put in our guess, our excel spreadsheet looks like this: We're not done yet! We have only made a guess. Now we need to use solver to find the minimum. To do this, follow steps 9-‐11 in the lesson.