这是一个新加坡的Excel决策建模作业代写

Question 1 (11 marks)

We have three factories (A, B, C) and five distributors (P, Q, R, S, T) of our products. The products
are shipped by vehicles from factories to distributors. At most 180 units of our products fit a
vehicle. The transportation cost is $1 per unit to load the vehicle and $1 per unit to unload the
vehicle plus the mileage cost. The mileage cost is based on the mileage the vehicle must travel
from the factory to the distributor and return to the same factory. This is considered as one trip.
The mileage cost of $15 per mile is independent of the number of units of products in the vehicle.
The maximum monthly productions of the factories are: A 4000 units, B 2000 units, and C 1500
units. The monthly demands by the distributors are: P 1000 units, Q 1600 units, R 1500 units, S
2000 units, and T 1400 units. The mileage table of the distances between factories and distributors
is:

Use Solver to determine the number of products to send each month from each factory to each
distributor so as to minimize the total transportation costs. Your worksheet should also show the
number of trips from each factory to each distributor and total transportation cost as well. Note
that to find the optimal solution depends heavily on starting a good initial solution, so please test
your model thoroughly.

Question 2 (12 marks)

In the country of Freedom, the national currency is the royal.

Single people pay no taxes on the first 1,000 royals, 10% on any income between 1,000 and 3,000
royals, 20% on any income between 3,000 and 10,000, and 30% on any income over 10,000.

Married people filing jointly pay no taxes on the first 1,000 royals, 8% on any income between
1,000 and 3,000 royals, 15% on any income between 3,000 and 10,000, and 25% on any income
in excess of 10,000 royals. So, for example, a single person with an income of 5,000 royals would
pay 200 royals for the income between 1,000 and 3,000 and 400 for the income between 3,000 and
5,000 for a total tax bill of 600 royals.

Create a Tax Rate table to represent the different tax rates for different income ranges, and any
base tax amount that is applicable to each income range. Create a model to calculate the taxed
owed given the marriage status and income, using lookup functions to look up from the Tax Rate
table.

The model also should show the actual percentage of income owed in taxes. In the example given,
the 600 royals owed represent 12% of income.

Create 4 test cases for different income and marital status.

Question 3 (13 marks)

The Solo Cell Phone Company offers five cell phone plans.

 Plan 1 is $9.99 fixed charge per month and each minute is $0.50 with zero free time.

 Plan 2 is $19.99 fixed charge per month with the first 100 minutes free and each minute in
excess of 100 costing $0.40.

 Plan 3 is $39.99 fixed charge per month with the first 250 minutes free and each minute
over 250 costing $0.30.

 Plan 4 is $79.99 fixed charge per month with the first 1,000 minutes free and each minute
in addition costing $0.20.

 Plan 5 is $159.99 fixed charge per month with unlimited calls at no extra cost.

Create a worksheet that allows a person to enter in a name, select the plan, and enter the number
of minutes used in a month. The worksheet should calculate the total bill for the month with the
following results:

a) Fixed charge incurred

b) Chargeable number of minutes (taking into account any free minutes)

c) Unit charge per minute applicable

d) Variable charge incurred due to chargeable number of minutes

e) Total charge incurred

Create the following test cases: John – Plan 1 7 minutes, Luis – Plan 2 80 minutes, Carla – Plan 3
120 minutes, Omar – Plan 4 1200 minutes and Suzanne – Plan 5 2400 minutes.