Optimizing Shirt Manufacturing for Profit: A Data-Driven Approach

QUESTION

Assume you run a shirt manufacturing company, which manufactures two products: (1) polo-style T-shirts, and (2) button-down dress shirts with collars.You must decide how many T-shirts and how many button-down shirts to make. Assume that you will sell every shirt you make. You have a limited amount of cotton and buttons. You can stock and have on hand only 13 million pounds of cotton to make the shirts and have only 110 million buttons on hand to make all your shirts. From experience, you know the return on sales ratio should be at least 20%. You think you must make at least 2 million T-shirts and at least 2 million button-down shirts. You want to be known as a balanced shirt maker, so you think that the ratio of button-downs to T-shirts should be not greater than 4:1. Thus if 9 million button-down shirts and 2 million T-shirts were produced, the ratio would be too high. You have to determine the optimal mix of shirts within the given conditions. To use Solver, you must set up a model of the problem, including the factors that can vary (the mix of T-shirts and button-down shirts) and the constraints on how much they can vary (how many of each). Your target is to maximize the company’s profit Your changing cells should look like those shown below (you can insert the value1 in each): CHANGING CELLS NUMBER OF T-SHIRTS 1 NUMBER OF BUTTON-DOWN SHIRTS 1 TAX RATE 0.28 SELLING PRICE: T-SHIRT 8 SELLING PRICE: BUTTON-DOWN SHIRT 36 VARIABLE COST TO MAKE: T-SHIRT 2.5 VARIABLE COST TO MAKE: BUTTON-DOWN SHIRT 14 COTTON USAGE (LBS): T-SHIRT 1.5 COTTON USAGE (LBS): BUTTON-DOWN SHIRT 2.5 TOTAL COTTON AVAILABLE (LBS) 13000000 BUTTONS PER T-SHIRT 3 BUTTONS PER BUTTON-DOWN SHIRT 12 TOTAL BUTTONS AVAILABLE 110000000 TAX RATE: The tax rate is 0.28 on pre-tax income, but no taxes are paid onlosses. SELLING PRICE: T-SHIRT: You sell polo-style T-shirts for $8. SELLING PRICE: BUTTON-DOWN SHIRT: You sell button-down shirts for $36. VARIABLE COST TO MAKE: T-SHIRT: It costs $2.5 to make a T-shirt. This variable cost are for machine-operator labour, cloth, buttons, and so-forth. VARIABLE COST TO MAKE: BUTTON-DOWN SHIRT: It costs $14 to make a button-down shirt. This variable cost are for machine-operator labour, cloth, buttons, and so-forth. COTTON USAGE (LBS): T-SHIRT: Each polo T-shirt uses 15 pounds of cotton fabric. COTTON USAGE (LBS): BUTTON-DOWN SHIRT: Each polo T-shirt uses 15pounds of cotton fabric. TOTAL COTTON AVAILABLE (LBS): You have only 13 million pounds of cotton on hand to be used to make all the T-shirts and button-down shirts. BUTTONS PER T-SHIRT: Each polo T-shirt has 3 buttons. BUTTONS PER BUTTON-DOWN SHIRT: Each button-down shirt has 1button on each collar tip, 8 buttons down the front, and 1 button on each cuff, for a total of 12 buttons. TOTAL BUTTONS AVAILABLE: You have 110 million buttons on hand to be used to make all your shirts Your spreadsheet should contain the calculations shown below. A discussion of calculation cells (and related business constraints) follows. CALCULATIONS  RATIO OF NET INCOME TO TOTAL REVENUE: This value is the net income after taxed divided by the total revenue. The minimum return on sales is 20% expressed as a decimal. COTTON USED: T-SHIRTS: This value is the product of the number of T-shirts and the cotton usage for this shirt. COTTON USED: BUTTON-DOWN SHIRTS: This value is the product of the number of button-down shirts and the cotton usage for this shirt. COTTON USED: TOTAL: This value is the sum of cotton used for T-shirts and button-down shirts. BUTTONS USED: T-SHIRTS: This value is the product of the number of T-shirts and the buttons used for this shirt. BUTTONS USED: BUTTON-DOWN SHIRTS: This value is the product of the number of button-down shirts and the buttons used for this shirt. BUTTONS USED: TOTAL: This value is the sum of buttons used for T-shirts and button-down shirts. RATIO OF BUTTON-DOWNS TO T-SHIRTS: This value is the number of button-down shirts divided y format optimizing SEO\by the number of T-shirts manufactured.

ANSWER

Optimizing Shirt Manufacturing for Profit: A Data-Driven Approach

Introduction

In the competitive world of apparel manufacturing, striking the right balance between different product lines can make all the difference in a company’s success. This essay delves into the optimization of shirt manufacturing for a fictional company producing two distinct products: polo-style T-shirts and button-down dress shirts. The objective is to maximize profitability while adhering to various constraints, such as resource availability, minimum production requirements, and desired product ratios.

Setting the Stage

To optimize the manufacturing process, a spreadsheet model is employed, with defined changing cells, fixed values, and calculated variables. The changing cells include the number of T-shirts and button-down shirts produced. Fixed values encompass key parameters like the tax rate, selling prices, variable costs, and resource constraints.

Calculating Profit and Revenue

The first step in this optimization process is to calculate net revenue for both T-shirts and button-down shirts. Net revenue is determined by multiplying the number of units produced by their respective selling prices. This is essential in understanding the income generated from each product line.

Additionally, variable costs associated with manufacturing each type of shirt are taken into account. These variable costs cover machine-operator labor, fabric, buttons, and other expenses. Subtracting these costs from the net revenue yields the net income for each product line.

Constraints and Resource Management

Resource constraints play a pivotal role in this optimization model. The availability of cotton and buttons sets physical limits on production. The calculations include the amount of cotton used for each shirt and the number of buttons required. It is imperative to ensure that these calculations do not exceed the total available resources.

Additionally, there are minimum production requirements. The company must produce at least 2 million T-shirts and 2 million button-down shirts. This ensures a baseline level of production to meet market demands.

Furthermore, a desired product ratio constraint is introduced. The company aims to be known as a balanced shirt manufacturer, and thus, the ratio of button-down shirts to T-shirts should not exceed 4:1. This constraint guarantees diversity in the product offerings and prevents an over-concentration in a single product line.

Taxation and After-Tax Income

To calculate the after-tax income, the model applies a predefined tax rate to the net income. In this scenario, the tax rate is set at 28%. It is important to ensure that taxes are only paid on profits and not on losses, a standard business taxation principle.

Optimizing Profit

With all the variables, constraints, and calculations in place, the next step is to optimize profit. This is where Excel’s Solver tool comes into play. The objective is to maximize after-tax income while adhering to all the constraints set in the model.

The solver iteratively varies the quantities of T-shirts and button-down shirts produced to identify the combination that yields the highest after-tax income, considering the limits imposed by resource availability, minimum production requirements, and product ratio constraints.

Interpreting the Results

Upon completion of the optimization process, the model will yield the optimal mix of T-shirts and button-down shirts to produce. This data-driven decision-making process ensures that the company not only maximizes profitability but also operates efficiently within the available resources and market conditions.

Conclusion

In the world of manufacturing, data-driven decision-making is increasingly critical for optimizing processes and maximizing profitability. The example presented in this essay demonstrates how a shirt manufacturing company can leverage spreadsheet models and optimization tools to find the right balance between product lines, taking into account various constraints and financial considerations. By using these techniques, the company can enhance its competitiveness, meet market demand, and build a reputation as a balanced shirt maker.