
Constraints	 	 
Component	Manufacturing Time (seconds per unit)	Forecasted demand	Manufacturing time 
Base	1.5	2500 Financial Manager calculators 	300 hours
Financial cartridge	3	2000 Technician calculators	 
Technician cartridge	2.5	 	 
Financial top	1	 	 
Technician top	1.2	 	 
 	 	 	 
Cost information for the objective	 
Component	Manufacture Cost per Unit	Purchase Price per Unit	 
Base	0.4	0.5	 
Financial cartridge	3.85	4.5	 
Technician cartridge	3.2	4.3	 
Financial top	0.65	0.7	 
Technician top	0.72	0.85	 


Follow the Make-or-Buy example, update information for constraints and the objective in Excel, and Run Solver to solve this optimization problem.

How many units of financial tops must be manufactured by the Janders Company? Round the answer to the nearest integer.

We can set up an optimization problem using Excel’s Solver to determine how many financial tops Janders Company must manufacture or buy to meet the demand while minimizing costs.

Let's break this problem down into clear steps:

### 1. **Defining Decision Variables**:
   - Let’s denote the number of units of each component to be manufactured or purchased as variables:
     - $ x_1 $ = Number of **financial tops** to be manufactured.
     - $ x_2 $ = Number of **financial tops** to be purchased.

### 2. **Objective Function** (Minimize total cost):
   The objective is to minimize the total cost. The total cost is the sum of manufacturing and purchasing costs for each component.
   
   The total cost for financial tops is:
   \[
   \text{Total cost} = 0.65 \times x_1 + 0.7 \times x_2
   \]
   (since manufacturing cost per unit is $0.65$ and purchase price per unit is $0.70$).

### 3. **Constraints**:
   The constraints are based on the demand and the total time available for manufacturing.
   
   - **Demand constraint**: The total number of financial tops (both manufactured and purchased) should meet the forecasted demand:
   \[
   x_1 + x_2 = 2000
   \]
   
   - **Manufacturing time constraint**: The total manufacturing time cannot exceed the available time for production (300 hours = 300 × 3600 = 1,080,000 seconds). Each financial top takes 1 second to manufacture, so:
   \[
   1 \times x_1 \leq \text{available time}
   \]
   
   The available manufacturing time is shared with other components (base, cartridges, and technician tops). We need to ensure the total time for all components fits within the available 1,080,000 seconds. From the problem:

   - Base: 1.5 seconds per unit × 2500 units = 3750 seconds.
   - Financial cartridge: 3 seconds per unit × 2000 units = 6000 seconds.
   - Technician cartridge: 2.5 seconds per unit × 2000 units = 5000 seconds.
   - Technician tops: 1.2 seconds per unit × 2000 units = 2400 seconds.

   Total time used by other components = 3750 + 6000 + 5000 + 2400 = 17150 seconds.

   This means the remaining time available for manufacturing the financial tops is:
   \[
   \text{available time for financial tops} = 1,080,000 - 17,150 = 1,062,850 \text{ seconds}.
   \]
   
   Therefore, the manufacturing time constraint is:
   \[
   1 \times x_1 \leq 1,062,850
   \]

### 4. **Setting Up Excel Solver**:
   In Excel, the problem can be set up as follows:
   
   - Column A: Components.
   - Column B: Manufacturing Cost per Unit.
   - Column C: Purchase Cost per Unit.
   - Column D: Number of Units Manufactured (decision variable).
   - Column E: Number of Units Purchased (decision variable).
   - Column F: Total Cost for Manufactured = $ \text{Manufacturing Cost per Unit} \times \text{Number of Units Manufactured} $.
   - Column G: Total Cost for Purchased = $ \text{Purchase Cost per Unit} \times \text{Number of Units Purchased} $.
   - Column H: Total Cost = $ \text{Total Cost for Manufactured} + \text{Total Cost for Purchased} $.
   
   Use Solver to:
   - **Minimize**: Total cost in column H.
   - **By changing**: The decision variables (D and E columns for number of units manufactured or purchased).
   - **Subject to constraints**:
     - $ D + E = 2000 $ for financial tops.
     - $ D \leq 1,062,850 $ for the manufacturing time.
     
### 5. **Solver Output**:
After running the Solver in Excel with the constraints set, the solution will indicate the optimal number of financial tops that must be manufactured. The result is typically based on cost minimization and available manufacturing time.

The number of **financial tops to be manufactured** can be directly derived from the output. Let's proceed with running this in Excel.

---

Would you like assistance setting this up in Excel or need further clarification on any part of this?

Constraints
Component	Manufacturing Time (seconds per unit)	Forecasted demand	Manufacturing time 
Base	1.5	2500 Financial Manager calculators 	300 hours
Financial cartridge	3	2000 Technician calculators
Technician cartridge	2.5
Financial top	1
Technician top	1.2

Cost information for the objective
Component	Manufacture Cost per Unit	Purchase Price per Unit
Base	0.4	0.5
Financial cartridge	3.85	4.5
Technician cartridge	3.2	4.3
Financial top	0.65	0.7
Technician top	0.72	0.85

Follow the Make-or-Buy example, update information for constraints and the objective in Excel, and Run Solver to solve this optimization problem.
How many units of financial tops must be manufactured by the Janders Company? Round the answer to the nearest integer.

Solve an optimization problem involving manufacturing and purchasing financial tops for Janders Company. Learn how to minimize total costs using Excel Solver, taking into account manufacturing constraints and forecasted demand. This solution includes linear programming concepts, cost formulas, and detailed calculations.

Math Problem Statement

Solution

Ask a new question for Free

By Image

Math Problem Analysis

Mathematical Concepts

Formulas

Theorems

Suitable Grade Level

Related Recommendation