Do it on excel
Suppose you are making and selling canned tomato. Based on demand forecasting, in the next 6 months you will sell 60000, 80000, 110000, 90000, 95000, and 70000 boxes of cans.
Because tomato crop is quite seasonal, your production costs are $5.5, $6.0, $3.6, $3.0, $3.5, and $5.6 per unit respectively in these months. So it might be a good idea to produce more than the demand when production cost is low, and store extra units in your warehouse to sell later when production cost is high. The monthly cost to store one box of cans of tomato is $1. For example, suppose 80 thousand units are produced in month 1, then 60 thousand of them are shipped in the same month and incur no storage cost. On the other hand, 20 thousand boxes have to be stored and each of them incurs a storage cost of $1 from month 1 to month 2.
Due to the space limit of your warehouse, you cannot store more than 50,000 boxes of cans in any month.
How to plan the production in each month to minimize the total costs (sum of production cost and storage cost) incurred in satisfying the demands? Build a model and use Solver to get the answer.
Your model should be robust enough, so that when numbers (e.g., production costs and demands) are changed, Solver can still produce correct results.
