Explore BrainMass

Modelling Problem Using Excel

A company that makes bikes wants to maximize profit over the next five months.

Materials for each bike costs $600.

Both humans and machines are needed to produce each bike.

Each human can work on up to 100 bikes per month.

Each machine can work up to 200 bikes per month.

TetraCon, Inc. has 4 humans and no machines.

Each human costs $6000 a month. It costs $2500 to hire a human, $1000 to fire a human. Hiring's and firings may take place at the beginning of each month.

Machines must be bought either at the beginning of January or March and costs $15000 each. Machines can be sold at the beginning of April for $12000, but at least one Machine must be owned at the end of May.

The demand for bikes in each of the next five months is as follows:
January: 600
February: 500
March: 400
April: 300
May: 600

At the end of each month TetraCon, Inc. incurs an inventory cost of $50 per bike.

Each bike is sold for $790.

Ignore the value of time.

Solution Summary

The solution is attached as a Word report as well as an Excel spreadsheet.