Homework #6
Study Guide 4
By: Abby Almonte
November 8, 2011
IE 327/L- Systems Engineering
Industrial Engineering Department
California State Polytechnic University, Pomona
Table of Contents
1.) Question 1: Cobb-Douglass Production Function……………………………………………………………………………………………………2
- Historical Data…………………………………………………………………………………………………………………………………………………2
- Natural Logarithm……………………………………………………………………………………………………………………………….…………..2
- Regression Analysis………………………………………………………………………………………………………………………………….………3
- Extract Coefficients………………………………………………………………………………………………………………………………….………4
2.) Question 2: Linear Programming………………………………………………………………………………………………….………………………..6
3.) Question 3: Regression Analysis…………………………………………………………………………………………………..…………………………8
1- Use the handout on web page on using Excel to find Cobb-Douglas Production Function CDPF. Find CDPF for a function with four variables X1 to X4. Consider the following historical data. Try to come up with only one isoquant line (Perform trial and error, it may take some time). You can download the Excel file in relation to CDPF and make changes to it.
1.) Historical Data: Data on output (Z) and input (X1, X2, X3,X4)
Z / X1 / X2 / X3 / X46 / 80 / 210 / 400 / 15
12 / 210 / 200 / 455 / 38
5 / 50 / 230 / 431 / 12
8 / 180 / 210 / 499 / 25
7 / 150 / 290 / 350 / 20
11 / 190 / 220 / 455 / 40
11 / 198 / 280 / 390 / 34
7 / 171 / 270 / 330 / 17
5 / 108 / 210 / 460 / 20
8 / 154 / 220 / 420 / 23
2.) Natural Logarithm of Historical Data
Ln (Z) / Ln (X1) / Ln (X2) / Ln (X3) / Ln (X4)1.791759 / 4.382027 / 5.347108 / 5.991465 / 2.70805
2.484907 / 5.347108 / 5.298317 / 6.120297 / 3.637586
1.609438 / 3.912023 / 5.438079 / 6.066108 / 2.484907
2.079442 / 5.192957 / 5.347108 / 6.212606 / 3.218876
1.94591 / 5.010635 / 5.669881 / 5.857933 / 2.995732
2.397895 / 5.247024 / 5.393628 / 6.120297 / 3.688879
2.397895 / 5.288267 / 5.63479 / 5.966147 / 3.526361
1.94591 / 5.141664 / 5.598422 / 5.799093 / 2.833213
1.609438 / 4.682131 / 5.347108 / 6.131226 / 2.995732
2.079442 / 5.036953 / 5.393628 / 6.040255 / 3.135494
3.) Regression Analysis
Regression StatisticsMultiple R / 0.939760582
R Square / 0.883149952
Adjusted R Square / 0.789669913
Standard Error / 0.145589863
Observations / 10
ANOVA
df / SS / MS / F / Significance F
Regression / 4 / 0.801009808 / 0.200252452 / 9.447470996 / 0.014972339
Residual / 5 / 0.105982041 / 0.021196408
Total / 9 / 0.90699185
Coefficients / Standard Error / t Stat / P-value / Lower 95% / Upper 95% / Lower 95.0% / Upper 95.0%
Intercept / 5.7550 / 7.8449 / 0.7336 / 0.4961 / -14.4110 / 25.9210 / -14.4110 / 25.9210
X Variable 1 / -0.0176 / 0.2372 / -0.0742 / 0.9437 / -0.6274 / 0.5922 / -0.6274 / 0.5922
X Variable 2 / -0.2638 / 0.6555 / -0.4025 / 0.7039 / -1.9488 / 1.4211 / -1.9488 / 1.4211
X Variable 3 / -0.7874 / 0.7939 / -0.9918 / 0.3668 / -2.8281 / 1.2533 / -2.8281 / 1.2533
X Variable 4 / 0.8171 / 0.2952 / 2.7675 / 0.0395 / 0.0581 / 1.5761 / 0.0581 / 1.5761
4.) Extract coefficients from regression analysis
In a0 = / 5.755 / > a0 = / 315.7655475a1 = -0.018
a2 = -0.264
a3 = -0.787
a4 = 0.817
Z = 315.766 * X1-0.018 * X2-0.264 * X3-0.787 * X40.817
If Z = 400
X1 / X2 / X3 / X41 / 7 / 9 / 1.8 / 5
2 / 10 / 2 / 4 / 6.68
3 / 4 / 3.715 / 4 / 8
4 / 8 / 6 / 1.21 / 3
5 / 5 / 5 / 5 / 10.97
2- We discussed a model in relation to LP in Study Guide 4. Write a summary of this problem in the form of a diagram. Diagram should consists of a rectangle that represents the problem and its model, one arrow towards the rectangle that represents “input information” for that model, and another arrow from the rectangle that represents “output information”. Under each arrow make a list of all input information used in that model and output information obtained from that model. Inside the rectangle write a sentence as what the problem is and copy/paste the model that represents that problem. For example for LP, copy the mathematical formulation. List the controllable and uncontrollable factors.
1
Input Information:
- Coefficient of O.F. Limitations
- Usage of raw materials/labor
in each toy (not considered in
this particular analysis)
Resources / X1 / X2 / Total AvailableLabor (hour) / 6 / 2 / 36
Parts / 5 / 5 / 40
Packing Material / 2 / 4 / 28
Profit / $5 / $3
Output Information:
- Max Z
- Number Xi
- If any resources are left over
- Has multi optimal solutions
1
Controllable Factors:
- Production Process
- Amount of hours workers work
- Schedule of workers
- Training for workers
Uncontrollable Factors:
- Demand
- Price of raw material
- Union price of workers (minimum wage of workers)
- Suppliers price
3- Explain why and how we use regression analysis as a tool to find production function.
Regression analysis is a technique used to analyzerelationship of two or more variables. It is used widely for forecasting and predicting, which means being able to make a rough estimate of the future through the use of constants and other variables. We use regression analysis to help us predict an isoquant line so that there is flexibility in decision making. An isoquant line is an unusual shaped line that shows all of the possible outcomes for the same value of Z. An example of the flexibility would be that the supplier that sells X1 products are going out of business, so they sell their products at half of the price. By referring to the isoquant line, the company can pick a point where more of X1 is used, and not a lot of X2 products are used.
To find product function using regression analysis, we can use Excel. First make a table that shows all of the Z and X values. Then, transform the values into natural logarithm or log10. It does not matter which one is used, as long as the same concept is used throughout the whole process. After finding Ln or Log10, we can perform a regression analysis. For Excel 2010, I had to insert the add-in in order to complete the function. I click on “File”, then “Options”, which are both circled below in purple in Figure 6.1.
Figure 6.1
After clicking on “Options”, a new window opens up.
Figure 6.2
After the window opens up, click on “Add-Ins” and click on “Go” beside the Manage drop down menu.
Figure 6.3
An Add-Ins screen will then pop up. Click on “Analysis ToolPak”, and then press “OK”. By doing this, I am adding an add-in that will allow me to do regression analysis.
Figure 6.4
I now have access to use analysis tools. It is shown under “Data” and in the analysis box, and is named “Data Analysis”, circled below in red.
Figure 6.5
After clicking on data analysis, a box pops up. Scroll down to find regression, highlight regressing by clicking on it, and press “OK”.
Figure 6.6
Input the X and Y values by highlighting the values in the natural logarithm. Leave everything else as is, and press okay.
Figure 6.7
After completing these steps, the summary output opens up in a new tab in the same excel file. After it opens up, you extract the coefficients from the X variables, and use the coefficients as the constants in the Z equation:
Z = a0 *X1a1 *X2a2 *X3a3 * X4a4
After you have the equation, you can predict the value of z using the x variables, or you can predict one of the x variables using the X and z variables.
1