# 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.

© BrainMass Inc. brainmass.com October 24, 2018, 11:54 pm ad1c9bdddfhttps://brainmass.com/math/linear-transformation/modelling-problem-using-excel-209641

#### Solution Summary

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

Forecasting

Time Period Actual Number of Units Sold

1 33

2 36

3 32

4 35

5 33

6 36

7 34

8 38

9 37

10 36

11 38

12 38

13 37

14 39

15 35

16 38

17 37

18 39

19 37

20 35

21 37

22 34

23 35

24 36

1. Based upon using mean absolute deviation (MAD) as a measure of forecast accuracy, which of the forecast models would be the preferred forecast model (i.e., which model provides the greatest degree of forecasting accuracy)?

• 3-Month Moving Average Model

• 3-Month Weighted Moving Average Model

• Exponential Smoothing Model

• Regression Model

2. Based upon using mean squared error (MSE) as a measure of forecast accuracy, which of the forecast models would be the preferred forecast model (i.e., which model provides the greatest degree of forecasting accuracy)?

• 3-Month Moving Average Model

• 3-Month Weighted Moving Average Model

• Exponential Smoothing Model

• Regression Model

3. Based upon using mean absolute percent error (MAPE) as a measure of forecast accuracy, which of the forecast models would be the preferred forecast model (i.e., which model provides the greatest degree of forecasting accuracy)?

• 3-Month Moving Average Model

• 3-Month Weighted Moving Average Model

• Exponential Smoothing Model

• Regression Model

4. Based upon using mean absolute deviation (MAD) as a measure of forecast accuracy, which of the forecast models would be the least preferred forecast model (i.e., which model provides the greatest degree of forecasting inaccuracy)?

• 3-Month Moving Average Model

• 3-Month Weighted Moving Average Model

• Exponential Smoothing Model

• Regression Model

5. Based upon using mean squared error (MSE) as a measure of forecast accuracy, which of the forecast models would be the least preferred forecast model (i.e., which model provides the greatest degree of forecasting inaccuracy)?

• 3-Month Moving Average Model

• 3-Month Weighted Moving Average Model

• Exponential Smoothing Model

• Regression Model

6. Based upon using mean absolute percent error (MAPE) as a measure of forecast accuracy, which of the forecast models would be the least preferred forecast model (i.e., which model provides the greatest degree of forecasting inaccuracy)?

• 3-Month Moving Average Model

• 3-Month Weighted Moving Average Model

• Exponential Smoothing Model

• Regression Model

7. Based upon using the 3-Month Moving Average Model and mean absolute deviation (MAD) as a measure of forecast accuracy, what would be the interval estimate for projected demand for the test equipment for time period 25?

• 32.18 - 37.82

• 34.83 - 35.17

• 33.65 - 36.35

• 33.70 - 36.30

8. Based upon using the 3-Month Moving Average Model and mean squared error (MSE) as a measure of forecast accuracy, what would be the interval estimate for projected demand for the test equipment for time period 25?

• 34.83 - 35.17

• 33.65 - 36.35

• 33.70 - 36.30

• 32.18 - 37.82

9. Based upon using the 3-Month Moving Average Model and mean absolute percent error (MAPE) as a measure of forecast accuracy, what would be the interval estimate for projected demand for the test equipment for time period 25?

• 34.83 - 35.17

• 33.70 - 36.30

• 33.65 - 36.35

• 32.18 - 37.82