Bài tập thực hành Tin học ứng dụng Excel
Bước 1:Tại trang tài liệu thuvienmienphi bạn muốn tải, click vào nút Download màu xanh lá cây ở phía trên. Bước 2: Tại liên kết tải về, bạn chọn liên kết để tải File về máy tính. Tại đây sẽ có lựa chọn tải File được lưu trên thuvienmienphi Bước 3: Một thông báo xuất hiện ở phía cuối trình duyệt, hỏi bạn muốn lưu . - Nếu click vào Save, file sẽ được lưu về máy (Quá trình tải file nhanh hay chậm phụ thuộc vào đường truyền internet, dung lượng file bạn muốn tải) Có nhiều phần mềm hỗ trợ việc download file về máy tính với tốc độ tải file nhanh như: Internet Download Manager (IDM), Free Download Manager, ... Tùy vào sở thích của từng người mà người dùng chọn lựa phần mềm hỗ trợ download cho máy tính của mình
HÌNH ẢNH DEMO
Chỉ xem 5 trang đầu, hãy download Miễn Phí về để xem toàn bộ Nguồn: thuvienmienphi
6 Đánh giá
tamvinhbn 7/6/2021 10:32:20 PM cảm ơn web đã cho tôi tải được một file hữu ích
hongmy1403 7/26/2021 5:00:01 AM mình rất cần tài liệu này...xin cảm ơn!
giabao0811 12/9/2021 5:44:25 PM Hay quá, càm ơn web đã cho một tài liệu hữu ích
phungvanphu2007 12/10/2021 8:42:46 PM Tài liệu hay và đáng giá! Hay download di nao
hungkem 12/12/2021 2:33:48 AM Hay quá, càm ơn web đã cho một tài liệu hữu ích
phatdatf007 1/9/2022 11:11:18 PM Hay quá, càm ơn web đã cho một tài liệu hữu ích
Excel là một trong các kỹ năng quan trọng mà bất kỳ một nhân viên văn phòng nào cũng cần có, nếu bạn đang ở trang này nghĩa là bạn đang là một trong số ít nhân sự văn phòng quan tâm tới Excel. Trong nhiều năm đào tạo Tin học văn phòng cho nhiều doanh nghiệp và học viên, mình nhận thấy là hầu hết các bạn không trú trọng phát triển kỹ năng này trong khi công việc làm đòi hỏi ứng dụng thường xuyên. Đang xem: Bài tập tin học ứng dụng excel Bộ đề bài tập Excel từ cơ bản tới nâng cao này có hơn 50 bài tập từ cơ bản tới nâng cao, hầu hết đều có lời giải, đây là tài liệu được đội ngũ phát triển của lingocard.vn sưu tầm và biên soạn, với bộ bài tập này bạn sẽ có môi trường thực hành các hàm Excel thường gặp như Sum, sumif, sumifs, if, ifs, countif, countifs, vlookup, hlookup, match, index, index kết hợp match…. bên cạnh đó bạn có thể thực hành thêm về định dạng dữ liệu, thực hành các tình huống thường gặp trong doanh nghiệp. Xem thêm: Giải Bài Tập Vật Lý 10 Bài 23 Sách Bài Tập Vật Lý 10 Sbt Bài 23 Để tải tài liệu này, bạn vui lòng click link dưới đây. Xem thêm: Cách Tính Lương Thưởng Tết 2018 Như Thế Nào? Cách Tính Lương Và Thưởng Tết Nguyên Đán 2018 Một số hình ảnh về bộ tài liệu bài tập này: Đánh giá bài viết này Tài liệu kèm theo bài viết 56 BAI TAP TONG HOP EXCEL CO BAI GIAI2.xlsTải xuống 50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…100.000+ học viên trên khắp Việt Nam Tìm hiểu ngay các khóa học của lingocard.vn TẠI ĐÂY Sửa lỗi Java đang tải thư viện chỉ bằng một cú click chuột Hướng dẫn thêm clip art vào file của bạn Hướng dẫn để giải nén, nén file và sao lưu tập tin dữ liệu như thế nào cho hiệu quả? Hướng dẫn cách gõ dấu tiếng Việt, cách gõ tắt, cách chuyển mã với Unikey – 37% Phím tắt trong Word, Excel, Power Point và Tips để tăng tốc độ làm việc 0.0 (0 đánh giá) – 37% EXG04 – Kỹ năng báo cáo – Tư duy tổ chức dữ liệu trên Excel0.0 (0 đánh giá) – 37% PPG01 – Tuyệt đỉnh PowerPoint – Chinh phục mọi ánh nhìn trong 9 bước4.9 (39 đánh giá) Bạn vui lòng đăng nhập để tải vềĐăng nhập Đăng nhập bằng Google Hoặc đăng nhập Đăng nhập Đăng ký tài khoản Đăng ký Đăng nhập bằng Google Hoặc đăng ký Đăng ký Đăng nhập Đóng cửa sổ Top Hỗ trợ khách hàng VỀ lingocard.vn Giới thiệu về lingocard.vn Tuyển dụng giảng viên Tuyển dụng nhân sự Đào tạo cho doanh nghiệp Chính sách bảo mật thông tin Quy định về thanh toán và hoàn trả chi phí Chính sách và quy định chung Quy định mua, hủy, sử dụng khóa học Bộ quy tắc hành xử của giảng viên và học viên trên lingocard.vn Quy trình xác nhận – hoàn, hủy đơn hàng tại hệ thống lingocard.vn hợp tác và liên kết Dạy học trên lingocard.vn Lazi.vn Topcv.vn Thb-consulting.com Tải App lingocard.vn Kết nối với chúng tôi Địa chỉ văn phòng: Tầng 2&3, Lô B7&B8, Ngách 1, Ngõ 187 Đường Nguyễn Tuân, Phường Nhân Chính, Quận Thanh Xuân, Thành phố Hà Nội, Việt Nam Xem thêm bài viết thuộc chuyên mục: Excel
BÀI TẬP THỰC HÀNH EXCEL MÔN TIN HỌC ỨNG DỤNG
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (747.97 KB, 30 trang ) HƯỚNG DẪN THỰC HÀNH TIN HỌC ỨNG DỤNG Cho P – số tiền đầu tư vào một dự án với lãi suất r .Giá trị tích lũy (gốc + lãi) của P vào thời điểm cuối kỳ tính lãi: F = P + P.r = P.(1 + r) Trường hợp tiền lời cuối mỗi được nhập chung vào số tiền gốc để tính lãi cho kỳ sau => lãi kép. Giá trị tích lũy của P sau n kỳ: F = P.(1 + r)n - Cho dự án với các khoản thu – chi bằng nhau mỗi kỳ (dự án có dòng tiền đều). Giá trị tương lai, giá trị hiện tại (FV, PV) của dòng tiền đều A, lãi suất r sau n kỳ: - Giá trị hiện tại ròng (Ner Present Value – NPV): Tổng giá trị hiện tại của các dòng tiền của dự án. Suất hoàn vốn nội (Internal Return Rate IRR): Suất chiết khấu tại điểm NPV = 0. - 1.2 Sử dụng hàm tài chính trong Excel Tham số trong các hàm tài chính: PfV : giá trị tương lai. Rate : lãi suất/suất sinh lời/suất chiết khấu mỗi kỳ. nper : số kỳ. Pmt : số tiền chi trả mỗi kỳ. Type : kiểu chi trả (1 –đầu kỳ, 0 – cuối kỳ), giá trị mặc định: 0. Một số hàm tài chính quan trọng: FV(rate, nper, pmt, [pv], type]) Tính GT tương lai của dòng tiền PV(rate, nper, pmt, [fv], type]) Tính GT ị hiện tại (DT đều). RATE(nper, pmt, pv, [fv], [type]) Tính lãi suất. NPER(rate, pmt, pv, [fv], [type]) Tính số kỳ. 1.3 Hướng dẫn giải một số bài tập mẫu Bài tập 1.1: Tính giá trị tích lũy của khoản đầu tư 2 Một nhà đầu tư mua 100 triệu đồng vào trái phiếu với thời hạn 5 năm, lãi suất 10%/năm, lãi nhập gốc mỗi năm. Tính và vẽ biểu đồ giá trị tích lũy, lợi nhuận của trái phiếu trên sau mỗi năm. Hướng dẫn Lập bảng tính và vẽ biểu đồ như được nêu trong Hình 1.1. Hình 1.1. Tính giá trị tích lũy của khoản đầu tư Công thức [B5] = $C$1 * (1 + $C$2) ^ A5 [C5] = B5 - $C$1 ... copy công thức [B9] = $C$1 * (1 + $C$2) ^ A9 [C9] = B9 - $C$1 Bài tập 1.2: Tính giá trị tương lai của dòng tiền đều Một khách hàng gửi 100 triệu đồng vào ngân hàng với lãi suất cố định 10%/năm. Cuối mỗi năm, khách gửi thêm 10 triệu đồng vào số tiền trên. Hỏi 5 năm, số tiền khách có số tiền là bao nhiêu? Hướng dẫn Lập bảng tính như trong Hình 1.2. Hình 1.2. Tính giá trị tương lai của dòng tiền đều 3 Công thức [B5] = FV(B3, B4, B2, B1) Bài tập 1.3: Tính số tiền trả đều mỗi ký Một khách hàng vay ngân hàng 100 triệu đồng, thời hạn vay 2 năm (24 tháng), lãi suất 1%/tháng, trả gốc + lãi cuối mỗi tháng với số tiền đều nhau. Hỏi số tiền khách phải trả mỗi tháng? Hướng dẫn Lập bảng tính như trong Hình 1.2. Hình 1.3. Tính số tiền trả đều mỗi kỳ Công thức [B4] = PMT(B3, B2, B3, B1) Bài tập 1.4: Tính số kỳ Một khách hàng gửi 100 triệu đồng vào ngân hàng với lãi suất cố định 10%/năm, tính lãi nhập gốc cuối mỗi năm. Cuối mỗi năm, khách gửi thêm 10 triệu đồng vào sổ. Hỏi sau bao nhiêu năm khách có số tiền tích lũy 300 triệu đồng? Hướng dẫn Lập bảng tính như trong Hình 1.5. Hình 1.4. Tính số kỳ gửi tiền. 4 Công thức [B5] = NPER( B3, B2, B1, B4) Bài tập 1.5: Tính lãi suất Một tiểu thương vày 10 triệu đồng của người quen, sau trả góp trong 12 tháng, mỗi tháng trả 1 triệu đồng. Tính lãi suất mà người này phải trả. Hướng dẫn Lập bảng tính như trong Hình 1.6. Hình 1.5 Tính lãi suất vay. Công thức [B4] = RATE(B3, B2, B1) Bài tập 1.6: Ra quyết định đầu tư Công ty X muốn đầu tư vào một dự án. Các nghiên cứu cho thấy rằng công ty phải bỏ ra $1,000,000 vốn đầu tư ban đầu, và sau đó sẽ thu về $140,000 mỗi năm trong 12 năm kế tiếp. Nếu không, công ty có thể đầu tư vào các dự án khác với lãi suất 8%/năm. Công ty có nên thực hiện dự án này hay không? Hướng dẫn • Lập bảng tính giá hiện tại của các khoản thu về với mức chiết khấu 8% (bằng suất sinh lời của các dự án khác) • Kết quả: Giá trị hiện tại của các của các khoản thu lớn hơn số tiền bỏ ra (1,000,000 USD) => nên đầu tư vào dự án. 5 Hình 1.6. Tính giá trị hiện tại của các khoản thu trong dự án. Công thức [B22] = PV(B20, B19, B3, B21) 1.4 Bài tập đề nghị 1. Một khách hàng gửi 100 triệu đồng vào sổ tiết kiệm loại không kỳ hạn tại ngân hàng với lãi suất 0.5%/tháng. Sau 15 tháng, khách hàng này tới ngân hàng để rút tiền. Tính tổng số tiền (gốc + lãi) khách có trong các trường hợp: a. Lãi không nhập gốc. b. Lãi nhập gốc sau mỗi tháng. 2. Tiếp tục bài tập trên. Giả sử mỗi tháng, khách gửi thêm 200,000đ vào sổ, lãi nhập gốc hàng tháng. Hỏi: a. Khách có bao nhiêu tiền trong sổ sau 2 năm? b. Sau bao nhiêu tháng khách mới tiết kiệm được 200 triệu đồng? c. Để có 200 triệu đồng sau 2 năm, mỗi tháng khách phải gửi thêm vào sổ bao nhiêu tiền? 3. Hai ngân hàng cùng cho khách hàng vay số tiền 60 triệu đồng theo hình thức trả góp trong 60 tháng (trả gốc 1 triệu đồng/tháng). Tuy nhiên, cách tính lãi của hai ngân hàng khác nhau như sau: - Ngân hàng 1: thu lãi hàng tháng đều nhau với số tiền bằng số tiền vay ban đầu nhân với lãi suất 0.65%/ tháng. - Ngân hàng 2: thu lãi giảm dần hàng tháng với số tiền bằng số dư nợ còn lại nhân với lãi suất 1.15%/ tháng. So sánh hai cách tính lãi trên (ngân hang nào thu lãi nhiều hơn?). 4. Một quốc gia có thu nhập bình quân đầu người hiện tại 1,500 USD/năm và đang cố gắng nâng mức trên lên gấp đôi trong vòng 10 năm tới. Tính tốc độ tăng trưởng thu nhập bình quân tối thiểu mỗi năm quốc gia này cần đạt. 6 5. Một tài xế taxi mua xe trả góp với giá 400 triệu đồng với lãi suất 1.5 %/tháng, tổng số tiền gốc + lãi được chia thành các phần đều nhau trả trong 24 tháng. Hỏi anh ta phải trả bao nhiêu tiền mỗi tháng? Nếu mỗi tháng tài xế trên trả gấp đôi số tiền được tính thì thời gian trả sẽ rút ngắn xuống còn bao nhiêu tháng. Ngoài cách thanh toán đã nêu, tài xế này còn có thể lựa chọn một trong các phương án sau: - Trả ngay 100 triệu đồng, sau đó trả tiếp trong 24 tháng, mỗi tháng 15 triệu đồng. Trả đều 15 triệu/tháng trong 24 tháng, sau đó trả tiếp 150 triệu khi thanh lý hợp đồng (cuối tháng 24). Giả sử người tài xế taxi này có khả năng trả theo tất cả các phương án kể trên. Hỏi phương án nào có lợi nhất (lãi suất thấp nhất) cho anh ta? 6. Một viên chức gửi 50 triệu đồng vào sổ tiết kiệm không kỳ hạn lãi suất 0.8%/tháng, sau đó đều đặn mỗi tháng gửi thêm vào sổ 500,000 đồng. Hỏi anh ta sẽ có bao nhiêu tiền trong sổ sau 36 tháng? Nếu muốn có 100 triệu đồng sau khoảng thời gian này thì mỗi tháng anh ta phải gửi thêm vào sổ bao nhiêu tiền? 7. Cho bảng giá của một cửa hàng xe máy: BẢNG GIÁ XE MÁY Stt Loại xe 1 Beverly 3V i.e 2 Vespa GPX 125 3V i.e 3 SH 150i 2015 4 NM-X 2015 5 SH 159 6 Vespa GTS Super 7 Vespa LXV 125 8 Vespa S25 9 Shark 170 10 SH Mode Giá bán 147,300,000 122,800,000 80,500,000 80,000,000 79,850,000 79,000,000 73,900,000 73,600,000 60,000,000 49,500,000 Khách có thể trả góp 0%, 30% hoặc 50% tiền mua xe trong 3 tháng, 6 tháng hoặc 12 tháng với lãi suất 0.5%/tháng, trả đều mỗi tháng. Lập bảng tính tiền mua xe cho khách theo mẫu: 7 Yêu cầu - Ô F3: sử dụng cức năng Data Validation List để hiển thị danh sách các loại xe cho người sử dụng chọn. - Ô F4: hiển thị giá bán loại xe được chọn. - Ô F6: sử dụng cức năng Data Validation List để cho phép người sử dụng chọn tỷ lệ trả góp (0%, 30%, 50%). - Ô F7, F8: hiển thị số tiền trả góp, số tiền trả ngy. - Ô F10: sử dụng cức năng Data Validation List để cho phép người sử dụng chọn thời gian trả góp (3 tháng, 6 tháng, 12 tháng). - Ô F11: hiển thị số tiền trả góp hàng tháng. Ví dụ: 8. Một người khác có 500 triệu đồng tiền nhàn rỗi có thể gửi tiết kiệm với lãi suất 0.8%/tháng, lãi nhập gốc hàng tháng, hoặc đầu tư vào một trong hai dự án sau: - Dự án 1: Mua một căn hộ giá 500 triệu đồng, sau đó cho thuê với giá 6 triệu đồng/ tháng, sau 36 tháng bán lại cho người thuê với giá 700 triệu đồng. 8 - 2 Dự án 2: Mua một chiếc xe tải giá 500 triệu đồng, sau đó cho tài xế thuê với giá 20 triệu đồng/tháng, sau 36 tháng chuyển xe cho người thuê. Hỏi phương án nào đem lại hiệu quả (lãi suất đầu tư) cao nhất? BÀI TOÁN LỰA CHỌN PHƯƠNG ÁN ĐẦU TƯ 2.1 Giới thiệu - - Một dự án đầu tư thường được thực hiện trong nhiềy kỳ có nhiều khoản thu/chi (dòng tiền vào/ra) khác nhau trong mỗi kỳ. Việc đánh giá/lựa chọn phương án đầu tư thường được thực hiện theo các phương pháp phân tích NPV và IRR. Khái niệm NPV và IRR: o NPV (Net Present Value): Tổng giá trị của các dòng tiền dự ánui về hiện tại theo một suất chiết khấu nào đó. o IRR (Internal Rateof Return): Suất chiết khấu để NPV = 0. Sử dụng NPV và IRR để đánh giá/lựa chọn phương án đầu tư: Dự án có NPV >0: có thể đầu tư. o Dự án có NPV =0: có thể đầu tư hoặc không. o Dự án có NPV <0: không nên đầu tư. o Dự án có NPV (hoặc IRR) cao hơn => đầu tư có lợi hơn (chi tiết chuên sâu: môn Thẩm định dự án đầu tư). Hàm NPV và IRR của Excel: NPV(rate, value 1, [value 2], …) IRR(values, [guest]) o - 2.2 Hướng dẫn giải một số bài tập mẫu Bài tập 2.1: Tính NPV Công ty X muốn đầu tư vào một dự án với thời hạn 13 năm với dòng tiền dự báo được nêu trong Bảng 1.1. Nếu không đầu tư vào dự án này, công ty có thể đầu tư vào các dự án khác với tỷ suất lợi nhuận 8% /năm. Công ty có nên đầu tư vào dự án này không? Năn 0 1 2 3 4 5 6 Dòng tiền (tỷ VND) -10,000 Năn 7 -8,000 0 1,000 2,000 3,000 4,000 8 9 10 11 12 13 Dòng tiền (tỷ VND) 5,000 6,000 5,00 4,000 3,000 2,000 1,000 Hướng dẫn Lập bảng dòng tiền các năm như trong Hình 2.1. Tính NPV của dự án bằng 1 trong 2 cách: 9 - Tính NPV = tổng giá trị hiện tại (PV) của dòng tiền mỗi năm. Dùng hàm NPV. Hình 2.1. Tính NPV. Công thức [C4] = PV($C$1,, A4, B4) ... copy [C17] = PV($C$1,, A17, B17) [C18] = SUM(C4:C17) [C19] = B4 + NPV($C$1, C5:C17) Bài tập 2.2: Vẽ đồ thị NPV và tính IRR Cho dự án X thực hiện trong 5 năm với dòng tiền dự báo được cho trong bảng dưới đây: Năn 0 1 2 3 Dòng tiền -100,000 -8,000,000 0 1,000,000 Năn 7 8 9 10 Dòng tiền 5,000,000 6,000,000 5,500,000 5,000,000 Yêu cầu - Vẽ đồ thị biểu diễn mối quan hệ giữa NPV với suất chiết khấu. Tính IRR của dự án. 10 Hướng dẫn Lập bảng tính như trong Hình 2.2. Hình 2.2 Vẽ biểu đồ NPV và tính IRR. Công thức [B12] = $B$3 + NPV($A12, $B$4:$B$9) ... [B19] = $B$3 + NPV($A19, $B$4:$B$9) [E18] = IRR[B3:B9] 2.3 Bài tập đề nghị Một công ty đang đánh giá khả năng đầu tư vào một trong hai dự án A và B. Nghiên cứu marketing cho các thông tin sau về từng dự án: - Dự án A: thời gian 12 năm, vốn ban đầu 10 triệu USD, thu về mỗi năm 1.15 triệu USD (từ năm 1 tới năm 12). - Dự án B: thời gian 12 năm, vốn ban đầu 9 triệu USD, dòng tiền trong các năm (từ năm 1 tới năm 12): 11 Năm 1 2 3 4 5 6 Dòng tiền -20 0.5 0.8 1.2 1.5 2 Năm 7 8 9 10 11 12 Dòng tiền 2.5 2.6 2.5 2.5 2.2 2 Yêu cầu 3 - Tính NPV và IRR cho mỗi dự án (lấy rate = 8%). - Cho biết công ty có nên đầu tư vào các dự án này hay không, nếu có thì nên chọn dự án nào. BÀI TOÁN ĐIỂM HÒA VỐN 3.1 Giới thiệu - Cho kế hoạch sản xuất/kinh doanh của một doanh nghiệp với lợi nhuận (Doanh thu - chi phí) phụ thuộc vào số lượng sản phẩm/ hàng hóa sản xuất/kinh doanh. - Bài toán điểm hòa vốn: Tính số lượngng sản phẩm tại điểm Lợi nhuận =0. - Phương pháp: Sử dụng công thức tính trực tiếp, dùng công cụ Goal Seekhoặc đồ hị. Sử dụng công cu Goal Seek để tìm điểm hòa vôn Thực hiện lần lượt các bước: - Lập bảng tính chi phí, doanh thu, lôi nhuận với ô Số lượng sản phẩm chứa giá trị nha\bất kỳ, Doanh thu, Chi phí, Lợi nhuận là các công thức liên quan tới Số lượng sản phẩm. - Sử dụng công cụ Solver để tính Số lượng sản phẩm để Lợi nhuận = 0. Hình 3.1. Công cụ Goal Seek. Các thông số trong Goal Seek: - Set cell: ô muốn đặt giá trị (Lợi nhuận). - To value: Giá trị muốn đ86t. 12 - By changing cell: ô cần tìm giá trị (Số lượng sản phẩm). Tìm điểm hòa vốn bằng đò thị (Đồ thị điểm hòa vốn) - Lập chuỗi giá trị Số lượng sản phẩm tăng dần theo một khoảng xác định. - Lập bảng tính Doanh thu, Chi phí theo Số lượng sản phẩm. - Vẽ đồ thị XY dựa trên kết quả tính toán. - Điểm hòa vốn: điểm cắt giữa Doanh thu và Chi phí. 3.2 Hướng dẫn giải một số bài tập mẫu Bài tập 3.1. Tìm và vẽ đồ thị điểm hòa vốn Năm 2016, công ty X lập kế hoạch sản xuất một lại sản phẩm mới với tổng số lượng dự tính 5,000 đơn vị. Nghiên cứu cho thấy, để thực hiện kế hoạch trên, công ty phải bỏ ra một khoản chi phí cố định 650 triệu đồng để mua sắm thiết bị, thuê nhà xương và quảng cáo, sau đó để sản xuất mỗi đơn vị sản phẩm, công ty lại phải bỏ thêm 2.7 triệu đồng tiền nguyên vật liệu, chi phí điện, nước và nhân công trực tiếp. Dự tính, mỗi đơn vị sản phẩm khi đưa ra thị trường sẽ bán được với giá bình quân 3.4 triệu đồng. Hãy lập bảng dự toán doanh thu, chi phí, lợi nhuận, tìm và vẽ đồ thị điểm hòa vốn cho ây chuyền sản xuất được nêu, Hướng dẫn - Lập bảng tính Dự toán doanh thu, chi phí, lơi nhuận như trong Hình 3.1. Hình 3.2. Bảng dự toán Doanh thu – Chi phí – Lợi nhuận. - Sử dụng công Goal Seek để tìm điểm hòa vốn: o Vào Data | What – If Analysis | Goal Seek. o Nhập các thông số cho Goal Seek (Hình 3.3), nhấp OK. 13 Hình 3.3. Công cụ Goal Seek. Hình 3.4. Kết quả thực hiện Goal Seek. - Lập bảng dữ liệu và vẽ đồ thị điểm hòa vốn (Hình 3.5). Hình 3.5. Đồ thị Điểm hòa vốn. Công thức [B15] =A15*$B$5 [C15] = $B$3 [D15] = A15*$B$4 [E15] = B15 + C15 3.3 Bài tập đề nghị Bài tập 1: ABC là một công ty may chuyên sản xuất sản phẩm áo chống nắng. Chi phí cố định hàng năm của công ty là 800 triệu đồng. Giá bán của mỗi áo là 200 ngàn 14 đồng, chi phí biến đổi để sản xuất và phân phối mỗi áo là 100 ngàn đồng. Xác định sản lượng hòa vốn cho công ty. Vẽ đồ thị điểm hòa vốn. Bài tập 2: Đoàn thanh niên dự định tổ chức một đêm diễn văn nghệ. Hội trường hiện có khoảng 1000 chỗ ngồi và giá vé bán ra cho đêm diễn là 50,000 đồng một vé. Ước tính chi phí cố định của đêm diễn là 25 triệu đồng, chi phí biến đổi cho mỗi vé bán ra là 5,000 đồng. Xác định số lượng khán giả đến xem để đoàn thanh niên sẽ hòa vốn cho đêm diễn này. Vẽ đồ thị điểm hòa vốn. Bài tập 3: Chị Mai chuyên chế biến sữa bắp tại nhà cung cấp cho các quán ăn nhỏ trong vùng. Chi phí cố định hàng tháng chị bỏ ra là 4,000,000 đồng. Chi phí biến đổi tính cho một chai sữa bắp là 2,000 đồng; mỗi chai sữa bắp được bán với giá 3,000 đồng. Hàng tháng chị Mai bán được khoảng 5,000 chai sữa bắp. a/ Xác định số lượng chai sữa bắp chị Mai cần bán để hòa vốn. Vẽ đồ thị điểm hòa vốn. b/ Nếu chi phí cố định tăng lên 10% thì điểm hòa vốn mới như thế nào? c/ Chị Mai tin rằng nếu giá bán giảm xuống còn 2,500 đồng/chai thì mỗi tháng chị có thể bán được thêm 1,000 chai. Nếu giá bán thay đổi thì điểm hòa vốn mới như thế nào? Bài tập 4 Nhà máy XYZ chuyên chế biến cao su thành phẩ từ cao su nguyên liệu. Cho bảng kế hoạch sản xuất năm 2015 của xí nghiệp: KẾ HOẠCH SẢN XUẤT NĂM 20015 Kjoa3n mục Kế hoạch SX (ấn thành phẩm) Giá nguyên liệu (tấn NL) Tỷ lệ tiêu hao (NL/TP) Giá bán /(tấn TP) Chi phí NVL phụ (/tấn TP) Chi phí điện + nước(/tấn TP) Lương trực tiếp ((/tấn TP) Khấu hao thiết bị Chi phí quản lý Chi phí quảng cáo Giá trị 30,000.0 3.5 55% 10.0 0.2 0.1 0.3 100.0 150.0 20.0 15 KẾT QUẢ KINH DOANH Khoản mục Doanh số CP cố định(khấu hao RB, CP QL, QC) Biến phí/ĐV (NVL, điện, nức, lương TT) Tổng biến phí Tỗng chi phí Lợi nhuận Giá trị Yêu cầu: 1. Lập và hoàn tất bảng tính. 2. Giữ nguyên giá bán và các yếu tố khác khác đã cho, tính sản lượng tối thiểu để công ty hòa vốn. 3. Vẽ đồ thị điểm hòa vốn cho từng trường hợp thay đổi giá bán và thay đổi sản lượng. 4 BÀI TOÁN TÌM PHƯƠNG ÁNTỐI ƯU 4.1 Giới thiệu - Bài toán có nhiềuứng dụng trong thực tế, cho phép đưa ra các phương án tối ưu (cực đại/cực tiểu hàm mục tiêu) dựa trên một tập biến độc lập thỏa mãn một số điều kiện ràng buộcnào đó. - Các dạng bài toán tối ưu: o Bài toán cực đại lợi nhuận. o Bài toán cực tiểu chi phú. o Bài toán qui hoạch tuyến tính. o Bài toán qui hoạch phi tuyến tính. 4.2 Qui trình giải bài toán tìm phương án tối ưu trên Excel - Lập bảnh tính để giải bài toán với các thành phần cơ bản: o Ô dữ liệukhông đổi. o Ô biến độc lập (giá trị cần tìm), được khởi đầu bởi các giá trị tiên đoán. o Ô công thức tính hàm mục tiêu và vế trái của các rành buộc, phụ thuộc vào giá tị các biến độc lập. - Sử dụng công cụ Solver để tìm phương án tối ưu. Lưu ý: Solver là một loại công cụ Add – In, phải được cài đặt trước khi sử dụng. Cài đặt Solver: 16 Vào thực đơn File/Options/Add-Ins, xuất hiện hộp thoại như trong hình 4.1. Tại mục Manage, chọn Excel Add-ins, nhấp chọn Go: hình 4.1. Cài đặt Solver. Chọn vào mục Solver Add-in trong hộp thoại Add-Ins, chọn OK: Các thành phần trong hộp thoại Sover: Target Cell: là ô chứa hàm mục tiêu. Tùy theo bài toán mà hàm mục tiêu cần đạt được giá trị tối ưu lớn nhất (Max), nhỏ nhất (Min) hay đạt tới một giá trị cụ thể (Value of). Changing Cells: là ô chứa các biến của bài toán, các ô này sẽ được thay đổi trong quá trình chạy Solver sao cho ô Target Cell đạt được giá trị tối ưu. Constraints: là các ràng buộc áp dụng đối với Changing Cells. 17 hình 4.2. Hộp thoại Solver. 4.3 Hướng dẫn giải một số bài tập mẫu Bài tập 1: Tìm giá trị cực đại của biểu thức 50x 1+30x2+25x3+30x4 với các ràng buộc sau: 2x1+2.5x2+3x3+1.8x4 <=800 (1) 1.2x1+x2+2x3+0.8x4 <=400 (2) 1.5x1+1.2x2+1.5x3+0.8x4 <=380 (3) x2 >=50 (4) x3 >=30 (5) x1, x2, x3, x4 >=0 (6) Bước 1: Xác định biến của bài toán. Bài toán có 4 biến từ x 1 đến x4. Nhập giá trị giả định bất kì cho 4 ô từ B4 đến E4. 18 Bước 2: Xác định hàm tối ưu. Hàm tối ưu của bài toán chính là giá trị F = 50x1+30x2+25x3+30x4. Nhập giá trị hàm tối ưu tại ô B9. Bước 3: Xác định các ràng buộc. Bài toán có 6 ràng buộc được đánh số từ (1) đến (6) ở trên. Nhập công thức cho các ràng buộc (1) (2) (3) tại các ô B16, B17, B18. Thực hiện việc nhập các tham số vào hộp thoại Solver Parameters như sau: 19 Sau khi hoàn tất các bước trên, nhấn nút Solve, kết quả trả về như sau: 20 Từ bảng kết quả nhận được có thể kết luận giá trị tối ưu của hàm mục tiêu là 12409 tương ứng với bộ giá trị (x1, x2, x3, x4) là (24.545, 50, 30, 297.73). Một số lưu ý đối với các chọn lựa trong mục Options của hộp thoại Solver Parameters: 21 Constraint Precision: độ chính xác của giải thuật Solver. Số được thiết lập càng nhỏ, độ chính xác càng cao. Use Automatic Scaling: khi mục này được lựa chọn, Solver sẽ co giãn các giá trị của biến, hàm mục tiêu, các ràng buộc một lượng tương tự nhau để tránh tác động của các giá trị quá lớn hoặc quá nhỏ lên độ chính xác của quá trình thực hiện Solver. Show Iteration Results: khi mục này được lựa chọn, Solver sẽ hiển thị giá trị cho mỗi giải pháp thử (trial solution). Trong quá trình chạy Solver, hộp thoại Show Trial Solution sẽ hiển thị, nhấn Continue để tiếp tục quá trình Solver hoặc nhấn Stop để dừng quá trình chạy Solver và hiển thị kết quả. Solving with Integer Constraints 22 Ignore Integer Constraints: khi mục này được lựa chọn, các ràng buộc giá trị nguyên, giá trị nhị phân sẽ được bỏ qua khi thực hiện Solver. Integer Optimality (%): khi giá trị được thiết lập là a, Solver cho phép giá trị tìm được của bài toán nằm trong phạm vi sai số a% so với giá trị tối ưu thực sự. Nếu muốn Solver tìm đúng giá trị tối ưu thực sự, thiết lập giá trị của lựa chọn này về 0. Max Time (Seconds): thiết lập thời gian tối đa (giây) giải thuật Solver được phép thực thi. Iterations: Thiết lập số vòng lặp tối đa giải thuật được thực thi. Bài tập 2: Xem xét bài toán sau về lựa chọn chế độ ăn tối ưu. Bốn loại thức ăn được xem xét: Khoai lang, thịt gà ta, chuối tiêu, giá đậu xanh. Giá trị dinh dưỡng và chi phí cho mỗi 100gr thức ăn được liệt kê trong bảng sau. Calories Đạm (gr) Béo (gr) Bột (gr) Xơ (gr) Chi phí (nghìn đồng) Khoai lang 119 0.8 0.2 28.5 1.3 1.2 Thịt gà ta 199 20.3 13.1 0 0 20 Chuối tiêu 97 1.5 0.2 22.2 0.8 1.8 Giá đậu xanh 43.0 5.5 0 5.3 2.0 0.5 Hãy tính toán chế độ ăn với chi phí thấp nhất sao cho vẫn đảm bảo chế độ dinh dưỡng, biết rằng người bình thường mỗi ngày cần 2000 calories, 50gr đạm, 65gr chất béo, 300gr chất bột, 25gr chất xơ. Bước 1: Xác định biến của bài toán. Bài toán có 4 biến, chính là lượng thức ăn nạp vào của mỗi loại thức ăn. Tiến hành nhập giá trị lượng thức ăn nạp vào (giá trị bất kì) cho 4 ô từ B4 đến E4. Bước 2: Xác định hàm tối ưu. Hàm tối ưu của bài toán chính là hàm chi phí (chi phí thấp nhất). Nhập giá trị chi phí của mỗi loại thức ăn tại 4 ô từ B8 đến E8. Tính tổng chi phí (hàm tối ưu) tại ô B9. Giá trị của hàm tối ưu được tính như sau: 23 B9 = B4*B8+C4*C8+D4*D8+E4*E8 = SUMPRODUCT(B4:E4, B8:E8) Bước 3: Xác định các ràng buộc. Ràng buộc của bài toán chính là lượng calories, lượng các chất đạm, béo, bột, xơ cần đảm bảo hàng ngày cho mỗi người. Nhập giá trị dinh dưỡng của mỗi loại thức ăn và tính toán lượng dinh dưỡng tổng cộng theo lượng thức ăn nạp vào như bảng sau: Trong đó: F13 = SUMPRODUCT($B$4:$E$4,B13:E13) – tổng lượng calories nạp vào F14 = SUMPRODUCT($B$4:$E$4,B14:E14) – tổng lượng đạm nạp vào F15 = SUMPRODUCT($B$4:$E$4,B15:E15) – tổng lượng chất béo nạp vào 24 F16 = SUMPRODUCT($B$4:$E$4,B16:E16) – tổng lượng chất bột nạp vào F17 = SUMPRODUCT($B$4:$E$4,B17:E17) – tổng lượng chất xơ nạp vào Ràng buộc cần đảm bảo: F13 >=2000 F14 >=50 F15 >= 65 F16 >= 300 F17 >= 25 B4:E4 >=0 Thực hiện việc nhập các tham số vào hộp thoại Solver Parameters theo mẫu sau: 25 |