Linear Spreadsheets and solvers
Minikich4 
CheriBrown-ManagementScience.xlsxProblem 1
| SOLVE THE FOLLOWING NON-LINEAR PROBLEM USING SOLVER | ||||||||||
| YOU HAVE TO INSERT THE FORMULAS IN GREEN CELLS | ||||||||||
| MARKETING COSTS ARE HIGHLIGHTED IN RED IN CELLS C20 AND D20 | ||||||||||
| INSERT THE FORMULA FOR MARKETING COST IN CELL C19 AND D19 | ||||||||||
| Problem With Nonlinear Marketing Costs | ||||||||||
| Doors (D) | Windows (W) | Range Name | Cells | |||||||
| Unit Profit (Gross) | $4 | $6 | DoorsProduced | C12 | ||||||
| Hours | Hours | GrossProfitFromSales | H12 | |||||||
| Hours Used Per Unit Produced | Used | Available | HoursAvailable | G7:G9 | ||||||
| Plant 1 | 1 | 3 | <= | 8 | HoursUsed | E7:E9 | ||||
| Plant 2 | 5 | 2 | <= | 14 | HoursUsedPerUnitProduced | C7:D9 | ||||
| MarketingCost | C14:D14 | |||||||||
| TotalMarketingCost | H14 | |||||||||
| Doors | Windows | TotalProfit | H16 | |||||||
| Units Produced | 0.000 | 0.000 | Gross Profit from Sales | UnitProfit | C4:D4 | |||||
| UnitsProduced | C12:D12 | |||||||||
| Marketing Cost | Total Marketing Cost | WindowsProduced | D12 | |||||||
| D^3 | 2*W^2 | |||||||||
| Total Profit |
Problem 2
| Daily price of Disney stock and NYSE are given below | |||
| PLOT THE DATA POINTS ON A GRAPH, INSERT A TREND LINE AND FIND OUT THE EQUATION | |||
| y | x | ||
| Dependent | Independent | ||
| Quarter | Year | DIS | (NYSE) |
| Q4 | 2016 | 13.10% | 3.13% |
| Q3 | 2016 | -4.38% | 2.21% |
| Q2 | 2016 | -1.50% | 2.77% |
| Q1 | 2016 | -5.49% | 0.63% |
| Q4 | 2015 | 3.47% | 3.51% |
| Q3 | 2015 | -9.94% | -9.31% |
| Q2 | 2015 | 8.82% | -0.86% |
| Q1 | 2015 | 11.37% | 0.55% |
| Q4 | 2014 | 7.14% | 1.27% |
| Q3 | 2014 | 3.83% | -2.52% |
| Q2 | 2014 | 7.08% | 4.29% |
| Q1 | 2014 | 4.81% | 1.23% |
| Q4 | 2013 | 19.93% | 8.10% |
| Q3 | 2013 | 2.12% | 5.58% |
| Q2 | 2013 | 11.18% | 0.06% |
| Q1 | 2013 | 14.07% | 7.86% |
| Q4 | 2012 | -3.29% | 2.33% |
| Q3 | 2012 | 7.78% | 5.76% |
| Q2 | 2012 | 10.80% | -4.94% |
| Q1 | 2012 | 16.74% | 9.76% |
| Q4 | 2011 | 26.43% | 10.09% |
| Q3 | 2011 | -22.76% | -18.36% |
| Q2 | 2011 | -9.39% | -1.02% |
| Q1 | 2011 | 14.87% | 5.54% |
Problem 3
| FIND OUT THE 5 DAY MOVING AVERAGE AND COMPLETE THE SPREADSHEET | ||||||
| Plug in the formulas in green cells | ||||||
| N=5 | ||||||
| Moving Average | Forecasting | |||||
| Independent | Forecast | Error | ||||
| Quarter | Year | (NYSE) | ||||
| Q4 | 2016 | 3.13% | MAD = | |||
| Q3 | 2016 | 2.21% | ||||
| Q2 | 2016 | 2.77% | ||||
| Q1 | 2016 | 0.63% | ||||
| Q4 | 2015 | 3.51% | ||||
| Q3 | 2015 | -9.31% | ||||
| Q2 | 2015 | -0.86% | ||||
| Q1 | 2015 | 0.55% | ||||
| Q4 | 2014 | 1.27% | ||||
| Q3 | 2014 | -2.52% | ||||
| Q2 | 2014 | 4.29% | ||||
| Q1 | 2014 | 1.23% | ||||
| Q4 | 2013 | 8.10% | ||||
| Q3 | 2013 | 5.58% | ||||
| Q2 | 2013 | 0.06% | ||||
| Q1 | 2013 | 7.86% | ||||
| Q4 | 2012 | 2.33% | ||||
| Q3 | 2012 | 5.76% | ||||
| Q2 | 2012 | -4.94% | ||||
| Q1 | 2012 | 9.76% | ||||
| Q4 | 2011 | 10.09% | ||||
| Q3 | 2011 | -18.36% | ||||
| Q2 | 2011 | -1.02% | ||||
| Q1 | 2011 | 5.54% |
Problem 4
| Complete the Exponential Forecast using the formula | ||||||
| Forecast = 𝛂*(last value) + (1 - 𝛂)*Last Forecast | ||||||
| Plug in the formulas in green cells | ||||||
| Alpha (𝛂) = | 0.25 | |||||
| Quarter | Year | (NYSE) | Forecast | Forecast Error | MAD = | |
| Q4 | 2016 | 3.13% | 3.00% | |||
| Q3 | 2016 | 2.21% | ||||
| Q2 | 2016 | 2.77% | ||||
| Q1 | 2016 | 0.63% | ||||
| Q4 | 2015 | 3.51% | ||||
| Q3 | 2015 | -9.31% | ||||
| Q2 | 2015 | -0.86% | ||||
| Q1 | 2015 | 0.55% | ||||
| Q4 | 2014 | 1.27% | ||||
| Q3 | 2014 | -2.52% | ||||
| Q2 | 2014 | 4.29% | ||||
| Q1 | 2014 | 1.23% | ||||
| Q4 | 2013 | 8.10% | ||||
| Q3 | 2013 | 5.58% | ||||
| Q2 | 2013 | 0.06% | ||||
| Q1 | 2013 | 7.86% | ||||
| Q4 | 2012 | 2.33% | ||||
| Q3 | 2012 | 5.76% | ||||
| Q2 | 2012 | -4.94% | ||||
| Q1 | 2012 | 9.76% | ||||
| Q4 | 2011 | 10.09% | ||||
| Q3 | 2011 | -18.36% | ||||
| Q2 | 2011 | -1.02% | ||||
| Q1 | 2011 | 5.54% | ||||
Problem 5
| Daily price of SP and NASDAQ for 2014 are given below | |||||||||
| RUN A REGRESSION ANALYSIS, PLOT THE DATA ON A SCATTER GRAPH | |||||||||
| FIND INTERCEPT AND SLOPE TO FORECAST | |||||||||
| Plug in the formulas in green cells | |||||||||
| y | Independent | y = 𝛂 + β*x | |||||||
| Dependent Variable | variable (x) | Forecast | Forecast error | ||||||
| Quarter | Year | DIS | (NYSE) | Intercept = | 0.0319078217 | 𝛂 | |||
| Q4 | 2016 | 13.10% | 3.13% | 7.36% | -5.74% | Slope = | 1.3335857597 | β | |
| Q3 | 2016 | -4.38% | 2.21% | 6.14% | 10.52% | ||||
| Q2 | 2016 | -1.50% | 2.77% | 6.88% | 8.38% | ||||
| Q1 | 2016 | -5.49% | 0.63% | 4.03% | 9.52% | ||||
| Q4 | 2015 | 3.47% | 3.51% | 7.87% | 4.40% | ||||
| Q3 | 2015 | -9.94% | -9.31% | -9.22% | 0.72% | MAD = | |||
| Q2 | 2015 | 8.82% | -0.86% | 2.04% | -6.77% | ||||
| Q1 | 2015 | 11.37% | 0.55% | 3.93% | -7.44% | ||||
| Q4 | 2014 | 7.14% | 1.27% | 4.89% | -2.25% | ||||
| Q3 | 2014 | 3.83% | -2.52% | -0.17% | -4.00% | ||||
| Q2 | 2014 | 7.08% | 4.29% | 8.91% | 1.83% | ||||
| Q1 | 2014 | 4.81% | 1.23% | 4.83% | 0.02% | ||||
| Q4 | 2013 | 19.93% | 8.10% | 13.99% | -5.94% | ||||
| Q3 | 2013 | 2.12% | 5.58% | 10.63% | 8.51% | ||||
| Q2 | 2013 | 11.18% | 0.06% | 3.27% | -7.91% | ||||
| Q1 | 2013 | 14.07% | 7.86% | 13.67% | -0.40% | ||||
| Q4 | 2012 | -3.29% | 2.33% | 6.30% | 9.60% | ||||
| Q3 | 2012 | 7.78% | 5.76% | 10.87% | 3.08% | ||||
| Q2 | 2012 | 10.80% | -4.94% | -3.39% | -14.19% | ||||
| Q1 | 2012 | 16.74% | 9.76% | 16.21% | -0.53% | ||||
| Q4 | 2011 | 26.43% | 10.09% | 16.65% | -9.78% | ||||
| Q3 | 2011 | -22.76% | -18.36% | -21.29% | 1.46% | ||||
| Q2 | 2011 | -9.39% | -1.02% | 1.83% | 11.22% | ||||
| Q1 | 2011 | 14.87% | 5.54% | 10.57% | -4.30% |
