Principles Of Supply Chain Management

Posted Under: Data Analysis

Ask A Question
DESCRIPTION
Posted
Modified
Viewed 19
For Project 4, your assignment will be in Chapters 8 and 9. For the next two weeks, you will be using the attached Excel Spreadsheet workbook to answer 6 data analysis questions. There are 6 tabs on the excel workbook, one for each of the 6 questions you will be answering. The first three tabs (1, 2, & 3) relate to Chapter 8 (Forecasting and demand planning) while tabs 4, 5, and 6 relate to calculations you will be completing regarding chapter 9 (Inventory management). The 6 calculation problems you will complete are all referenced back to an example in chapters 8 and 9. For most of the tabs, there are multiple tasks to complete, all are sequential, so complete them in order. The point values for each problem are in the instructions. Finally, there are detailed instructions for how to complete each problem in the Excel template below. Download the template, store it on your computer, then, please read the instructions carefully. Upload the completed excel file this week when you are ready for it to be graded. Use the following naming convention JSmith_Project4
Attachments
Tab 1 Forecasting with the Mean Forecasting the Mean: Handy Power Tools needs Forecast Sales of its most popular drill for the last month of the year (month 12). The company has been selling the drill for the past 15 years and sales have been steady. The company uses a simple mean to forecast weekly sales for December. Sales over the past 11 months are per the table below. Use this data to forecast the 12th month. Then for step 2, create a "Clustered Column Chart" to visually represent the trend over the entire 12 months. The instructions for both tasks are below (possible points - 15) Instructors - Overall Awarded Points by Problem Month Actual Sales 1 33 Problem 1 Problem 2 Problem 3 Problem 4 Problem 5 Problem 6 Total 2 25 Possible Points 15 15 20 20 15 15 100 3 18 Actual Points 0 0 0 0 0 0 0 4 17 5 44 6 19 7 22 8 34 9 42 10 18 11 21 12 <-- To find the Average, use =AVERAGE(B4:B15) Task 1 To calculate the forecast for the 12th month using the mean, we will be using the Excel function "=average(B4: B14). Start by putting your curser into cell B15, then go up to the fx function and search for "Average." Then highlight cells B4 through B14 and hit enter. The number returned in Cell B15 is the forecast for Month 12 using the mean of months 1 through 11. Task 2 For this task, you will be creating a "Column"chart to visually represent the data for all 12 months. Start by highlighting all the data under "Month" and under "Actual Sales" include the titles "Month" and Actual Sales." You should see A3 to A15 and 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 teams colors by clicking on the background (then the bars) and formatting each in your color preference. Tab 2 Exponential Smoothing 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 calculte the Forecast for February using a smoothing coefficient (α) 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). (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 1500 1500 1500 1500 January Actual usage 1100 1100 1100 1100 February Forecast Create an Excel Formula using the formula above as a guide Task 2 Explain why the smoothing cooefficient has so much impact on the February Forecast Explanation: Tab 3 Linear Regression Forecasting using Simple Linear Regression: For this forecasting we will use historical data for Pizza sales versus Advertizing dollar spent. Instead of hand calculating the regression equation, we will use Excel to do the work for us. 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. If not, 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 to your tool bar. 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) and Advertising (X- Value). 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 coeficients "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, input X range, the Labels box won't be checked, AND, the output rante 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 lables 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 calcuate the approximate. Then calculate the difference between the Y-sales actual and the Y-sales Approximate to calculat 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 (From table above) (from table above) (from table above) Use the formula below: Use: from from =G28 +H28*D28 =C28-E28 Reg. Anal Reg Anal 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: a b Linear Regression Formula Tab 4 Computing EOQ Computing The EOQ at Georgia's Florists: You have taken over at inventory manager at Georgia's Florists. You would like to use EOQ to computer the best quantity of orchids to order. The previous manager at Georgia's orderd orchids once a month in quantities of 1,000 to simply match the monthly demand for year 1. Orchids are expenseive to order, 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 anual 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 In the year 5 column, you have new monthly deamand 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 step3, 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 asterick "*" 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? Task 1 Task 2 Given Year 1 Year 5 Monthly Demand 1000 2000 product cost per unit $ 60.00 $ 65.00 Holding cost/unit Factor 0.15 0.16 Cost Per Order (S) $ 300.00 $ 350.00 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 =(B10*80)-(B6*1.15*B10) 1.15 to 1.16 Step 4 What Happened? Tab 5 EPQ & Imax 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 estimted to be $1.00 per wheel truck per year and set up costs for the machining center and assembly operations is $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 example. For Task 2, In 5 years the annual demand has doubled 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: Tab 6 Average Inventory Calc. One of the products your company makes is Iphone Cases that include a belt holster. Suppose that that you 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 Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted. Printed by: Ronimorgan09@gmail.com. Printing is for personal, private use only. No part of this book may be reproduced or transmitted without publisher's prior permission. Violators will be prosecuted.
Explanations and Answers 0

No answers posted

Post your Answer - free or at a fee

Login to your tutor account to post an answer

Posting a free answer earns you +20 points.

Login

NB: Post a homework question for free and get answers - free or paid homework help.

Get answers to: Principles Of Supply Chain Management or similar questions only at Tutlance.

Related Questions