SCM 421 Supply Chain Analytics
Extra Credit
This is to be done individually.
Upload your word doc and any other supporting documents into the Extra Credit upload area before the due date (see Canvas) at 11:59pm.
Your supporting documents are just that, supporting.Put all of your answers here in this word document so that we can grade your extra credit without looking elsewhere (but also upload all of those other documents/spreadsheets so that we can spot check supporting work as well as look at them if there are errors).
Upload this word doc in the area it asks you to, DO NOT ZIP the word doc
Upload your supporting documents into a zip folder in the area it asks you to.
In general, use your judgement.If the text says print out., then copy and paste an image of your spreadsheet instead.Think to yourself what would a grader need to be able to grade this accurately without looking at my supporting docs
Put all of your answers in blue text.This way well be able to tell the difference between your answers and our questions.If your text isnt in blue, we wont read it.Youll lose 20 points if you forget to do this.
Delete all questions you did not answer.This way well be able to see which ones you worked on more easily.Youll lose 20 points if you forget to do this.
Among the following questions, there is a mix of analytical spreadsheet building, Tableau, reading essays and responding, R, etc.Ive tried to make the points align with the time I think each problem should take.
Pick those problems that play on your strengths.
For instance, What is Code, and The Modeling Process dont require any Excel or model-building skills at all (essays).
As a rule of thumb, I expect students to take about half an hour to earn 1 point.So that a problem worth 8 points I expect would take 4 hours.
The total you could earn is about 294 points.However, I have some restrictions.The spirit of these restrictions is that the extra credit is there to help you if in the past you did worse on a homework or exam than you hoped.Its not there to replace one of the last analytics deliverables or the final.Thus, I may add more restrictions if I feel this spirit is being abused.The 2 restrictions for now are:
The points you receive in this assignment will be capped.The points you receive on this assignment cannot put your cumulative points earned at more than 2250 points, including every assignment until the last day of classes but not including the final.That is, in math-speak:
Let X = All Points earned excluding the final and excluding this assignment (But including the other extra credit assignments, ADs, Datacamp, Exams 1 & 2, etc.)
Let Y = The points you earn on this extra credit assignment.
Let Z = All points earned excluding the final but including this assignment.
Then:
Z = MAX(MIN(2250, X + Y), X)
FinalGrade = Z + ScoreOnFinal
You cant use this to replace one of the remaining analytics deliverables (unless you have a drop in your back pocket you havent used yet) or the remaining Data Camp assignments.If you have not yet dropped and AD, you can still drop one (and only one) of the last 2 ADs and still get credit for this assignment.However, if you have already dropped an AD (where a drop means a zero or large portions not even attempted), then you must complete the last two ADs with a good solid effort.In rough logic speak:
IF ((minADGradeOnADs1To7 = 0 AND Ads8To9AreCompleted AND DataCamps8And9AreCompleted) OR (minADGradeOnADs1To7 > 0 AND atLeastOneADOfADs8To9AreCompleted AND DataCamps8And9AreCompleted)) THEN countThisExtraCreditELSE dontCountThisExtraCreditAtAll
Contents
Part 0 What is code? (18 points)3
Part 1 Tornado charts(7 points)6
Part 2 Linear regression vs. Classification(8 points)6
Part 3 Logistic regression (15 points)7
Part 4 Copy shop (2-22)(8 points)7
Part 5 Dataware (2-27) (9 points)7
Part 6 Loans A (2-38) (8 points)8
Part 7 Function in R (8 points)8
Part 8 The modeling process (18 points)8
Part 9 Peaco (2-39) (10 points)9
Part 10 Car Loan (2-43) (5 points)10
Part 11 Bookshelves (6 points)10
Part 12 Fermi problems (16 points)11
Part 13 Pipeline data (8 points)12
Part 14 Supply chain podcasts (6 points)13
Part 15 B and B (8 points)13
Part 16 Sealpoint (5 points)13
Part 17 Data from Web (7 points)13
Part 18 R (5 points)14
Part 19 Tableau Dashboard (12 points)14
Part 20 Tableau for data scientists (12 points)14
Part 21 Tableau essential training (12 points)14
Part 22 Shortcuts (3 points)14
Part 23 Excel competition (12 points)15
Part 24 R coding learn on own (or Python) (max 60 points)15
Part 25 more modeling competitions (8 points)16
Part 0 What is code? (18 points)
Go here, and read the story on what is code http://www.bloomberg.com/graphics/2015-paul-ford-what-is-code/
(This made splashes in the long for journalism world as well as the hacker/coder community)
https://news.ycombinator.com/item?id=9698870
http://www.huffingtonpost.com/2015/07/07/bloomberg-what-is-code_n_7740986.html
Read this story on a computer (because there are interactive elements).Answer the questions below.When I say paraphrase, be brief with your wording.Give me a gist of whats going on, but not an in-depth essay.
At the very top, click on the envelope.What is the message from Josh? (cut and paste in blue font)
In the first youtube video after the text Five? Five Thousand?, at the 2 minute mark, what does the narrator say a computer is?A ____ with _____.
Who is TMitTB
How does Paul Ford describe himself as a coder?
When you hit a 9 on the keyboard, what is the code it produces? (beginning with F.) (this is the interactive widget with the picture of the keyboard)?
In section 2.2, theres a 1 in an oval (after economic infrastructure).What is the note?Copy and paste.
Whats in the oval 2?Copy and paste.
Who chanted developers!
According to University of Hawaii, what percentage of spreadsheets have errors?
Why doesnt the following code work?
PRINT *, WHY WONT IT WORK
END
Look at the paragraph that begins: Every character truly, truly.. paraphrase this in your own words.
Look at paragraph starting Thinking this way will teach you two things about computers:What are the two things?
Paraphrase what an algorithm is, and what you learned from that section.
According to stack overflow, what fraction of coders are women?
Why are coders so obsessive about languages?
Why is C important?
Summarize the email validation meeting
What happens when you try to run the Java plug in?
What famous people are in the Windows 95 training video?
What is debugging?
What is github and why is it important?
In the red box sending and email, paraphrase what the coder is doing.
Why is stackoverflow important?
In the writing live code section:
What number is tabby?
Whats the code to replace tabby with Boston terrier?
Whats the code to get rid of pugs?
Whats the code to add mutt?
Should you learn to code?Paraphrase.
Paste here your certificate of completion with photo of you in the middle.
Part 1 Tornado charts(7 points)
Go here to learn about tornado charts:
http://mba.tuck.dartmouth.edu/toolkit/
http://mba.tuck.dartmouth.edu/toolkit/help4.html#TornadoChart
http://mba.tuck.dartmouth.edu/toolkit/software/SensitivityToolkit2007.zip
Download app and make a tornado chart for some past homework assignment.Interpret.Paste your tornado chart (jpg or png or snapshot) and your description/interpretation here.(This app works only on a PC.If you have a Mac, google how to make a tornado chart on a Mac from scratch.You also need admin access.)
Part 2 Linear regression vs. Classification(8 points)
Do a stepwise regression for the titanic survival example in R.Compare the results from the tree to the results from the regression.What differences do you see and why?(Think about how to recode your Y (output) variable so as to be able to run a linear regression on it.)
Paste your answer (interpretation) here as well as the summary output of your model from R. (summary(my_model))
Part 3 Logistic regression (15 points)
Learn about how to do logistic regression in R.Compare the results of a logistic regression on the titanic survival data with the results from the classification tree.Paste your interpretation about logistic regression as well as the summary of your model here.(summary(my_model))
Part 4 Copy shop (2-22)(8 points)
You are thinking of opening a small copy shop.It costs $5000 to rent a copier for a year and it costs $0.03 per copy to operate the copier.Other fixed costs of running the store will amount to 400 per month.You plan to charge an average of $0.1 per copy, and the store will be open 365 days per year.Each copier can make 100,000 copies per year.
For one to five copiers rented and daily demand of 500, 1000, 1500, and 3000 copies per day, find annual profit.That is, find annual profit for each of these combinations of copiers rented and daily demand. Paste your portion of your spreadsheet here as an image.
If you rent three copiers, what daily demand for copies will allow you to break even?
Graph profit as a function of the number of copiers for a daily demand of 500 copies.For a daily demand 2000 copies.Interpret your graphs.Paste your graph here.
Part 5 Dataware (2-27) (9 points)
Dataware is trying to determine whether to give a $10 rebate, cut the price by $6, or have no price change on a software product.Currently, 40,000 units of the product are sold each week for $45 apiece.The variable cost of the product is $5.The most likely case appears to be that a $10 rebate will increase sales 30%, and half of all people will claim the rebate.For the price cut, the most likely case is that sales will increase 20%.
Given all other assumptions, what increase in sales from the rebate would make the rebate and price cut equally desirable?
Dataware does not really know the increase in sales that will results from a rebate or price cut.However, the company is sure that the rebate will increase sales by between 15% and 40% and that the price cut will increase sales by between 10% and 30%.Perform a sensitivity analysis that could be used to help determine Datawares best decision.Paste the analysis here that you would show to your boss, and interpret.
Part 6 Loans A (2-38) (8 points)
Suppose you are borrowing $25,000 and making monthly payments with 1% interest for a 5 year loan.Show that the monthly payments should equal $556.11.The key relationships are that for any month t:
(Ending month t balance) = (ending month t 1 balance) ((Monthly payment) (Month t interest))
(Month t interest) = (Beginning month t balance) x (monthly interest rate)
Of course, the ending balance after 60 months must equal zero.
Paste the jpg of your spreadsheet here with numbers.Also paste a jpg of the spreadsheet showing the formulae (remember how to show formulae: Ctrl + `).To do the latter, use print screen, as copying and pasting as special the formulae might crash excel.
Part 7 Function in R (8 points)
Write a function in R that performs the factorial operation.So that myFactorialFunction(6) would return 6*5*4*3*2*1 = 720, and myFactorialFunction(0) would return 1 (by convention 0! = 1.: you might have to write an IF statement to handle zero).
Paste your code here, and also paste the output showing your function being called for 6, for 0, and for 10.
Part 8 The modeling process (18 points)
This exercise is intended to allow us to learn about how expert modelers actually approach a problem.Your task is to do the following, and write several paragraphs to answer the questions.Thomas Willemain did research on how expert modelers think about problems, and how often times, the thought process is not linear (and not perfect at first).We have access to those transcripts.
The 4 problem statements he used are outlined in this paper (which you should have access to: let me know if you dont: http://pubsonline.informs.org/doi/abs/10.1287/opre.43.6.916 on page 918). (You can also try to access through libraries.psu.edu: search for the article title mentioned in the above link.) Pick one of the 4 problems. Answer the below questions.
Draw a black box model of your problem.Start to think about what would go into the guts of the black box. Think about what information you need, what the exact model might be or look like, implementation, etc.DO NOT use excel at all or a computer.Instead, use either paper, chalkboard, or whiteboard, and document your thoughts and the process via photographs with your smartphones or scanned pdfs of the paper. Paste your diagrams here.
AFTER youve tackled the model on your own, skim through the transcripts of at least 4 modelers for this one problem, and pick one.So that you will converge upon one modeler and one problem.This is equivalent to an hours worth of thinking out loud, so plan ahead the time it might take to read carefully.As you read, think about and make notes as to how your group and your adopted modeler approached the problem differently.In the word docs, TRW is the person running the session, and the modeler is denoted by a letter.
After youve done the above, write a summary of your results here, covering the following elements:
Which problem you chose, which modeler, and why.
Your black box model of the problem
What you thought should go into the black box (at the end of your process, what technique did you converge on in order to move from inputs to outputs).
Description of the modelers thoughts and thought process at the beginning of the transcript
Description of the essence of the model/technique that the modeler converged upon at the end.
Description of the paths the modeler went down that she/he did not pursue (paths that were slightly explored and then abandoned)
Description of some simplifications and assumptions the modeler made (this is an important part)
A sketch of your interpretation of the roadmap the modeler took from start to end.(Broad strokes, not sooo detailed.Use your artistic license and be creative, but also informative)
Description of:
The personality of the modeler
An interesting thing you learned
An interesting/funny piece of dialogue/monologue
Part 9 Peaco (2-39) (10 points)
You are thinking of starting Peaco, which will produce peakbabies, a product that competes with beanie babies.In year 0 (right now), you will incur costs of $4 million to build a plant.In year 1, you expect to sell 80,000 peakbabies for a unit price of $25.The price of $25 will remain unchanged through years 1 through 5.Unit sales are expected to grow by the same percentage (g) each year.During years 1 to 5, Peaco incurs two types of costs: variable costs and overhead.Each year, variable costs equal half the revenue.During year 1, overhead costs equal 40% of revenue.This percentage is assumed to drop 2% points per year.So during year 2, overhead will equal 38% of revenue, and so on.Peacos goal is to have profits for years 0 to 5 sum to 0.This will ensure that the $4 million investment in year zero is paid back by the end of year 5.What annual percentage growth rate g does Peaco require to pay back the plant cost by the end of year 5? Upload your spreadsheet.Copy and paste a screen shot of your spreadsheet here.
Part 10 Car Loan (2-43) (5 points)
The file carloanshell.xlsx contains a template for a car loan.Specifically, once values are entered in the input cells (which are now blue), you need to enter formulae in the grey cells to calculate the amount financed, the monthly payment (assuming that monthly payments stay the same throughout the term of the loan), the total interest paid, and an amortization schedule.For the latter, fill in the entire grey area with formulae, but use the IF functions so that blanks appear past the term of the loan.
Youll need to learn about the PMT() function for cell B9.(You can google this).Think of this as a tool for users.Change the formatting to be as we learned about in class using cell styles.Lock all cells so that users cant even click on the formula cells, but only input cells (make it idiot proof, and a useful tool for a loan officer).
Paste a screenshot of your excel sheet here, and also upload your model.
Part 11 Bookshelves (6 points)
Download the file Bookshelves.pdf from course reserves on Angel.
Download the file Bookshelf Costs.xlsx from ANGEL.Follow the instructions in the pdf Bookshelves to complete the spreadsheet.
When making the file, use the formatting conventions I talked about in class.
Use Excels Input, Calculation, Decision, and Output cell styles to identify cells.
Get rid of the gridlines.
When creating the chart, make one line sold black and the other a dashed black/grey line (that is, make it easy to view if printed out on a B&W printer).
On the chart, remove the axis lines and the chart border.
On the chart, add horizontal and vertical axis labels.
On the chart, on the y-axis, make it so that the labels (the numbers) do not have decimal places (so $500 instead of $500.00).
Using name labels for this particular assignment is more difficult than for others b/c the inputs are in arrays.You do not need to use cell labels for this spreadsheet.
Question 1: If the Oak Bookshelf needs 35 board feet instead of 30, what is the cost per bookshelf at the end of year 6?
Question 2: Assume now that you also want to account for nails.The Cherry bookshelf takes 30 nails while the Oak bookshelf takes 100.A nail costs $0.10 today, but will increase at 50% per year (they are rare titanium nails).Alter your spreadsheet to account for this new cost (in addition to labor and wood). Add it in such a way that it looks like you had planned it all along (you will have to insert rows and move some of the other work to do this.The cleaner and more natural it looks, the higher you will be scored).What is the cost in year 6 for the Cherry and Oak bookshelves?
Turn in your spreadsheet with the nails and the chart. Paste a screenshot of your excel sheet here.
Part 12 Fermi problems (16 points)
All the following questions ask you to use dimensional analysis or to estimate something.For each question, sketch your estimates in the space below.You do not need to turn in spreadsheets: this is similar to exam 1 where you did it by hand and showed your work.You can use dimensional analysis, or sketch a tree, or your choice, as long as you show your work.
Do NOT use google or wiki or any other outside data to estimate any of the inputs.Use your own experience and reasoning that you can justify (do not merely guess or make up numbers).For each input number describe why you estimated it as such.
If you run a trucking fleet (of tractor trailers) of 10 trucks that are all busy all week on short hauls (about 4-5 hours in 1 direction), how many tires will you go through in a year?
If the water supply at PSU were compromised, and drinking water had to be brought in to PSU via tanker, how many tankers a day would you need?
How much does Beaver Stadium weigh?(break the problem into parts: what do benches weigh, etc and then combine it all at the end).
In the 1989 Loma Prieta earthquake in California, approximately 2 million books fell off the shelves at the Stanford University library. If you were the library administrator and wanted to hire enough part-time student labor to put the books back on the shelves in order in 2 weeks, how many students would you have to hire? (You may assume that the books just fell off the shelves and got a bit mixed up but books in different aisles did NOT get shuffled together.You may need to estimate the time to put a book back on a shelf?You can literally visualize the process, or even try it out yourself with some books at home)
There have been some burglaries downtown, and residents are worried.They want a patrol car to pass every house every ten minutes.How many police cars to patrol every house in downtown State College every 10 minutes? (For this question, you are allowed to use google).
How many toothpicks do you need to lay on the bottom of an Olympic sized pool in order to cover it?
What does a bowling ball weigh, in units of M&Ms?
How many inches of road are there in Pennsylvania?
How many trees are in Pennsylvania?
Write your own Fermi Problem.Points for the spirit of the question and for the funn-ness.Good Fermi questions have answers that cannot be easily googled or verified.
Part 13 Pipeline data (8 points)
Go to this link:
https://www.phmsa.dot.gov/sites/phmsa.dot.gov/files/data_statistics/pipeline/accident_hazardous_liquid_1986_jan2002.zip
In this folder, there is a pdf Hazardous Liquid Accident PHMSA F7000-1 Rev.01-2010 Data fields.pdf which displays the form that companies fill out when a pipeline leaks.This will give you a sense of the columns.
There is also a txt data file: Hazardous Liquid Accident PHMSA F7000-1 Rev.01-2010.txt.Bring this file into Excel using techniques we have learned in this class.
What is the average amount of barrels of substance spilled per incident?(Use column UNINTENTIONAL_RELEASE_BBLS)
Make a histogram of UNINTENTIONAL_RELEASE_BBLS.Choose appropriate bin sizes.
What fraction of incidents result in an unintentional release of more than 1 barrel?
Turn in your histogram
Assume that someone notified you that there was a release, but didnt tell you how much.What would you guess the chances to be that the incident released more than 50 barrels?
What is the distribution of time elapsed between the date of the report and the date of the incident?(Turn in your histogram for this)
What fraction are reported in the first 3 weeks?
How would you describe the shape of the distribution (and what might you infer?)
Make a pivot chart that shows the number of incidents for each hour of the day.During what time block is it most likely that there is an incident?Why might you infer this?
Part 14 Supply chain podcasts (6 points)
Listen to the rest of the oil supply chain podcasts (http://www.npr.org/sections/money/2016/08/26/491342091/planet-money-buys-oil )(episodes 1-5).And also listen to the Planet Money TShirt project (http://www.npr.org/series/248799434/planet-moneys-t-shirt-project ), listening to all of the stories (a bunch of short ones).Write a few paragraph summary of what it was all about and what you learned in both podcasts.
Part 15 B and B (8 points)
Look in the B and B folder.Read the word doc and complete the shell.Upload your spreadsheet.
If every element had equal weight, what would be the scores of each company?Put a screen shot here of your model with equal weights.
Part 16 Sealpoint (5 points)
Go to the sealpoint folder.Follow the word doc to complete the shell.You can use the review sheet we used in exam 1 review as a start if youd like.Upload your spreadsheet.Here, paste your plot that shows TOC, TCC, and TOC+TCC vs. EOQ level.Interpret this.
Part 17 Data from Web (7 points)
Find some data from the web.This could be sports data, political funding data, movie data, etc.Download it, and analyze it.Show your results here, and tell me 3 interesting things that I might now have known otherwise.Dont forget to describe your datasource and why you chose it.
Part 18 R (5 points)
Learn how to do something cool in R that we didnt do in class.You can look at R blogs, or the Revolution blog, etc.Describe why you think its cool, why it cant be done in Excel, and then try it out for yourself. Paste your code here.
Part 19 Tableau Dashboard (12 points)
Do the Lynda.com (LinkedIn Learning) training in Tableau: Creating Interactive Dashboards in Tableau 10 with Nate Makdad
Go through the entire training.Take screenshots of your work as you create it.There are five modules videos, so you should have about 5 screenshots (small ones). Publish your final dashboards using Tableau public, and put the links here.Dont upload your Tableau files.
Part 20 Tableau for data scientists (12 points)
Do the Lynda.com (LinkedIn Learning) training in Tableau: Tableau 10 for Data Scientists with Matt Francis
Go through the entire training.Take screenshots of your work as you create it.There are 9 modules, so you should have about 9 screenshots (small ones). Publish your final dashboards using Tableau public, and put the links here.Dont upload your Tableau files.
Part 21 Tableau essential training (12 points)
Do the Lynda.com (LinkedIn Learning) training in Tableau: Tableau Essential Training with Curt Frye
Go through the entire training.Take screenshots of your work as you create it.There are about 14 modules, so you should have about 14 screenshots (small ones). Publish your final dashboards using Tableau public, and put the links here.Dont upload your Tableau files.
Part 22 Shortcuts (3 points)
Do the shortcut exercise again. Write down here that yes, you did do it again (this is on the honor system).
Part 23 Excel competition (12 points)
Go to Spreadsheet Guru Contest 2015 Test_rev1.xlsx.Go to the Test Result Picture tab.This is what you want to create in Put your result in this sheet tab.Use all the other data on all the other tabs to create this story/picture.
Paste a jpg (screenshot) of your final solution here: (itll have the missing values filled in.The final sheet that youre trying to make pretty.)Well know you did it because the numbers that are missing in the first tab will be visible in this tab.Youll know you have it correct because the checksum will work.That is, =SUM(B4:P11) should equal 66,498,377,875.
This is based on an actual competition held each year called the spreadsheet guru competition http://meetings2.informs.org/wordpress/analytics2015/spreadsheet-guru-competition-2/(This is just a link to information, theres nothing of substance here.) (While I dont love the name of the competition, it is an interesting assignment that tests interesting skills)
Part 24 R coding learn on own (or Python) (max 60 points)
Learn R on your own.Here are options, which you can combine:
One of the many courses on Lynda.com (LinkedIn Learning)
R Statistics Essential Training (15 points)
Code Clinic: R (10 points)
Data Wrangling in R (12 points)
Logistic Regression in R and Excel (5 points)
Social Network Analysis using R (3 points)
Learning the R Tidyverse (9 points)
Integrating Tableau and R for Data Science (5 points)
The data Science of Marketing (6 points)
https://www.edx.org/course/analytics-edge-mitx-15-071x-0 (30 points for entire course, with partial credit possible for doing part of the course)
Datacamp R: Well give you 2.5 extra credit points for every 1000 XP points you earn.You can do anything in DataCamp that we have not assigned.Some suggestions include:
Introduction to the Tidyverse (4150 XP )
Cluster Analysis in R (3800 XP)
Data Analysis in R, the data.table Way (2900 XP)
Importing data in R Part I (3600 XP)
Cleaning data in R (4750 XP)
Intermediate R Practice (4800XP = 12 points)
Writing functions in R (7250 XP = 18.1 points)
Working with Dates and Times in R (4000 XP)
Forecasting using R (4450 XP = 11.1 points) (may want to do other time series track as prerequisites, for which well also give credit)
Data visualization with ggplot2 (5250 XP = 13.1 points)
Importing & Cleaning Data Case Studies
Supervised leaning in R Regression
Intro to machine learning in R
Exploring pitch data with R
Data camp Intro to Python for Data Science:
As we said above, anything in DataCamp (including Python and SQL courses) where you show us your completion page and we give you 1 extra credit point per 400XP.
https://www.pluralsight.com/search?q=R(7 points)
Other (but see me to make sure its approved)
Take one or more of the above courses.To show us that you actually completed the tutorial, upload your R files, and copy and paste all of your code here.Also, take screen shots of the different stages of the different courses (this may result in dozens of screenshots: thats okay).Also, be sure to tell us which courses you took and the relevant points so that were not guessing what your screenshots go to. (We will not give you credit if you dont tell us this and make it easy on graders).Remember theres a MAX 60 points you can earn here.
Part 25 more modeling competitions (8 points)
Complete a competition for any past year.Compare your answers to the correct answers.Show screenshots of your model showing us that your model leads to the correct answers.Choose a section that actually asks you to build a model (as opposed to a round/section that asks you questions about Excel).
http://www.modeloff.com/past-questions/
Reviews
There are no reviews yet.