CHƯƠNG 1 ỨNG DỤNG MS EXCEL 2010 TRONG PHÂN TÍCH KINH DOANH
1.7.3. Bài toán minh họa
Xí nghiệp Rạng Đông sản xuất áo len xuất khẩu từ 2 loại chỉ. Định mức nguyên liệu cho từng loại áo như sau áo len loại 1 cần 1,7 cuộn len trắng và 2,1 cuộn len đỏ, áo len loại 2 cần 2,4 cuộn len trắng và 1,8 cuộn len đỏ; áo len loại 3 cần 1,6 cuộn len trắng và 2,4 cuộn len đỏ.
Hiện tại Công ty có 700 cuộn len trắng và 800 cuộn len đỏ.
Lợi nhuận lần lượt cho 3 loại sản phẩm trên là áo len loại 1 là 2.000, áo len loại 2 là 4.000 và áo len loại 3 là 3000.
Được biết toàn bộ sản phẩm sản xuất đều xuất khẩu hết và yêu cầu số sản phẩm tối thiểu của mỗi loại phải là 100 áo.
Hãy lập kế hoạch sản xuất tối ưu cho xí nghiệp Rạng Đông.
Hướng dẫn:
1. Xác định hàm mục tiêu và điều kiện ràng buộc của bài toán Gọi x1, x2 và x3 lần lượt là áo len loại 1, áo len loại 2 và áo len loại 3 Điều kiện ràng buộc của bài toán:
1,7x1+ 2,4x2+1,6x3<=700 2,1x1+1,8x2+2,4x3<=800
x1>=100; x2>=100; x3>=100 (x1, x2, x3>0) Hàm mục tiêu:
Lợi nhuận F=2000x1+4000x2+3000x3.
Tìm x1, x2 và x3 để F cực đại
2. Tổ chức dữ liệu trên Excel và chạy solver Thực hiện từng bước như hình sau:
Kết quả sau khi chạy
C. TÀI LIỆU HỌC TẬP
[1]. Bài giảng Tin học nâng cao, bậc đại học - chương trình chất lượng cao, 2013.
[2]. Bài giảng, bài tập của giảng viên.
[3]. Moffat Stephen, Excel 2010 Intoduction Part II, Stephen Moffat & Ventus Publishing ApS, 2011. ISBN 978-87-7681-805-0
[4]. Moffat Stephen, Excel 2010 Advaned, The Mouse Training Company & Ventus Publishing, 2011. ISBN 978-87-7681-788-6
[5]. http://www.gcflearnfree.org/excel2007 [6]. http://www.gcflearnfree.org/excel2010 [7]. http://www.gcflearnfree.org/excel2013
D. CÂU HỎI, BÀI TẬP, NỘI DUNG ÔN TẬP, BÀI TẬP TÌNH HUỐNG VÀ THẢO LUẬN
CÂU HỎI ÔN TẬP VÀ THẢO LUẬN
1. Để xác định số tiền nhận được trong tương lai với lãi suất không cố định, sử dụng hàm nào? Cách dùng hàm như thế nào?
2. Để phân tích hiệu quả của dự án dùng chỉ số nào? Ứng dụng các hàm trong Excel?
3. Có thể sử dụng kết quả hàm NPV của MS Excel làm chỉ số NPV của dự án hay không?
Tại sao?
4. Để tìm giao điểm đường cung và đường cầu sử dụng công cụ nào? Cho ví dụ minh họa.
5. Để phân tích các tình huống trong kinh doanh sử dụng công cụ nào? ví dụ minh họa.
6. Để tìm phương án tối ưu sử dụng công cụ nào? Các bước thực hiện?
7. Để tổng hợp và phân tích dữ liệu sử dụng những chức năng nào? Hãy phân biệt các chức năng đó?
8. Vay ngân hàng một khoản tiền với lãi suất 12%/năm, mỗi năm trả lãi 2 lần. Hãy cho biết lãi suất thực của khoản tiền vay này?
9. Lãi suất tiền gửi tiết kiệm kỳ hạn 12 tháng (lãnh lãi cuối kỳ) tại 1 ngân hàng thương mại là 12%/năm.
10. Theo bạn ngân hàng sẽ yết lãi suất tiền gửi tiết kiệm kỳ hạn 12 tháng lãnh lãi hàng quý là bao nhiêu phần trăm/năm? Vì sao?
11. Trình bày các bước giải bài toán tối ưu?
12. Trình bày các công cụ sử dụng trong bài toán phân tích rủi ro? Ý nghĩa từng công cụ?
13. Để phân tích các tình huống trong kinh doanh, sử dụng công cụ nào?
BÀI TẬP TÌNH HUỐNG BÀI TẬP 1:
Thống kê về thu nhập của quốc gia (Y), sản lượng điện (X1), sản lượng than (X2), sản lượng lương thực (X3), sản lượng thép (X4). Hãy ước lượng các tham số hồi quy mẫu biết rằng thu nhập của quốc gia Y và X có quan hệ tuyến tính.
Dự báo Y với X1=5,2; X2= 65,1; X3= 275,3 và X4=37,8.
Y X1 X2 X3 X4
733.300 3.089 76.200 283.500 12.844
750.900 3.503 79.400 274.500 19.835
747.600 3.817 77.000 268.000 21.797
727.600 3.870 74.000 265.700 24.759
694.400 3.706 64.400 259.600 28.093
702.600 3.851 63.100 256.800 31.121
714.000 4.170 66.300 259.300 32.759
717.630 4.378 62.900 263.400 34.556
750.000 5.000 66.700 273.100 36.788
BÀI TẬP 2:
Công ty Rạng Đông cho rằng các dịch vụ kỹ nghệ của công ty cung ứng cho các công ty xây dựng có quan hệ trực tiếp đến số hợp đồng xây dựng trong vùng theo mô hình tuyến tính. Hãy xây dựng một mô hình hồi quy do dự báo mức độ nhu cầu về dịch vụ của công ty. Sử dụng phương trình hồi quy để dự báo mức độ nhu cầu trong 4 quý tới.
Ước tính trị giá hợp đồng quý tới là 260, 290, 300 và 270 (đvt: 10 triệu đồng) Năm Quý Nhu cầu của công ty Trị giá hợp đồng thực hiện
1 1 8 150
2 10 170
4 15 190
4 9 170
2 1 12 180
2 13 190
3 12 200
4 16 220
BÀI TẬP 3:
Bà Ngọc Minh mua ghế matxa toàn thân hiệu Max – 614 của cửa hàng Mạnh Nguyễn.
Chính sách bán hàng trả góp của cửa hàng như sau: khách hàng thanh toán trước 20%
trị giá sản phẩm, phần còn lại góp đều trong 12 tháng, mỗi tháng 2.000.000 đ. Lãi suất cố định hàng tháng là 0,9%.
Bà Ngọc Minh đồng ý mua hàng nhưng đề nghị được thanh toán một lần duy nhất vào thời điểm cuối hợp đồng (cuối tháng thứ 12). Hỏi khách hàng phải thanh toán bao nhiêu?
BÀI TẬP 4:
Cửa hàng Hùng Vương bán xe theo phương thức trả góp, tiền vốn và lãi sẽ trả đều từng tháng với một số tiền nhất định. Ông Minh Tân mua xe PCX với giá 54 triệu vnđ, trả trước 10%, phần còn lại trả đều trong 24 tháng với lãi suất mỗi thàng là 1.02%.
Hãy xác định số tiền ông phải góp hàng tháng? Lập bảng chi tiết tiền trả vốn, trả lãi hàng tháng.
BÀI TẬP 5:
Cửa hàng Mạnh Nguyễn chuyên kinh doanh các loại ghế matxa với hai hình thức trả một lần hoặc trả góp. Chính sách bán hàng trả góp ghế matxa model Max-614b như sau:
ngay khi mua hàng khách hàng trả 5.000.000 đồng, số còn lại góp dần đều nhau trong 12 tháng, mỗi tháng trả 3.000.000 đồng. Lần trả tiền góp đầu tiên là 2 tháng sau ngày nhận hàng. Lãi suất tiền vay là 1%/tháng.
Hỏi giá trị thật của ghế matxa model Max-614b là bao nhiêu?
BÀI TẬP 6:
Anh Ba đang cố gắng để dành tiền tiết kiệm với mong muốn sau 3 năm sẽ có được 124 triệu đồng để xây nhà. Nếu anh Ba gửi tiết kiệm với lãi suất 6%/năm kỳ hạn 1 tháng thì bình quân mỗi tháng anh Ba phải gửi vào bao nhiêu tiền? Biết rằng trước khi gửi tiền, anh Ba đã dành dụm được số tiền là 35 triệu đồng trong tài khoản ngân hàng với lãi suất cũng là 6%/năm
BÀI TẬP 7:
Công ty XNK lương thực An Giang có nhu cầu trang bị máy đánh bóng gạo.
Hiện tại có hai công ty đến chào hàng như sau:
- Công ty Cơ Khí Phước Lập chào loại máy CBLC được thiết kế theo phương pháp dùng dao và lưới kết hợp với luồng nước phun sương làm cho gạo sạch, trắng và bóng có trị
giá 100 triệu, thời gian sử dụng là 2 năm (giá trị thanh lý không đáng kể) và mỗi năm mang lại một khoản thu nhập ròng 70 triệu.
- Công ty Cơ Khí Bình Minh chào loại máy có đặc điểm tương tự máy của Cơ Khí Phước Lập trị giá là 150 triệu, thời gian sử dụng là 4 năm và giá trị thanh lý không cần tính tới. Nếu công ty sử dụng máy này thì mỗi năm mang lại cho công ty một khoản thu nhập ròng là 55 triệu.
Chi phí sử dụng vốn của công ty là 10%.
Hãy dùng tiêu chuẩn NPV để lựa chọn nên mua máy của hãng nào?
BÀI TẬP 8:
Thương gia John mua một khách sạn nhỏ tại Đà Lạt với giá 70.000$. Thu nhập đạt được trong thời gian 5 năm đầu lần lượt là: 12.000; 15.000; 18.000; 21.000, 26.000. Cuối năm thứ 5, ông John bán lại khách sạn này thu được 20.000$.
Hỏi hiện giá thuần của khoản đầu tư là bao nhiêu? ( Lãi suất chiết khấu là 11%).
BÀI TẬP 9:
Đầu tháng 4/2013 nhà máy 27Z chuyên sản xuất đồ gỗ xuất khẩu mua một máy bào với giá 2.400$. Sau 10 năm sử dụng bán lại được 300$.
Tính khấu hao cho các năm từ thứ nhất đến thứ 10 của chiếc máy bào đó và cho biết tỷ lệ khấu hao cố định?
BÀI TẬP 10:
Một thiết bị sản xuất có nguyên giá 240.000.000 đồng, thời gian khấu hao là 5 năm, giá trị sa thải khi thanh lý là 20.000.000 đồng. Thiết bị đưa vào sản xuất đầu tháng 7.
Hãy tính số tiền khấu hao hàng năm theo phương pháp số dư giảm dần?
BÀI TẬP 11:
Một TSCĐ có nguyên giá là 2.400.000.000 đồng, giá trị sa thải khi thanh lý là 200.000.000
Hãy dùng hàm VDB tính số tiền khấu hao hàng năm, tính số tiền còn phải khấu hao sau khi đã khấu hao năm thứ 1, thứ 2, 3, 4 và tính tổng số tiền khấu hao từ năm thứ 1 đến năm thứ 3?
BÀI TẬP 12:
Công ty sản xuất gạch Đồng Tân ước tính chi phí biến đổi để sản xuất 1 tấn gạch là 2.700.000 đồng, định phí hàng tháng là 650 triệu đồng.
Giá bán trên thị trường dao động từ 3 triệu đến 3,6 triệu.
Mức sản xuất trong công ty dao động từ 1100 đến 1500 tấn gạch/ tháng.
Hãy tính mức lợi nhuận trước thuế của công ty trong 3 trường hợp sau:
- Trường hợp 1: giá bán 3,2 triệu – tiêu thụ 1.100 tấn gạch/tháng - Trường hợp 2: giá bán 3,4 triệu – tiêu thụ 1.200 tấn gạch/tháng - Trường hợp 3: giá bán 3,6 triệu – tiêu thụ 1.500 tấn gạch/tháng BÀI TẬP 13:
Công ty Phúc Yên bán căn hộ trả góp tại Chung cư Phúc Yên 2, đường Phan Huy Ích Phường 15 quận Tân Bình với giá 12,3 triệu đồng/m2.
Chung cư Phúc Yên 2 có các loại căn hộ như sau:
Loại căn hộ Diện tích (m2)
Căn hộ 1 phòng ngủ loại 1 60
Căn hộ 1 phòng ngủ loại 2 54
Căn hộ 2 phòng ngủ loại 1 91
Căn hộ 2 phòng ngủ loại 2 83
Căn hộ 3 phòng ngủ loại 1 106
Căn hộ 3 phòng ngủ loại 2 97
Ngân hàng Agribank hỗ trợ cho vay với lãi suất tiền vay cố định 11%/năm, thời gian vay tối đa 15 năm và số tiền vay tối đa là 80% trị giá căn hộ.
Yêu cầu: hãy tính số tiền trả góp đều hàng tháng với thời gian trả góp là 5 năm, 10 năm và 15 năm. Biết rằng khách hàng trả trước 20% trị giá căn hộ.
BÀI TẬP 14:
Công ty Trường Sơn chuyên sản xuất dầu gừng chuyên dùng cho thai phụ có số liệu sản xuất kinh doanh như sau:
Số liệu về sản xuất
- Giá mua 1 lít dầu 168.000 vnđ - Giá bán 1 chai dầu (20 ml) 11.400 vnđ - Số lượng sản xuất/năm 350.000 - Biến phí 1000 chai 2.050.000 vnđ - Định phí chưa tính khấu hao 440.000.000 vnđ
Tài sản cố định:
Tài sản cố định Trị giá (triệu đồng) Ghi chú
Nhà xưởng 3.500 Khấu hao 10 năm
Thiết bị dây chuyền tự động 2.200 Khấu hao 5 năm
Hệ thống máy tính 540 Khấu hao 5 năm
Xe vận chuyển 1.400 Khấu hao 7 năm
Tài sản khác 350 Khấu hao 5 năm
Tổng cộng 7.990
Biến phí trên chưa tính phần chi phí phần nguyên liệu tinh dầu.
Theo bình quân cứ 1 lit dầu nguyên liệu thì sản xuất được 42 chai dầu.
Công ty tính khấu hao theo tổng số năm sử dụng Yêu cầu:
- Hãy tính doanh thu, chi phí, lợi nhuận trong 1 năm của công ty.
- Hãy tính giá bán điểm hòa vốn của công ty, vẽ đồ thị điểm hòa vốn thể hiện đường doanh thu và chi phí
- Với giá bán dao động trong khoảng 10.000 đ – 12.000 đ và sản lượng dao động trong khoảng 250.000 chai – 400.000 chai. Hãy tính lợi nhụân biến động của Công ty?.
- Trong trường hợp Công ty muốn lợi nhuận từ việc bán dầu hàng năm là 1 tỷ thì sản lượng sản xuất và tiêu thụ hàng năm là bao nhiêu?
BÀI TẬP 15:
Nhà đầu tư chứng khoán Tùng Teen đang phân tích kế hoạch đầu tư toàn bộ số tiền là 750.000 $ vào các loại trái phiếu của các công ty được đánh giá theo bảng sau:
Trái phiếu của công ty Suất thu lợi hàng năm
Số năm
đáo hạn Đánh giá trái phiếu
ACME Chemical 8,65% 11 1- Cực kỳ tốt
DynaStar 9,50% 10 3- Tốt
Eagle Vision 10% 6 4 – Khá tốt
MicroModeling 8,75% 10 1- Cực kỳ tốt
OptiPro 9,25% 7 3- Tốt
Sabre Systems 9% 13 2 – Rất tốt
Nhằm bảo vệ khoản đầu tư, ông Tùng Teen quyết định đầu tư không quá 25% tiền vào
(có số năm đáo hạn từ 10 năm trở lên). Các trái phiếu DynaStar, Eagle Vision và OptiPro có suất thu lợi cao nhất tuy nhiên không được đầu tư vào 3 loại trái phiếu này quá 35%
của tổng số tiền dành đầu tư vì chúng có rủi ro cao (rủi ro cao khi được đánh giá từ 2 – Tốt trở xuống).
Ông Tùng Teen cần xác định phải đầu tư như thế nào để cực đại hóa lợi tức và phải đảm bảo thỏa mãn các quy định nêu ra như trên?
BÀI TẬP 16:
Công ty may mặc Phương Đông hiện đang lập kế hoạch sản xuất 3 mặt hàng áo Jaket, áo Chemis và áo Bludong để xuất khẩu sang Nga. Được biết chi phí giờ công sản xuất của từng mặt hàng qua 3 công đoạn cắt, may, hoàn chỉnh như sau : (ĐVT: vnđ)
Chemis Bludong Jaket
Giờ công bộ phận cắt 0,2 0,4 0,3
Giờ công bộ phận may 0,3 0,5 0,4
Giờ công bộ phận hoàn chỉnh 0,1 0,2 0,1
Đơn giá/1 SP 48.000 74.000 58.000
Năng lực tối đa của các bộ phận như sau :
Bộ phận cắt 1.250 giờ công
Bộ phận may 1.650 giờ công
Bộ phận hoàn chỉnh 540 giờ công
Tối thiểu trong một tháng mỗi loại phải sản xuất 200 sản phẩm.
Hãy tính kế hoạch sản xuất mỗi loại bao nhiêu để đạt tổng giá trị sản phẩm lớn nhất và vẫn bảo đảm các điều kiện về năng lực sản xuất và quy định số lượng sản phẩm tối thiểu?
BÀI TẬP 17:
Phân tích dữ liệu với chức năng SubTotal, Pivot Table, Consolidate (xem tập tin đính kèm) BÀI TẬP 18:
Công ty bánh kẹo Á Châu cần sản xuất 3 loại bánh trung thu: bánh đậu xanh, bánh thập cẩm và bánh dẻo. Lượng nguyên liệu đường, đậu cho một bánh mỗi loại, lượng dự trữ nguyên liệu, tiền lãi cho một bánh mỗi loại được cho trong bảng sau:
Nguyên liệu Bánh đậu xanh Bánh thập cẩm Bánh dẻo Lượng dự trữ
Đường 40 g 60 g 50g 500 kg
Đậu 70 g 0 g 20 g 300 kg
Lãi 3000 2000 2500 21.797
Hãy lập mô hình bài toán tìm số lượng mỗi loại bánh cần sản xuất sao cho không bị động về nguyên liệu mà lãi đạt được cao nhất
BÀI TẬP 19:
Cơ sở Thành Công chuyên sản xuất đồ gỗ dự định sản xuất ba loại sản phẩm là bàn, ghế và tủ. Định mức sử dụng lao động, chi phí sản xuất và giá bán mỗi sản phẩm mỗi loại ước tính trong bảng sau:
Các yếu tố Bàn Ghế Tủ
Lao động (ngày công) 2 1 3
Chi phí sản xuất (ngàn đồng) 100 40 250
Giá bán (ngàn đồng) 260 120 600
Hãy lập mô hình toán học của bài toán xác định số sản phẩm mỗi loại cần phải sản xuất sao cho không bị động trong sản xuất và tổng doanh thu đạt được cao nhất, biết rằng cơ sở có số lao động tương đương với 500 ngày công, số tiền dành cho chi phí sản xuất là 40 triệu đồng và số bàn, ghế phải theo tỉ lệ 1/6.
BÀI TẬP 20:
Để hỗ trợ cho xã viên đổi xe, Hợp tác xã xe khách Việt Thắng có chương trình bán xe trả góp, trị giá xe 50 chổ ngồi (bao gồm thuế trước bạ...) là 1.8 tỷ VNĐ, lãi suất ưu đãi cố định hàng năm là 9%, trả góp trong vòng 5 năm, mỗi quý trả 1 lần.
Xã viên Văn Hà đăng ký mua xe với số tiền trả trước là 20% trị giá xe.
Yêu cầu:
- Cho biết số tiền ông Hà phải trả từng kỳ.
- Xe ông Hà chạy tuyến đường TP.HCM – Phan Thiết với giá vé 150.000 đồng/1 người, chi phí xăng dầu, tài xế, phụ xế, bến bãi,... là 3.000.000 đồng/chuyến, chi phí cố định hàng tháng dùng để tu bổ xe là 3.000.000 đồng.
Hãy xác định xem 1 tháng ông Hà phải chạy bao nhiêu chuyến mới hòa vốn (giả sử các chuyến đều đầy khách). Vẽ đồ thị điểm hòa vốn (thể hiện rõ lợi nhuận).