There are two factories with a total capacity of 6,500 per months and eight customers with forecasted demand totalling 6,816. This makes the demand greater than the capacity of the factory for a given month. The transportation model is structured in the following way:

Objective – Minimise transportation cost subject to the following constraints.Constraint #1 – Total number of products sent from the two factories is ≤ factory capacityConstraint #2 – Total number of products demanded ≥ customer demand.According to Chase et al (1998, p. 305) spreadsheets are useful in solving LP problems as most spreadsheets have optimisation tools that are ‘easy to use and understand’. The model can be solved using Microsoft Excel Solver by setting up an input table with the cost to transfer each unit from the two factories to the eight customers as shown in the table below.

Table 1 shows that there are two factories – F1 and F2. R1 to R8 represents the eight customers. Each row shows the cost of transporting a unit from the respective factory to the respective customer. The transportation plan can then be found by setting up a table which includes formulas for the total transportation cost, total received and totally transported. This along with the constraints indicated above will be input to the section on the spreadsheet where the results will be located. In order to solve the transportation problem, the solver should be selected from the analysis section of the Microsoft Excel toolbar. A dialogue box entitled ‘solver parameters’ will appear. The dialog box requires the identification of the target cell (the objective function – which is to minimise the total transportation cost), the changing cells (decision variables – the cells that will contain the number of units of the product to be supplied to each of the eight customers from each of the two factories), and the constraints (factory capacity and number of products demanded by each customer) (Chase et al 1998).

The ‘Target Cell’ contains the formula for the total transportation cost. The ‘Changing Cells’ contain the results which Solver will provide so that the value in the ‘Target Cell’ can be calculated. The box which requires information on ‘Subject to the Constraints’ helps to ensure that the results are less than or equal to the factory capacity in the case of the capacity constraint, and greater than or equal to in the case of the demand constraints. .