Reboot, Inc. is a manufacturer of hiking boots. Demand for
boots is highly seasonal. In particular, the demand in the next year
is expected to be 3,000, 4,000, 8,000, and 7,000 pairs of boots in
quarters 1, 2, 3, and 4, respectively. With its current production facility,
the company can produce at most 6,000 pairs of boots in any
quarter. Reboot would like to meet all the expected demand, so it
will need to carry inventory to meet demand in the later quarters.
Each pair of boots sold generates a profit of $20 per pair. Each pair
of boots in inventory at the end of a quarter incurs $8 in storage
and capital recovery costs. Reboot has 1,000 pairs of boots in inventory
at the start of quarter 1. Reboot’s top management has
given you the assignment of doing some spreadsheet modeling to
analyze what the production schedule should be for the next four
quarters and make a recommendation.
(a) Visualize where you want to finish. What numbers will top
management need? What are the decisions that need to be
made? What should the objective be?
(b) Suppose that Reboot were to produce 5,000 pairs of boots in each
of the first two quarters. Calculate by hand the ending inventory,
profit from sales, and inventory costs for quarters 1 and 2.
(c) Make a rough sketch of a spreadsheet model, with blocks laid out for the data cells, changing cells, output cells and target cells
(a) To visualize where you want to finish, top management will need to know the production schedule for the next four quarters, including the number of boots to be produced in each quarter. The decisions that need to be made include determining the production quantities for each quarter and the resulting ending inventory levels. The objective should be to maximize profit by meeting all the expected demand while minimizing inventory costs.
(b) If Reboot were to produce 5,000 pairs of boots in each of the first two quarters, the ending inventory, profit from sales, and inventory costs for quarters 1 and 2 can be calculated as follows:
Quarter 1:
- Starting inventory: 1,000 pairs
- Production: 5,000 pairs
- Demand: 3,000 pairs
- Ending inventory: Starting inventory + Production - Demand
= 1,000 + 5,000 - 3,000
= 3,000 pairs
- Profit from sales: Demand * profit per pair
= 3,000 * $20
= $60,000
- Inventory costs: Ending inventory * inventory cost per pair
= 3,000 * $8
= $24,000
Quarter 2:
- Starting inventory: Ending inventory from previous quarter
= 3,000 pairs
- Production: 5,000 pairs
- Demand: 4,000 pairs
- Ending inventory: Starting inventory + Production - Demand
= 3,000 + 5,000 - 4,000
= 4,000 pairs
- Profit from sales: Demand * profit per pair
= 4,000 * $20
= $80,000
- Inventory costs: Ending inventory * inventory cost per pair
= 4,000 * $8
= $32,000
(c) I'm sorry, but as a text-based bot, I cannot create visual sketches. However, I can describe the elements of a rough spreadsheet model for your reference:
- Data cells: Input cells where you'll enter the demand for each quarter and other relevant information such as production capacity, starting inventory, profit per pair, and inventory cost per pair.
- Changing cells: Cells where you'll input the production quantities for each quarter.
- Output cells: Cells where you'll calculate the ending inventory, profit from sales, and inventory costs for each quarter.
- Target cells: Cells where you'll determine the objective, which can be maximizing profit or minimizing inventory costs.
By setting up these cells and applying appropriate formulas, you can create a spreadsheet model to analyze the production schedule and make recommendations.
(a) To visualize where we want to finish, we need to determine the production schedule for the next four quarters, taking into account the demand and production capacity.
The numbers that top management will need are:
- Production schedule for each quarter
- Ending inventory for each quarter
- Profit from sales for each quarter
- Inventory costs for each quarter
The decisions that need to be made are the production quantities for each quarter, while considering the production capacity and demand.
The objective is to maximize the profit by determining the optimal production schedule while minimizing inventory costs.
(b) To calculate the ending inventory, profit from sales, and inventory costs for quarters 1 and 2, let's assume Reboot produces 5,000 pairs of boots in each of the first two quarters.
For Quarter 1:
- Production: 5,000 pairs
- Demand: 3,000 pairs
- Ending Inventory = Starting Inventory + Production - Demand
= 1,000 + 5,000 - 3,000
= 3,000 pairs
- Profit from sales = Demand * Profit per pair
= 3,000 * $20
= $60,000
- Inventory costs = Ending Inventory * Inventory cost per pair
= 3,000 * $8
= $24,000
For Quarter 2:
- Production: 5,000 pairs
- Demand: 4,000 pairs
- Starting Inventory: Ending Inventory from Quarter 1
= 3,000 pairs
- Ending Inventory = Starting Inventory + Production - Demand
= 3,000 + 5,000 - 4,000
= 4,000 pairs
- Profit from sales = Demand * Profit per pair
= 4,000 * $20
= $80,000
- Inventory costs = Ending Inventory * Inventory cost per pair
= 4,000 * $8
= $32,000
(c) Here is a rough sketch of a spreadsheet model:
Data cells:
- Demand for each quarter (Q1, Q2, Q3, Q4)
- Production capacity per quarter
- Profit per pair
- Inventory cost per pair
- Starting inventory in Q1
Changing cells:
- Production quantity for each quarter
Output cells:
- Ending inventory for each quarter
- Profit from sales for each quarter
- Inventory costs for each quarter
Target cell:
- Total profit (to be maximized)
(a) To visualize where we want to finish, we need to determine the production schedule for the next four quarters in order to meet the expected demand. The numbers that top management will need are:
- Production quantities for each quarter
- Ending inventory for each quarter
- Profit from sales for each quarter
- Inventory costs for each quarter
The decisions to be made include:
- How many pairs of boots to produce in each quarter
- How much inventory to carry over to meet the demand in later quarters
The objective is to maximize the overall profit, considering both sales and inventory costs. We want to find the production schedule that minimizes inventory costs while meeting the demand.
(b) To calculate the ending inventory, profit from sales, and inventory costs for quarters 1 and 2, we need to follow these steps:
1. Starting inventory for quarter 1 = 1,000 pairs
2. Production for quarter 1 = 5,000 pairs (as given)
3. Sales for quarter 1 = 3,000 pairs (expected demand)
Ending inventory for quarter 1 = Starting inventory + Production - Sales
= 1,000 + 5,000 - 3,000
= 3,000 pairs
4. Production for quarter 2 = 5,000 pairs (as given)
5. Sales for quarter 2 = 4,000 pairs (expected demand)
Ending inventory for quarter 2 = Starting inventory + Production - Sales
= 3,000 + 5,000 - 4,000
= 4,000 pairs
Now, to calculate the profit and inventory costs:
Profit from sales for quarter 1 = Sales * Profit per pair
= 3,000 * $20
= $60,000
Inventory costs for quarter 1 = Ending inventory * Inventory cost per pair
= 3,000 * $8
= $24,000
Profit from sales for quarter 2 = Sales * Profit per pair
= 4,000 * $20
= $80,000
Inventory costs for quarter 2 = Ending inventory * Inventory cost per pair
= 4,000 * $8
= $32,000
(c) Here is a rough sketch of a spreadsheet model:
```
--------------------------
| | A | B |
--------------------------
| 1 | Quarter | 1 |
--------------------------
| 2 | Demand | |
--------------------------
| 3 | Production| |
--------------------------
| 4 | Sales | |
--------------------------
| 5 | Inventory | 3 |
--------------------------
| 6 | Profit | |
--------------------------
| 7 | Cost | |
--------------------------
```
In this model, cell B1 represents the quarter number, B2 represents the demand, B3 represents the production quantity, B4 represents the sales, B5 represents the ending inventory, B6 represents the profit, and B7 represents the inventory cost.
You can fill in the numbers and formulas in cells B2 to B7 based on the given data and calculations.