SCMG201_1
DaWizest08
Project4ExcelTemplate.xltxExercise 1
| Forecasting the Mean: Handy Power Tools needs to find the average Sales of its most popular drill for the last 12 months. The company has been selling the drill for the past 15 years, and sales have been steady. Sales over the past 12 months are per the table below. Use months 1 through 12 to establish a monthly Average (mean) sales. Then for step 2, create a "Clustered Column Chart" to visually represent the trend in monthly sales over the entire 12 months. The instructions for both tasks are below (possible points - 15) | ||||||||||||||||||
| Instructors - Overall Awarded Points by Exercise | ||||||||||||||||||
| Month | Actual Sales | |||||||||||||||||
| 1 | 33 | Exercise 1 | Exercise 2 | Exercise 3 | Exercise 4 | Exercise 5 | Exercise 6 | Total | ||||||||||
| 2 | 26 | Possible Points | 15 | 15 | 20 | 20 | 15 | 15 | 100 | |||||||||
| 3 | 20 | Earned Points* | ERROR:#VALUE! | |||||||||||||||
| 4 | 19 | * For details refer to each tab | ||||||||||||||||
| 5 | 32 | |||||||||||||||||
| 6 | 22 | Note: You must use Excel Formulas or functions to earn any credit on all calculations in all six exercises. No credit will be awarded for "plugged" numbers. | ||||||||||||||||
| 7 | 20 | |||||||||||||||||
| 8 | 34 | |||||||||||||||||
| 9 | 39 | |||||||||||||||||
| 10 | 17 | |||||||||||||||||
| 11 | 20 | |||||||||||||||||
| 12 | 18 | |||||||||||||||||
| Average | ||||||||||||||||||
| Points Allowed | ||||||||||||||||||
| Task 1 | ||||||||||||||||||
| To calculate the average sales over the past 12 months, we will be using the Excel function "=average(B4: B15). Start by putting your cursor into cell B16, then go up to the fx function and search for "Average." Then highlight cells B4 through B15 and hit enter. The number returned in Cell B16 is the average monthly sales for that year. | ||||||||||||||||||
| Task 2 | ||||||||||||||||||
| For this task, you will create a "Column" chart to visually represent the Actual Sales for all 12 months. Start by highlighting all the data under "Actual Sales" and include the title "Actual Sales." You should see B3 and B15 highlighted. Next, go into "Insert" then "Recommended Charts" and choose the Column chart. Finally, move the chart up and to the right of the "Actual Sales" column. Change the color of the background and bars to your favorite sports team's colors by clicking on the background (then the bars) and formatting each in your color preference. |
Exercise 2
| Forecasting using Exponential Smoothing: The forecasted usage of steel consumption for Tesla Motors was 1,500 tons in January, the company had a slight downturn in orders for their all-electric cars, and the actual usage was 1,100 tons. The Sales manager wants you to calculate the Forecast for February using smoothing coefficients (α) of .7, .8, .9 and 1.0 Use the table below to calculate your forecast for February. Create the following formula in row 5 for each of the four different α levels (.7, .8, .9, & 1.0). Credit will only be allowed if formulas or Excel functions are used (possible points - 15) | |||||||
| Task 1 | |||||||
| Calculate the February Forecast using four different smoothing Coefficients | |||||||
| Smoothing Coeffiient (α) | 0.7 | 0.8 | 0.9 | 1.0 | |||
| January Forecasted Usage | 1400 | 1400 | 1400 | 1400 | |||
| January Actual usage | 1100 | 1100 | 1100 | 1100 | |||
| February Forecast | |||||||
| Create an Excel Formula using the formula in the yellow box above as a guide | |||||||
| Task 2 | |||||||
| points Allowed | |||||||
| Explanation regarding what you have learned about using different Alpha values: |
Exercise 3
| Forecasting using Simple Linear Regression: For this forecasting, we will use historical data for Pizza sales versus advertising dollars spent. Instead of hand calculating the regression equation, we will use Excel to do the work. What you will be calculating with Excel are the two coefficients in a Regression Equation (reference in your book on Page 175). "a" is the Y-intercept and b is the slope of the line. (Possible Points - 20) | |||||||||||||
| Data Table | |||||||||||||
| Month 2017 | Historical Pizza Sales in $ by Period (x$1000) | Historical Advertizing $ Spent by Period (x1000) | |||||||||||
| Y - Sales Actual | X - Advertising Actual | ||||||||||||
| 1 | 6 | 0.4 | |||||||||||
| 2 | 7 | 0.6 | |||||||||||
| 3 | 11 | 0.5 | |||||||||||
| 4 | 14 | 1.0 | |||||||||||
| 5 | 14 | 1.1 | |||||||||||
| 6 | 15 | 1.3 | |||||||||||
| 7 | 17 | 1.3 | |||||||||||
| 8 | 21 | 1.8 | |||||||||||
| 9 | 23 | 1.9 | |||||||||||
| 10 | 28 | 2.0 | |||||||||||
| 11 | 28 | 2.0 | |||||||||||
| 12 | 29 | 2.0 | |||||||||||
| Exercise Instructions | |||||||||||||
| Step 1 | To begin, you must first add the Analysis Tool pack if it has not already been installed. To check if it is installed go to "Data" and Look to the far right of the tool bar to see if "Data Analysis" is installed (note, sometimes it is grayed out in the box, but click on the last panel with an icon in it). If the Analysis Tool pack is not installed, follow these directions to add Data Analysis: Click on the "File" tab in the top row of the banner. Then, select "Options" from the drop down menu and then click on "Add-ins." Look for an Add-in titled "Analysis Tool Pack" and click on it to add it to your toolbar. Look again in Data to the far left and see if you now have "Data Analysis. | SUMMARY OUTPUT | |||||||||||
| Step 2 | You are now ready to complete a Regression analysis of pizza Sales (Y-value or dependent variable) and Advertising (X- Value or independent variable). As you will learn (or may have already learned) in Data Analytics (statistics), The X-value is the independent or predictor variable and the Y-value is the dependent or outcome variable. The standard formula for a linear equation is y = a +bx. Where a is the "Y-intercept" and b is the slope of the linear regression line. What you are going to find through the "Regression" function (using the data analysis package you just installed) are the two coefficients "a" and "b." So, let's get started by first, going into the banner heading labeled "Data" and then, second, clicking on Data Analysis. Next, scroll down and find "Regression" and open it up by clicking on "Regression. You should have opened a panel that looks exactly like what is off to the side of these instructions except there will be nothing in the input Y range or input X range, the Labels box won't be checked, AND, the output range will not be selected yet. That is the next step. | Regression Statistics | |||||||||||
| Step 3 | Next, you will need to highlight your Dependent variable (y) and your independent Variable (x), highlight only from B5 to B17 for the Y-variable and from C5 to C17 for the X-variable. Next, check the box that tells the tool pack you are including labels or titles for each column (Y-Sales and X-Advertising). Next, check the "Output Range" button and use the window to tell your tool pack where to put the analysis. You can but don't need to check the "Line Fit Plots" as shown in the window beside these instructions. Hit OK and you have now run a regression analysis. Look for the part of the analysis that looks like the window to the left side. | ||||||||||||
| Step 4 | To "fill out" the Formula for a linear equation, Y = a + bx, we need two constants, a, the y-intercept, and b, the slope of the regression line. The print out you get from your regression analysis should have a section that looks like the one printed to the side of these instructions. and what I have highlighted are the a and b values... So, when you stick these constants into the regression formula, you get Y = .9976 + 12.64X. Now, for every value of X, you can calculate Y. Remember, there is always some "error" in this regression formula due to the line being an approximation for the y value. Your last assignment is to take one x value, stick it into the formula, and show the approximation for Y. So, insert the Y-sales actual and the X-advertising Actual you want to check into the table below, insert the values for "a" and "b" you get from the regression analysis. Then use the formula you create to calculate the approximate. Then calculate the difference between the Y-sales actual and the Y-sales Approximate to calculate the error in the Linear regression formula. | ||||||||||||
| Task 1 | Calculate the Y-sales approximate using the constants you obtained from the regression analysis (use month 6 and fill in the first 4 columns, then install the correct values for "a" and "b" in the last two columns. Finally, use the formula under the "Y-sales Approximation" to calculate the value you regression equation gives you for the new Sales value. Finally, calculate the difference. | ||||||||||||
| Month | Y - Sales Actual | X - Advertising Actual | Y - Sales Approximate | Y-difference | a | b | |||||||
| 6 | 15 | 1.3 | |||||||||||
| (From table above) | (from table above) | (from table above) | Use the coefficients from | Use: | from | from | |||||||
| the Regression Analysis | =C28-E28 | Reg. Anal | Reg Anal | ||||||||||
| To calculate this number | |||||||||||||
| Task 2 | Use your text book to explain why the Y-sales Actual is different from the Y-Sales Approximation you obtained using the regression formula. Explanation: | ||||||||||||
| Points Allowed | |||||||||||||
a
b
Linear Regression Formula
Exercise 4
| Computing The EOQ at Georgia's Florists: You have taken over as inventory manager at Georgia's Florists. You would like to use EOQ to compute the best quantity of orchid bulbs to order. The previous manager at Georgia Florist ordered orchid bulbs once a month in quantities of 1,000 to simply match the monthly demand for year 1. Orchids are expensive to purchase, with a cost of $300 per order. Holding costs are 15% of product unit cost, with a product cost of $60 per unit this year. Start with Step 1 and calculate your annual demand and your holding cost per unit first. Next, calculate the EOQ for year 1 (see formula in yellow highlight). Next, calculate the number of orders per year for year 1. Finally, for year 1 calculate the Annual Holding Costs. For Step 2 in the year 5 column, you have a new monthly demand which as has doubled along with an increase in product cost per unit, holding costs, and ordering cost per order. Recompute all that you computed in year 1. For step 3, assume your company has not raised the selling price in the last 5 years from your standard $80 per Orchid plant, what what has happened to gross profit? For step 3, calculate each year's Gross Profit using (GP) = (annual Demand * unit Selling Price) - (Product Cost per Unit * 1.15 * Annual demand). Notes: (1) the 1.15 allows for holding costs per unit, (2) in your formula, the asterisk "*" is used for times as in 3*3=9, and (3) the cost per order is a fixed cost and not considered when calculating GP. Finally, in Step 4, explain what happened to the GP from year 1 to year 5 and does anything need to change? (possible points 15) | |||||
| Task 1 | Task 2 | ||||
| Given | Year 1 | Year 5 | |||
| Monthly Demand | 1000 | 2500 | |||
| product cost per unit | $ 60.00 | $ 65.00 | |||
| Holding cost/unit Factor | $ 0.15 | $ 0.16 | |||
| Cost Per Order (S) | $ 300 | $ 350 | |||
| Annual Demand (D) | |||||
| Holding cost $/unit (H) | |||||
| Compute the EOQ | |||||
| =SQRT(2*B9*B7/B10) | |||||
| Compute Orders per Year | |||||
| =B9/B12 | |||||
| Step 3 | |||||
| Compute Annual Holding Cost | |||||
| =(B12/2)*B10 | |||||
| Step 3 | |||||
| Compute Gross Profit | Be sure to change the holding cost in the formula from | ||||
| =(B9*80)-(B5*1.15*B9) | 1.15 to 1.16 | ||||
| Step 4 | |||||
| What Happened? | |||||
| Points Allowed | |||||
Exercise 5
| Economic Production Quantity (EPQ): A skateboard manufacturer uses 30,000 wheel trucks a year (2 trucks per skateboard) in year 1 (Task 1). The firm has automated machining and assembly operations that can produce wheel trucks at a rate of 360 per weekday (no Saturday or Sunday operations). The Carrying costs are estimated to be $1.00 per wheel truck per year and set-up costs for the machining center and assembly operations are $60. Considering that the plant operates 250 days per year, calculate the Optimum Order Quantity (Qopt) and the Maximum inventory (IMAX). Refer to page 204 in Sanders for EPQ and Imax examples. For Task 2, In 5 years the annual demand is expected to double while production set-up costs have increased by 10%, and Carrying costs have doubled. Calculate the new Optimum Order Quantity (Qopt) and Maximum inventory (IMAX) (Possible Points - 15) | |||
| Task 1 | Task 2 | ||
| Year 1 | In 5 Years | ||
| Demand per year (D) | 30,000 | Trucks per year | 60,000 |
| Demand per day (d) | Trucks per day | ||
| Production Days/year | 250 | Days/year | 250 |
| Production rate per day | 360 | Trucks/day | 360 |
| Set up cost per production run (S) | $ 60.00 | $/set up | $ 66.00 |
| Carrying cost ($) | $ 1.00 | $/Truck | $ 2.00 |
| Step 1 Calculate Qopt | |||
| Year 1 | In 5 yrs | ||
| Optimum Order Quantity (Qopt) = | Trucks | ||
| '=SQRT((2*B5*B9)/(B10*(1-B6/B8))) | |||
| Step 2. Calculate Imax | |||
| Year 1 | In 5 yrs | ||
| Maximum Inventory (Imax) = | Trucks | ||
| '=B14*(1-B6/B8) | |||
| Step 3. What effect does increasing and decreasing carrying cost have on Q and I? | |||
| Explanation: | |||
| Points Allowed | |||
Exercise 6
| One of the products your company makes is Iphone Cases that include a belt holster. Suppose that you have inventory to satisfy an annual demand of 3000 units (D). Your production quantity (Q) is 300 units and you want to always have on hand a safety stock (SS) of 100 Units. Calculate the Average Inventory and the average inventory turns for this item. Refer to page 270 in Sanders for instructions on Measuring Inventory Performance. (Possible Points- 15) | ||
| Annual Demand (D) | 3000 | units |
| Production/Order quantity (Q) | 300 | units |
| Safety Stock (SS) | 100 | units |
| Task 1 | ||
| Average Inventory | units | |
| Task 2 | ||
| Inventory Turnover | Turns per year | |
| Points Allowed | ||
