Hire Experts For Answers

Order Now#### Related Study Services

- Homework Answers
- Coursework writing help
- Term paper writing help
- Writing Help
- Paper Writing Help
- Research paper help
- Thesis Help
- Dissertation Help
- Case study writing service
- Capstone Project Writing Help
- Lab report Writing
- Take my online class
- Take my online exam
- Do my test for me
- Do my homework
- Do my math homework
- Online Assignment Help
- Do my assignment
- Essay Writing Help
- Write my college essay
- Write my essay for me

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

**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

- C++ Homework Need Done Now***************************************
- I Need The Out As Student Information In Tkinter
- Small Refactor Of Legacy Python Code
- Code In P5.Js Please. Reply If You Can Help
- Code In P5.Js For Three Questions.
- Beginner Python Programming Assignment
- Python Code For Homework Help
- Using Functions And Formulas In Excel In Order To Answer Questions From Dataset
- Write My One Page Doctoral Assignment
- Predictive Maintenance With Machine Learning Techniques To Reduce Cost
- Nle Exam Help - Nltk, Hmm, Corpus, Python, No Programming
- Python Code Help Please - Flowchart And Code
- Create A Password Checker For A Class Project. I Need Help.
- Write A Function In R Programing With The Requirement In The Attached File
- Python Tv Series Info Generator
- Very Simple Python Code - Panda Package - Won't Take You More Than 1H
- Weeks 6-8 -Gui Programming Assignment
- I Have A Graduate Data Science Exam To Complete In Python
- My Python Assignment Data Mining
- Python Programming - Create A Solar System
- Hangman Programming Assignment (Python)
- Building A Cnn Binary Image Classifier To Differentiate Animal Face From No Animal Face.
- Fractals, I Will Attach Some Photos Of The Questions
- Business Pricing Excel Test Sample Date & Questions
- C++ Backtracking Program- Word Search
- Simulate Zombie Apocalypse On Python
- C++ Wordsearch- Backtracking Algorithm
- Log Parsing And Counting Using Python And Jupyter Notebook
- Write A Java Program And Server
- Solve The Problem Using Python
- Help On Python Assignment (Basic)
- Design And Create A Gui Interface That Allows You To Input Information For Courses And Students Taking Each Course.
- Cplusplus Programming Coursework
- I Have Python Programing Homework
- How To Solve This Class Method Problem In Python
- Final Year Project For My School.
- Solve 4 Problems In Ocaml Using Functional Programing
- Data Analytical Final Project Presentation
- Financial Analysis Project For Etf Betas
- C Memory Allocation Assignment
- Life And Health Insurance Exam
- C++ Read In Using Input .Txt File Name And Print Map With Extra Details, Navigation Menu For Each Detail
- Data Science Assignment In Rmarkdown.
- Cybersecurity Help Siem Input
- Creating Frontend Webpage Using A Template
- Creating A Training Matrix For A Division Of Company
- Could More Free Standing Emergency Rooms In The Hotspot Areas Reduce Fatalities From Shot Wounds Or Violence Related?
- I Need A Easy Script/ Macro For Work
- Python Programming With Ordinary Differential Equations
- Special Topics In Geographical Information Systems