Solver is a very useful tool for businesses. As we saw in Chapter 9, solver can be used to determine the optimal solutions to problems. This helps the user determine the best course of action under those particular circumstances. Solver is also a great tool to use to compare alternative courses of action. We can use Solver to determine the best course of action for a initial set of circumstances (call this a base case) and then explore the effects of changing different aspects. Solver helps answer these types of questions:
• Do the changes increase or lower profits/costs?
• Is it reasonable to invest $X to make changes?
The worksheet model and Solver become an awesome what-if tool for exploration of decisions alternatives.
Each of the problems will have a base case and one or more alternatives to explore. Create a worksheet that models the problem and use Solver to get a solution to the base case. Modify the worksheet and explore the consequences of each alternative. Use the features of Excel to easily meet the requirements detailed below.
Honest Abe Furniture manufactures tables and chairs and sells unfinished tables, unfinished chairs, finished tables, and finished chairs. A table requires 40 board ft of wood, and a chair requires 30 board ft of wood. Honest Abe’s current supplier has 70,000 board ft of wood available for purchase at $1.25 per board ft each month. It takes 2 hours of skilled labor to manufacture an unfinished table or an unfinished chair. Three more hours of skilled labor will turn an unfinished table into a finished table, and 2 more hours of skilled labor will turn an unfinished chair into a finished chair. A total of 6,000 hours of skilled labor are available each month. Honest Abe’s current labor cost is $15.75 per hour. All furniture produced can be sold. An unfinished table is sold for $110. A finished table is sold for $165. Unfinished chairs and finished chairs sell for $95 and $135 respectively.
Honest Abe is considering changing suppliers. The new supplier can supply up to 90,000 board ft of wood per month but at an increased cost. The new cost is $1.35 per board ft. To change suppliers, Honest Abe would need to spend $5,000 up front. Make a recommendation on which supplier to use.
Test the law of large numbers for N random normally distributed numbers with mean = 0 and standard deviation = 1:
Create a python script in Jupyter notebook that will count how many of these numbers fall between -1 and 1 and divide the total quantity of N. You know that E(X) =68.2%. Check whether Mean(Xn) -> E(X) as you return your script while increasing N.
You are a data scientist working for a consulting firm. One of your colleague from the Auditing department has asked you to help them assess the financial statement of organization X.
You have been supplied with two lists of data: monthly revenue and monthly expenses for the financial year in question. Your task is to calculate the following financial metrics:
- Profit for each month
- Profit after tax for each month (the tax rate is 30%)
- Profit for each month – equals to profit after tax divided by the revenue
- Good months – where the profit after tax was greater than the mean for the year
- Bad months – where the profit after tax was less than the mean for the year
- The best month – where the profit after tax was less than the mean for the year
#Data set
revenue = [14574.49, 7606.46, 8611.41, 9175.41, 8058.65, 8105.44, 11496.28, 9766.09, 10305.32, 14379.96, 10713.97, 15433.50]
expenses = [12051.82, 5695.07, 12319.20, 12089.72, 8658.57, 840.20, 3285.73, 5821.12, 6976.93, 16618.61, 10054.37, 3803.96]
Assignment 4: Due on March 09, 2022
#Dear Student,
#
#Welcome to the world of Basketball Data!
#I'm sure you will enjoy this section of the Python Programming course.
#
#Instructions for this dataset:
# Simply copy ALL the lines in this script by pressing
# CTRL+A on Windows or CMND+A on Mac and run the Jupyter cell
# Once you have executed the commands the following objects
# will be created:
# Matrices:
# - Salary
# - Games
# - MinutesPlayed
# - FieldGoals
# - FieldGoalAttempts
# - Points
# Lists:
# - Players
# - Seasons
# Dictionaries:
# - Sdict
# - Pdict
#Comments:
#Seasons are labeled based on the first year in the season
#E.g. the 2012-2013 season is preseneted as simply 2012
#Notes and Corrections to the data:
#Kevin Durant: 2006 - College Data Used
#Kevin Durant: 2005 - Proxied With 2006 Data
#Derrick Rose: 2012 - Did Not Play
#Derrick Rose: 2007 - College Data Used
#Derrick Rose: 2006 - Proxied With 2007 Data
#Derrick Rose: 2005 - Proxied With 2007 Data
#Import numpy
import numpy as np
#Seasons
Seasons = ["2005","2006","2007","2008","2009","2010","2011","2012","2013","2014"]
Sdict = {"2005":0,"2006":1,"2007":2,"2008":3,"2009":4,"2010":5,"2011":6,"2012":7,"2013":8,"2014":9}
#Players
Players = ["KobeBryant","JoeJohnson","LeBronJames","CarmeloAnthony","DwightHoward","ChrisBosh","ChrisPaul","KevinDurant","DerrickRose","DwayneWade"]
Pdict = {"KobeBryant":0,"JoeJohnson":1,"LeBronJames":2,"CarmeloAnthony":3,"DwightHoward":4,"ChrisBosh":5,"ChrisPaul":6,"KevinDurant":7,"DerrickRose":8,"DwayneWade":9}
#Salaries
KobeBryant_Salary = [15946875,17718750,19490625,21262500,23034375,24806250,25244493,27849149,30453805,23500000]
JoeJohnson_Salary = [12000000,12744189,13488377,14232567,14976754,16324500,18038573,19752645,21466718,23180790]
LeBronJames_Salary = [4621800,5828090,13041250,14410581,15779912,14500000,16022500,17545000,19067500,20644400]
CarmeloAnthony_Salary = [3713640,4694041,13041250,14410581,15779912,17149243,18518574,19450000,22407474,22458000]
DwightHoward_Salary = [4493160,4806720,6061274,13758000,15202590,16647180,18091770,19536360,20513178,21436271]
ChrisBosh_Salary = [3348000,4235220,12455000,14410581,15779912,14500000,16022500,17545000,19067500,20644400]
ChrisPaul_Salary = [3144240,3380160,3615960,4574189,13520500,14940153,16359805,17779458,18668431,20068563]
KevinDurant_Salary = [0,0,4171200,4484040,4796880,6053663,15506632,16669630,17832627,18995624]
DerrickRose_Salary = [0,0,0,4822800,5184480,5546160,6993708,16402500,17632688,18862875]
DwayneWade_Salary = [3031920,3841443,13041250,14410581,15779912,14200000,15691000,17182000,18673000,15000000]
#Matrix
Salary = np.array([KobeBryant_Salary, JoeJohnson_Salary, LeBronJames_Salary, CarmeloAnthony_Salary, DwightHoward_Salary, ChrisBosh_Salary, ChrisPaul_Salary, KevinDurant_Salary, DerrickRose_Salary, DwayneWade_Salary])
#Games
KobeBryant_G = [80,77,82,82,73,82,58,78,6,35]
JoeJohnson_G = [82,57,82,79,76,72,60,72,79,80]
LeBronJames_G = [79,78,75,81,76,79,62,76,77,69]
CarmeloAnthony_G = [80,65,77,66,69,77,55,67,77,40]
DwightHoward_G = [82,82,82,79,82,78,54,76,71,41]
ChrisBosh_G = [70,69,67,77,70,77,57,74,79,44]
ChrisPaul_G = [78,64,80,78,45,80,60,70,62,82]
KevinDurant_G = [35,35,80,74,82,78,66,81,81,27]
DerrickRose_G = [40,40,40,81,78,81,39,0,10,51]
DwayneWade_G = [75,51,51,79,77,76,49,69,54,62]
#Matrix
Games = np.array([KobeBryant_G, JoeJohnson_G, LeBronJames_G, CarmeloAnthony_G, DwightHoward_G, ChrisBosh_G, ChrisPaul_G, KevinDurant_G, DerrickRose_G, DwayneWade_G])
#Minutes Played
KobeBryant_MP = [3277,3140,3192,2960,2835,2779,2232,3013,177,1207]
JoeJohnson_MP = [3340,2359,3343,3124,2886,2554,2127,2642,2575,2791]
LeBronJames_MP = [3361,3190,3027,3054,2966,3063,2326,2877,2902,2493]
CarmeloAnthony_MP = [2941,2486,2806,2277,2634,2751,1876,2482,2982,1428]
DwightHoward_MP = [3021,3023,3088,2821,2843,2935,2070,2722,2396,1223]
ChrisBosh_MP = [2751,2658,2425,2928,2526,2795,2007,2454,2531,1556]
ChrisPaul_MP = [2808,2353,3006,3002,1712,2880,2181,2335,2171,2857]
KevinDurant_MP = [1255,1255,2768,2885,3239,3038,2546,3119,3122,913]
DerrickRose_MP = [1168,1168,1168,3000,2871,3026,1375,0,311,1530]
DwayneWade_MP = [2892,1931,1954,3048,2792,2823,1625,2391,1775,1971]
#Matrix
MinutesPlayed = np.array([KobeBryant_MP, JoeJohnson_MP, LeBronJames_MP, CarmeloAnthony_MP, DwightHoward_MP, ChrisBosh_MP, ChrisPaul_MP, KevinDurant_MP, DerrickRose_MP, DwayneWade_MP])
#Field Goals
KobeBryant_FG = [978,813,775,800,716,740,574,738,31,266]
JoeJohnson_FG = [632,536,647,620,635,514,423,445,462,446]
LeBronJames_FG = [875,772,794,789,768,758,621,765,767,624]
CarmeloAnthony_FG = [756,691,728,535,688,684,441,669,743,358]
DwightHoward_FG = [468,526,583,560,510,619,416,470,473,251]
ChrisBosh_FG = [549,543,507,615,600,524,393,485,492,343]
ChrisPaul_FG = [407,381,630,631,314,430,425,412,406,568]
KevinDurant_FG = [306,306,587,661,794,711,643,731,849,238]
DerrickRose_FG = [208,208,208,574,672,711,302,0,58,338]
DwayneWade_FG = [699,472,439,854,719,692,416,569,415,509]
#Matrix
FieldGoals = np.array([KobeBryant_FG, JoeJohnson_FG, LeBronJames_FG, CarmeloAnthony_FG, DwightHoward_FG, ChrisBosh_FG, ChrisPaul_FG, KevinDurant_FG, DerrickRose_FG, DwayneWade_FG])
#Field Goal Attempts
KobeBryant_FGA = [2173,1757,1690,1712,1569,1639,1336,1595,73,713]
JoeJohnson_FGA = [1395,1139,1497,1420,1386,1161,931,1052,1018,1025]
LeBronJames_FGA = [1823,1621,1642,1613,1528,1485,1169,1354,1353,1279]
CarmeloAnthony_FGA = [1572,1453,1481,1207,1502,1503,1025,1489,1643,806]
DwightHoward_FGA = [881,873,974,979,834,1044,726,813,800,423]
ChrisBosh_FGA = [1087,1094,1027,1263,1158,1056,807,907,953,745]
ChrisPaul_FGA = [947,871,1291,1255,637,928,890,856,870,1170]
KevinDurant_FGA = [647,647,1366,1390,1668,1538,1297,1433,1688,467]
DerrickRose_FGA = [436,436,436,1208,1373,1597,695,0,164,835]
DwayneWade_FGA = [1413,962,937,1739,1511,1384,837,1093,761,1084]
#Matrix
FieldGoalAttempts = np.array([KobeBryant_FGA, JoeJohnson_FGA, LeBronJames_FGA, CarmeloAnthony_FGA, DwightHoward_FGA, ChrisBosh_FGA, ChrisPaul_FGA, KevinDurant_FGA, DerrickRose_FGA, DwayneWade_FGA])
#Points
KobeBryant_PTS = [2832,2430,2323,2201,1970,2078,1616,2133,83,782]
JoeJohnson_PTS = [1653,1426,1779,1688,1619,1312,1129,1170,1245,1154]
LeBronJames_PTS = [2478,2132,2250,2304,2258,2111,1683,2036,2089,1743]
CarmeloAnthony_PTS = [2122,1881,1978,1504,1943,1970,1245,1920,2112,966]
DwightHoward_PTS = [1292,1443,1695,1624,1503,1784,1113,1296,1297,646]
ChrisBosh_PTS = [1572,1561,1496,1746,1678,1438,1025,1232,1281,928]
ChrisPaul_PTS = [1258,1104,1684,1781,841,1268,1189,1186,1185,1564]
KevinDurant_PTS = [903,903,1624,1871,2472,2161,1850,2280,2593,686]
DerrickRose_PTS = [597,597,597,1361,1619,2026,852,0,159,904]
DwayneWade_PTS = [2040,1397,1254,2386,2045,1941,1082,1463,1028,1331]
#Matrix
Points = np.array([KobeBryant_PTS, JoeJohnson_PTS, LeBronJames_PTS, CarmeloAnthony_PTS, DwightHoward_PTS, ChrisBosh_PTS, ChrisPaul_PTS, KevinDurant_PTS, DerrickRose_PTS, DwayneWade_PTS])
Final Assessment
Introduction to Data Science
Vel Tech University
Due Date: March 07, 2022
1. Super Office Furniture (SOF) is produces executive desks using a specially selected grade of walnut. Initially, three models – Jet, Bear and Cobra – are to be marketed. SOF expects to sell all it can make of each desk. Each Jet desk requires 1 1/4 hour for fabrication., 1 hr for assembly and 1 hr for finishing; each Bear requires 1 1/2 hr for fabrication, 1 hr for assembly , and 1 hour for finishing; each model Cobra desk requires 1 1/2 hr, 3/4 hr, and 1/2 hr for fabrication, assembly and finishing respectively. The revenue on each model Jet desk is $500, the revenue on each model Bear desk is $450, and the revenue on each model Cobra desk is $425. Each week, the total time available in the fabrication department, the assembly department, and the finishing department is 310 hr, 205 hr and 190 hr respectively. The fabrication department costs $16 per hr to run; the assembly department costs $12 per hr to run; and the finishing department costs $17 per hour to run. Materials for each Jet and Bear produced costs $300. The materials in each Cobra costs $250.
SOF has been approached by a vendor who sells a machine that will reduce the assembly time for all the desks by 30%. There is a one-time purchase and installation cost of $150,000. Make a recommendation on the purchase of the new equipment.
For the Solver problems, make sure you meet the following:
Specifications:
• Submit one workbook with a worksheet for the problem
• Develop an accurate worksheet for the problem
• Save scenarios for all possible alternatives (Hint: Be sure to include all of the cells affected by the alternatives on the worksheet – these won’t be limited to just the Solver changing cells)
• Create a report comparing all the possible alternatives
• Add a worksheet that details your recommendation
Due Date: March 08, 2022
2. You have been supplied data for two more additional in-game statistics
a) Free Throws
b) Free Throw Attempts
You need to create three plots that portray the following insights:
i. Free Throw Attempts per game
ii. Accuracy of Free Throws
iii. Player playing style (2 vs 3 points preference) excluding Free Throws
Each Free Throw is worth 1 point
The data has been supplied in the form vectors. You will have to create the two matrices before your proceed with the analysis. The matrix calculation for part iii is:
(Points – FreeThrows)/FieldGoals
Due Date: March 09, 2022
3. The world bank was very impressed with your deliverables on the previous assignment, and they have a new project for you.
You are required to produce a scatterplot depicting Life Expectancy (y-axis) and Fertility Rate (x-axis) statistics by Country.
The scatterplot needs to be categorized by Countries Regions.
You have been supplied with data for 2 years: 1960 and 2013 and you are required to produce a visualization for each of these years.
Some data has been provided in a csv file, some in python lists. The CSV file contains combined data for both years. All data manipulations must be performed in Python (not in Excel) because this project can be audited at the later stage.
You have also been requested to provide insights into how the two periods compare.
Data set for the final assessment is available at this link:
https://drive.google.com/file/d/1mxLJ9MJYMCU6kEE_unghNHOcPnw23SHX/view?usp=sharing
import android.content.Context;
import android.graphics.Canvas;
import android.graphics.Color;
import android.graphics.Paint;
import android.util.Log;
import android.view.MotionEvent;
import android.view.SurfaceHolder;
import android.view.SurfaceView;
import android.content.res.AssetFileDescriptor;
import android.content.res.AssetManager;
import android.graphics.RectF;
import android.media.AudioManager;
import android.media.SoundPool;
import java.io.IOException;
class GameEngine extends SurfaceView implements Runnable{
// This is our thread
private Thread gameThread = null;
// This is new. We need a SurfaceHolder
// When we use Paint and Canvas in a thread
// We will see it in action in the draw method soon.
private SurfaceHolder ourHolder;
// A boolean which we will set and unset
// when the game is running- or not.
private volatile boolean playing;
// Game is paused at the start
private boolean paused = true;
// A Canvas and a Paint object
private Canvas canvas;
private Paint paint;
// How wide and high is the screen?
private int screenX;
private int screenY;
// This variable tracks the game frame rate
private long fps;
// This is used to help calculate the fps
private long timeThisFrame;
// The player's bat
Bat bat;
// A ball
Ball ball;
// Up to 200 bricks
Brick[] bricks = new Brick[200];
int numBricks = 0;
// For sound FX
SoundPool soundPool;
int beep1ID = -1;
int beep2ID = -1;
int beep3ID = -1;
int loseLifeID = -1;
int explodeID = -1;
// The score
int score = 0;
// Lives
int lives = 3;
// The constructor is called when the object is first created
public GameEngine(Context context, int x, int y) {
// This calls the default constructor to setup the rest of the object
super(context);
// Initialize ourHolder and paint objects
ourHolder = getHolder();
paint = new Paint();
// Initialize screenX and screenY because x and y are local
screenX = x;
screenY = y;
// Initialize the player's bat
bat = new Bat(screenX, screenY);
// Create a ball
ball = new Ball();
// Load the sounds
// This SoundPool is deprecated but don't worry
soundPool = new SoundPool(10, AudioManager.STREAM_MUSIC,0);
try{
// Create objects of the 2 required classes
AssetManager assetManager = context.getAssets();
AssetFileDescriptor descriptor;
// Load our fx in memory ready for use
descriptor = assetManager.openFd("beep1.ogg");
beep1ID = soundPool.load(descriptor, 0);
descriptor = assetManager.openFd("beep2.ogg");
beep2ID = soundPool.load(descriptor, 0);
descriptor = assetManager.openFd("beep3.ogg");
beep3ID = soundPool.load(descriptor, 0);
descriptor = assetManager.openFd("loseLife.ogg");
loseLifeID = soundPool.load(descriptor, 0);
descriptor = assetManager.openFd("explode.ogg");
explodeID = soundPool.load(descriptor, 0);
}catch(IOException e){
// Print an error message to the console
Log.e("error", "failed to load sound files");
}
restart();
}
// Runs when the OS calls onPause on BreakoutActivity method
public void pause() {
playing = false;
try {
gameThread.join();
} catch (InterruptedException e) {
Log.e("Error:", "joining thread");
}
}
// Runs when the OS calls onResume on BreakoutActivity method
public void resume() {
playing = true;
gameThread = new Thread(this);
gameThread.start();
}
@Override
public void run() {
while (playing) {
// Capture the current time in milliseconds in startFrameTime
long startFrameTime = System.currentTimeMillis();
// Update the frame
// Update the frame
if(!paused){
update();
}
// Draw the frame
draw();
// Calculate the fps this frame
// We can then use the result to
// time animations and more.
timeThisFrame = System.currentTimeMillis() - startFrameTime;
if (timeThisFrame >= 1) {
fps = 1000 / timeThisFrame;
}
}
}
private void update(){
// Move the bat if required
bat.update(fps);
// Update the ball
ball.update(fps);
// Check for ball colliding with a brick
for(int i = 0; i < numBricks; i++){
if (bricks[i].getVisibility()){
if(RectF.intersects(bricks[i].getRect(),ball.getRect())) {
bricks[i].setInVisible();
ball.reverseYVelocity();
score = score + 10;
soundPool.play(explodeID, 1, 1, 0, 0, 1);
}
}
}
// Check for ball colliding with bat
if(RectF.intersects(bat.getRect(),ball.getRect())) {
ball.setRandomXVelocity();
ball.reverseYVelocity();
ball.clearObstacleY(bat.getRect().top - 2);
soundPool.play(beep1ID, 1, 1, 0, 0, 1);
}
// Bounce the ball back when it hits the bottom of screen
// And deduct a life
if(ball.getRect().bottom > screenY){
ball.reverseYVelocity();
ball.clearObstacleY(screenY - 2);
// Lose a life
lives --;
soundPool.play(loseLifeID, 1, 1, 0, 0, 1);
if(lives == 0){
paused = true;
restart();
}
}
// Bounce the ball back when it hits the top of screen
if(ball.getRect().top < 0){
ball.reverseYVelocity();
ball.clearObstacleY(12);
soundPool.play(beep2ID, 1, 1, 0, 0, 1);
}
// If the ball hits left wall bounce
if(ball.getRect().left < 0){
ball.reverseXVelocity();
ball.clearObstacleX(2);
soundPool.play(beep3ID, 1, 1, 0, 0, 1);
}
// If the ball hits right wall bounce
if(ball.getRect().right > screenX - 10){
ball.reverseXVelocity();
ball.clearObstacleX(screenX - 22);
soundPool.play(beep3ID, 1, 1, 0, 0, 1);
}
// Pause if cleared screen
if(score == numBricks * 10){
paused = true;
restart();
}
}
void restart(){
// Put the ball back to the start
ball.reset(screenX, screenY);
int brickWidth = screenX / 8;
int brickHeight = screenY / 10;
// Build a wall of bricks
numBricks = 0;
for(int column = 0; column < 8; column ++ ){
for(int row = 0; row < 3; row ++ ){
bricks[numBricks] = new Brick(row, column, brickWidth, brickHeight);
numBricks ++;
}
}
// Reset scores and lives
score = 0;
lives = 3;
}
private void draw(){
// Make sure our drawing surface is valid or game will crash
if (ourHolder.getSurface().isValid()) {
// Lock the canvas ready to draw
canvas = ourHolder.lockCanvas();
// Draw the background color
canvas.drawColor(Color.argb(255, 26, 128, 182));
// Draw everything to the screen
// Choose the brush color for drawing
paint.setColor(Color.argb(255, 255, 255, 255));
// Draw the bat
canvas.drawRect(bat.getRect(), paint);
// Draw the ball
canvas.drawRect(ball.getRect(), paint);
// Change the brush color for drawing
paint.setColor(Color.argb(255, 249, 129, 0));
// Draw the bricks if visible
for(int i = 0; i < numBricks; i++){
if(bricks[i].getVisibility()) {
canvas.drawRect(bricks[i].getRect(), paint);
}
}
// Draw the HUD
// Choose the brush color for drawing
paint.setColor(Color.argb(255, 255, 255, 255));
// Draw the score
paint.setTextSize(70);
canvas.drawText("Score: " + score + " Lives: " + lives, 10,80, paint);
// Show everything we have drawn
ourHolder.unlockCanvasAndPost(canvas);
}
}
// The SurfaceView class implements onTouchListener
// So we can override this method and detect screen touches.
@Override
public boolean onTouchEvent(MotionEvent motionEvent) {
// Our code here
switch (motionEvent.getAction() & MotionEvent.ACTION_MASK) {
// Player has touched the screen
case MotionEvent.ACTION_DOWN:
paused = false;
if(motionEvent.getX() > screenX / 2){
bat.setMovementState(bat.RIGHT);
}
else{
bat.setMovementState(bat.LEFT);
}
break;
// Player has removed finger from screen
case MotionEvent.ACTION_UP:
bat.setMovementState(bat.STOPPED);
break;
}
return true;
}
}
Graphical User Design for IoS
Download code available at at this link:
https://drive.google.com/file/d/1LYOiMN0yxa2tDTg6wsm_Gzpf5ATr-q7W/view?usp=sharing