agsb quanti final paper 2013

7/25/2019 AGSB Quanti Final Paper 2013

1/21

ATENEO DE MANILA UNIVERSITY

GRADUATE SCHOOL OF BUSINESS

#20 Rockwell Drive, Rockwell Center, Makati City, 1200 Philippines

TUV CORPORATION, INC.:THE USE OF QUANTITATIVE TOOLS TO FORECAST AND

MEET DEMANDS, MAXIMIZE PROFIT AND FORECAST

SALES REVENUE

Submitted to:

Professor Ralph AnteQuantitative Methods for Managers (R14)

Submitted by:

Encarnacion, Kyla

Fermin, Verando

Hatanaka, Hans

Navidad, Freedom Ianfe

Palmos, Russell Joyce

Sy, Neilwin

September 28, 2013


2/21

Page 2

TUV CORPORATION, INC.:

THE USE OF QUANTITATIVE TOOLS TO FORECAST AND MEET DEMANDS, MAXIMIZE PROFIT AND

FORECAST SALES REVENUE

Encarnacion, Kyla | Fermin, Verando | Hatanaka, Hans | Navidad, Freedom Ianfe | Palmos, Russell Joyce | Sy, Neilwin

ABSTRACT

This paper shall initially discuss the existing inventory/stocking policies and

historical sales data of some of the products of TUV Corporation, Inc. (the Company).

The authors of this paper will seek to forecast demands, with respect to products

that are subject of this study, to enable it to determine whether it could meet product

demands as they come and eventually stay true to its mission of providing quality

service to its clients; the best combination of products to maximize profit; and the sales

revenue for the ensuing year.

The authors of this paper shall attempt to demonstrate the application of

quantitative tools in meeting the objectives of this study.


3/21

Page 3





LIMITATIONS OF THE STUDY

While the Company maintains an inventory of around four hundred (400)

products, due to time and data gathering constraints, this paper discusses and studies

only twenty-five (25) of such products, chosen for the seventy-five percent (75%) of the

total revenue of the Company. Moreover, the computations and solutions herein are

based on the Companys one-year historical data, starting from September of 2012 to

August of 2013, considering that the Companys fiscal year ends on the month of

August.


4/21

Page 4





INTRODUCTION: THE COMPANY

The Company was established in 2001, and since then it has been a

supplier/provider of air-conditioning parts and materials for residential, commercial and

industrial applications

MISSION, VISION & PASSION

TUV is proud of its strong c l ient-focus edand sol id bu siness partner relat ionships .

Our aim is to serve our clients, embracing their technical needs and challenges to

provid e products that exceed qual i ty standards. Attention to detail and quality of

work, paired with years of industry experience, make us the perfect choice to partner

with to provide superior products.

KEY PRODUCTS

COPPER TUBES, ELBOWS & COUPLINGS RUBBER

INSULATION TUBES


5/21

Page 5





CAPACITORS FAN MOTORS

ORDERING AND STOCKING POLICIES

The Company imports its products using Full Container Loads (FCLs) of twenty (20) to

forty (40) feet. The delivery lead time is pegged at two (2) months.

The Company maintains one (1)-month each of safety, emergency and special projects

stocks.

The effects of the Companys ordering and stocking policies are best illustrated in the

figure below:

3140

3072

2673

2057

1113

8051

420

1455

1200

930

500

365

524

512

446

343

186

135

0

500

1000

1500

2000

2500

3000

3500

CP-3034 CP-2524 CP-3004 FM-0501 FM-0138 FM-0354

Stock

Existing ROP

ROP


6/21

Page 6





HISTORICAL SALES DATA

Based on the last fiscal year, the Company was able to generate the following

sales, which sales represent the historical sales data on demands:

COPPER TUBES, ELBOWS AND COUPLINGS

RUBBER INSULATION TUBES

Stock Code CE-12SR CE-34SR CE-38LR CE-38SR CE-58LR CE-58SR

01-Sep-12 1155 656 526 400 438 457

01-Oct-12 798 835 526 652 438 1673

01-Nov-12 1112 888 526 639 438 1260

01-Dec-12 835 938 526 361 438 115101-Jan-13 694 1481 526 923 438 1450

01-Feb-13 1007 834 96 723 73 902

01-Mar-13 901 368 52 1402 86 1061

01-Apr-13 1185 1380 211 1431 389 1853

01-May-13 1165 1072 294 1241 386 1111

01-Jun-13 630 945 189 453 156 1228

01-Jul-13 585 981 236 1813 161 2875

01-Aug-13 545 861 259 378 342 1018

Stock Code RI-1212 RI-1238 RI-1412 RI-1438 RI-3412 RI-3434 RI-3812 RI-3834 RI-3838 RI-5812 RI-5834 RI-5838

01-Sep-12 909 248 1104 833 568 246 1138 448 292 980 886 354

01-Oct-12 1442 372 1593 1061 915 386 1540 590 368 1067 722 461

01-Nov-12 1050 355 1490 941 324 353 1410 342 370 1146 422 421

01-Dec-12 1177 908 1634 1345 796 166 1056 354 795 1074 437 311

01-Jan-13 1227 636 2039 1151 839 515 1746 673 781 1434 741 430

01-Feb-13 729 276 1091 793 425 322 1067 387 490 896 670 290

01-Mar-13 977 644 1292 1142 861 239 1106 1340 770 1160 1239 436

01-Apr-13 2090 1187 2374 3003 1217 465 2363 518 1493 2120 767 644

01-May-13 2214 721 2832 1928 710 472 2138 482 853 2008 842 554

01-Jun-13 3466 712 3418 2108 1027 799 2401 154 1133 2585 724 622

01-Jul-13 1838 769 2626 2084 993 737 2434 322 1003 1698 714 77901-Aug-13 1929 423 1701 1761 1251 741 2092 566 927 2021 951 370


7/21

Page 7





FAN MOTORS

CAPACITORS

Stock Code FM-0138 FM-0354 FM-0501

01-Sep-12 66 47 14201-Oct-12 116 73 91

01-Nov-12 72 62 194

01-Dec-12 26 45 98

01-Jan-13 121 70 128

01-Feb-13 48 60 138

01-Mar-13 69 31 115

01-Apr-13 152 86 370

01-May-13 165 66 294

01-Jun-13 71 85 18101-Jul-13 124 78 181

01-Aug-13 83 102 125

Stock Code CP-1524 CP-2524 CP-3004 CP-3034

01-Sep-12 282 215 407 306

01-Oct-12 125 151 143 147

01-Nov-12 167 157 85 152

01-Dec-12 92 174 104 213

01-Jan-13 218 100 123 195

01-Feb-13 166 169 72 164

01-Mar-13 144 219 258 152

01-Apr-13 516 706 260 420

01-May-13 916 384 542 591

01-Jun-13 206 46 183 364

01-Jul-13 225 612 211 299

01-Aug-13 257 139 285 137


8/21

Page 8





THE CHALLENGES

Given the historical sales data above, the authors would like to:

1. Forecast demands for the next ensuing year;

2. Determine the stocking policy that could best meet the forecasted

demands;

3. Determine the best combination of products to achieve maximum profit;

and

4. Forecast sales revenue for the next ensuing fiscal year.

To meet these challenges, appropriate quantitative tools such as Monte Carlo

Simulation, Inventory Management, Case Modelling and Linear Programming shall be

used.

Meeting these challenges is not only essential for the economic survival of the

Company, but also for the Company to stay true to its Mission, Vision and Passion of

providing quality services to its clients and embracing their technical needs.

FORECASTING AND MEETING SALES DEMANDS

Based on the historical sales data provided above and using the Montecarlo

Simulation tool, sales demand on the next ensuing year is forecasted as follows:


9/21

Page 9





COPPER TUBES, ELBOWS AND COUPLINGS

RUBBER INSULATORS

CAPACITORS


10/21

Page 10





FAN MOTORS

Given the forecasted demands and the delivery lead time of two (2) months,

theres a need to simulate stocking and ordering policies that could meet the forecasted

demands.


11/21

Page 11






12/21

Page 12






13/21

Page 13






14/21

Page 14






15/21

Page 15






16/21

Page 16






17/21

Page 17





Based on the simulated results and monthly costs computed above, we need to

determine the costs of lost sales in the event that theres a demand, but the Company

wouldnt be able to serve the same as the product -in-demand is not in its inventory.

By adding the stocks and the end of the monthand the new deliverieswe get the

total number of stocks after each delivery. These stocks after deliveryare equal to the

stocks after sales. A negative stocks after sales means loss in sales. Costs of lost sales

is computed by multiplying the loss in saleswith the contribution margin.

Now, comparing the existing Total Annual Cost (TAC) versus the TAC for the

forecasted demands, and considering the Costs of Lost Sales, would give us the

following results:


18/21

Page 18





CONCLUSION:

While savings in the amount of Three Hundred Fifty-Nine Thousand Seven

Hundred Seventy-Five Pesos and Seventy-Seven Centavos (Php359,775.77) will be

incurred from the Simulated Inventory (following the results of Montecarlo Simulation),

the Company is advised to maintain its current inventory practices because of the costs

of lost sales that it will have to incur if it modifies said practices pursuant to the

forecasted demands. Moreover, lost sales is reflective of a Company deviating from its

actual Mission, Vision and Passion which is to provide quality service to its customers

and address their technological needs.


19/21

Page 19





MAXIMIZING PROFIT

Given the data below, what combination of products should TUV sell in order to

achieve maximum profit. Minimum demand data is calculated from breakeven point

while maximum demand is the maximum stock on hand. It is also assumed that TUVs

maximum capital is limited to 12 Million pesos per year.

Product Selling Price Unit Cost Profit Min Demand/ Month

MaxDemand /

Month

RI-1412 39.29 27.5 11.79 773 2642

RI-3812 46.43 32.5 13.93 592 2391

RI-1212 52.68 36.875 15.80 471 2156

RI-5812 58.04 40.625 17.41 380 1920

RI-1438 22.32 15.625 6.70 1058 2054RI-3412 65.18 45.625 19.55 178 1006

RI-3838 28.57 20 8.57 366 908

RI-5834 102.68 71.875 30.80 115 1025RI-1238 32.14 22.5 9.64 308 861

RI-3834 89.29 62.5 26.79 92 716RI-5838 36.61 25.625 10.98 199 635

RI-3434 111.61 78.125 33.48 66 641

CE-58SR 26.79 18.75 8.04 827 1927

CE-34SR 40.18 28.125 12.05 402 1405

CE-12SR 19.64 13.75 5.89 1337 2285CE-38SR 14.29 10 4.29 1106 1374

CE-58LR 49.11 34.375 14.73 134 574CE-38LR 26.79 18.75 8.04 268 625

CP-3034 205.36 143.75 61.61 24 429

CP-2524 178.57 125 53.57 25 393

CP-1524 151.79 106.25 45.54 37 482

CP-3004 187.50 131.25 56.25 22 353FM-0501 1116.07 781.25 334.82 3 277

FM-0138 1651.79 1156.25 495.54 1 138

FM-0354 2053.57 1437.5 616.07 1 119


20/21

Page 20





STEP 1: OBJECTIVE

Maximize Profit

STEP 2: DECISION TO BE MADE

Combination of products that yield maximum profit

STEP 3: CONSTRAINING FACTOR AFFECTING DECISION

Minimum demand per product

Maximum product availability

Capital of 12Million pesos per year

STEP 4: DECISION VARIABLES

Let:

X1= RI-1412 X6= RI-3412 X11= RI-5838 X16= CE-38SR X21= CP-1524X2= RI-3812 X7= RI-3838 X12= RI-3434 X17= CE-58LR X22= CP-3004

X3= RI-1212 X8= RI-5834 X13= CE-58SR X18= CE-38LR X23= FM-0501X4= RI-5812 X9= RI-1238 X14= CE-34SR X19= CP-3034 X24= FM-0138

X5= RI-1438 X10= RI-3834 X15= CE-12SR X20= CP-2524 X25= FM-0354

STEP 5: CONSTRAINTS USING DECISION VARIABLES

1.

773 < X1< 2642 402 < X14< 1405

592 < X2 < 2391 1337 < X15< 2285471 < X3< 2156 1106 < X16< 1374

380 < X4< 1920 134 < X17< 5741058 < X5< 2054 268 < X18< 625

178 < X6< 1006 24 < X19< 429

366 < X7< 908 25 < X20< 393

115 < X8


21/21

Page 21





2.

27.5X1+ 32.5X2+ 36.9X3.. 1437.5X25< 1.2M

3.

X1+ X2+ X3.. X25> 0 (Non-Negativity)

STEP 6: OBJECTIVE FUNCTION

Max Profit = 11.79X1+ 13.93X2+ 616.07X25

CONCLUSION:Using solver, TUV should sell the quantities given on the table below to maximize

profit given an investment capital of 12Million pesos.

Solver Solution on Product Mix Variables

X1= 854 X6= 299 X11= 281 X16= 1132 X21= 361

X2= 677 X7= 417 X12= 273 X17= 241 X22= 353

X3= 568 X8= 311 X13= 883 X18= 324 X23= 277

X4= 498 X9= 373 X14= 475 X19= 429 X24= 138

X5= 1098 X10= 250 X15= 1378 X20= 338 X25= 119

agsb quanti final paper 2013

Documents