70.2K

Verified Solution

Link Copied!

Project Description: Excel Comp Homework 1 V1.4 Homework 1 contains three Excel Computational exercises: C to F, Concatenation, and Supply & Demand. In C to F exercise, we will use Centigrade to Fahrenheit conversion to illustrate a "least-squares" or "minimum sum of squared errors" best fit. Obviously, Fahrenheit translate directly into Centigrade through a linear transformation: F=1.8C +32, the formula for which is already in D2. In Concatenation exercise, the objective is to concatenate (string together) text fields of address data so that the results can be easily converted into mailing labels. You can use the Concatenation function or the "&" operator to obtain the needed result. Supply & Demand: Demand is defined in economics as a schedule of prices and the quantity demanded at each respective price. The higher the price, the smaller is the quantity demanded. Supply is similarly defined as a schedule of prices and the quantity supplied by firms at each respective price. The higher the price, the larger is the quantity supplied. Excess Demand the difference between quantity demanded and quantity supplied at a given price. Equilibrium occurs at that price where the quantity demanded exactly matches the quantity supplied, or excess demand = 0. Steps to Perform: I Step Points Instructions Possible 1 C to F 0 2 We type in E2"-$a$3c2+$b$3, and we get an "estimate" of F based on the values for the slope and intercept in A3:B3 3 In F2 we compute the error "-D2-E2" and in G2 we square that error with "=12^2". 4 4 2 5 6 In 12 we type "=sum(g:g)" and because there are no other values in the column, can use this shortcut and we get a sum of the values g2:932. (Obviously, this wouldn't work if there were extraneous values in column G.) We fill down D2:G2 and we set A3 to 1.8 and B3 to 32 and see that the estimated values are exactly equal to the actual F values. Our "sum of squared errors" is zero We set the A3 value to 2, and use Solver to minimize the sum of squared errors in 12. Executing Solver we find that the value of the intercept term that yields this minimum is 27. We now have a more readily usable formula: F-2C 27. This is our "best fit" while restricting the value of the slope term to 2.0. Thus, C= 30, estimated F= 87. Actual F=86 2

Answer & Explanation Solved by verified expert

See Answer