Posted: November 27th, 2015
Excel Program
Part 1 Breakeven Analysis Name this sheet Robots
You are a senior vice president of a company that manufactures kitchen appliances.
You are considering using robots to replace up to ten of your skilled workers on the factory floor.
Using a spreadsheet, analyze the costs of acquiring several robots (10) to paint and assemble some of your products versus the cost savings in labor.
How many years would it take to pay for the robots from the savings in fewer employees?
Assume that the skilled workers make $20 per hour, including benefits.
Assume the cost of a robot is $40,000.
Format
INPUT
Number of employees being replaced by robots 10
Employee hourly rate 20
Number of robots 10
Cost per robot $400,000
CONSTANT
Months/Year 12
Weeks/Year 52
Days/Year 365
Weeks/Month 4
Days/Month 30
Hours/Day 24
Minutes/Hour 60
Seconds/Minute 60
Work hours/Day 8
Work days/Week 5
CALCULATIONS
Total robot Cost = Number of robots * robot cost
Weekly payroll for employees being laid off = Number employees being replaced by robots * work days/week*work hours/day*Employee hourly rate
Total annual payroll for employees being laid off = Weekly payroll for employees being laid off *weeks/year
Breakeven in weeks: Total robot cost/weekly payroll for employees being laid off
Part 2 Breakeven Analysis Name this sheet Fruit Picking Machine
Page 382 PSE 1
You are investigating the use of an automated fruit-picking machine to reduce the labor costs on your 600 acre Valencia orange grove that yields about 240,000 pounds of fruit (roughly 80,000 oranges) per season. You currently employ a crew of 10 migrant workers to hand-pick the fruit over a three-day period and pay them $8/hour. The fruit-picking machine costs $25,000 and requires two people to operate it. It is capable of picking oranges right off the tree at the rate of one orange per 10 seconds. The machine is quite sophisticated and employs a vacuum-gripper combined with a vision system to pluck ripe fruit from a tree. Does it make economic sense to purchase the automated fruit-picking machine?
Extra credit: Up to 4 points not part of the above assignment (Due when above assignment is due)
Calculate Years, Months, Weeks, Days, Hours, Minutes, and Seconds in you Breakeven Analysis in Part 1. What is left over from years goes to months, months to weeks, weeks to days, days to hours, hours to minutes, and minutes to seconds.
Note: If I change any of the Inputs, the above will automatically recalculate years, months, weeks, etc. This extra credit activity is an individual activity
PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET A GOOD DISCOUNT
Place an order in 3 easy steps. Takes less than 5 mins.