Question No.2
Ms. Trang is working in the Planning Department. The manager has just assigned her to finish the worksheet as in the figure 1, a production plan of the First Concrete Company. The production plan is stored in the workbook named productionplan.xls
Figure 1. A production plan


The First Concrete Company has two production branches: Binh Thuan, Binh Phuoc. Based on the site of construction, the manager decided that (1) the Binh Thuan Branch will provide concrete to sites of Dong Nai, Vung Tau and (2) the Binh Phuoc Branch will serve sites of Hochiminh city and Binh Duong.
The unit cost consits of the production cost and the transportation cost (i.e. the cost of moving one m3 product from the source to the site of construction)

1. Source : Binh Thuan Branch



Destination
Production cost ($/m3)
Transportation cost ($/m3)
Unit cost ($/m3)

Binh Duong
120
6
126

Dong Nai
120
4
124

Ho Chi Minh city
120
5
125

Vung Tau
120
7
127






2. Source : Binh Phuoc Branch



Destination
Production cost ($/m3)
Transportation cost ($/m3)
Unit cost ($/m3)

Binh Duong
118
2
120

Dong Nai
118
5
123

Ho Chi Minh city
118
5
123

Vung Tau
118
10
128

Please do the following actions:
Create the workbook named productionplan.xls, then save the workbook as YourID.xls
Calculate and fill three columns: Source, Unit price, Total. .
Source : based on decision of the manager, write a formular to fill the cell with the name of branch (Binh Thuan or Binh Phuoc) (10p)
Unit price = Unit cost x 120% (5p)
Total = Quantity x Unit Price (3p)
Apply formatting techniques to create a professional-looking workbook.(5p)
Create the following table in Sheet3, then write formular to fill cells B2, B3, C2, C3 (10p)

A
B
C

1
Branch
Total production quantity ($/m3)
Total ($)

2
Binh Thuan



3
Binh Phuoc




Total



.
Revenue = sum of Total amount classified by branch
5. Do you think the manager’s decision is the best solution? Can you provide a better solution? If yes, create a new wooksheet (in the same workbook) and show your solution in that sheet. (7p)
-The end-