Ứng dụng tin học trong kinh doanh

CHƯƠNG 1: SỬ DỤNG EXCEL ĐỂ THIẾT KẾ CÔNG THỨC THU CHI TIỀN MẶT 1.1. YÊU CẦU QUẢN LÝ CỦA CÔNG TY ABC 1.1.1. Sử dụng Excel để quản lý kế toán tiền mặt Công ty ABC bao gồm: Thu tiền mặt: Cập nhật thông tin của phiếu thu tiền mặt bao gồm: ngày thu, số chứng từ, nội dung thu, tài khoản đối ứng và số tiền thu. Chi tiền mặt: Cập nhật thông tin của phiếu chi tiền mặt bao gồm: ngày chi, số chứng từ, nội dung chi, tài khoản đối ứng và số tiền chi. Báo cáo tổng kết tiền mặt: bao gồm số dư đầu kỳ, số thu trong kỳ có phân theo từng tài khoản đối ứng, số chi trong kỳ có phân theo từng tài khoản đối ứng, tồn quỹ cuối kỳ. Báo cáo theo ngày: Tương tự báo cáo tổng kết nhưng theo khoản thời gian từ ngày… đến ngày do người sử dụng nhập vào. Một số yêu cầu: Tài khoản đối ứng thu, chi phải có trong bảng tổng kết, nếu không sẽ báo sai. Nhập thông tin về thu và chi tiền mặt ở các sheet Thu và sheet Chi, chương trình sẽ tự động cập nhật tổng tiền thu và chi theo từng tài khoản đối ứng, và số tồn quỹ cuối kỳ. Ngày nhập phải trong khoản từ đầu tháng đến cuối tháng, ngày sau phải lớn hơn hay bằng ngày nhập trước. 1.1.2. Trong tháng có các nghiệp vụ kinh tế phát sinh sau: Số dư đầu tháng 1 năm 2008 của Công ty ABC Trong đó: TK 111: 3.00.000.000 (đơn vị tính: đồng) P Các khoản thu như sau: Phiếu thu số 001 (3/1). Rút tiền gửi ngân hàng nhập quỹ tiền mặt: 30.000.000đ Phiếu thu số 002 (5/1). Khách hàng thanh toán nợ tháng trước bằng tiền mặt: 500.000.000 Phiếu thu số 003 (8/1). Thu tiền bán hàng hoá bằng tiền mặt do công ty A trả, số tiền 165.000.000 đ (Thuế GTGT 15.000.000 đ) Phiếu thu số 004 (10/1). Thu hồi tạm ứng số tiền: 3.500.000 đ Phiếu thu số 005 (12/1). Thu hồi đầu tư chứng khoán ngắn hạn bằng tiền mặt: 410.000.000 đ Phiếu thu số 006 (15/1). Thu hồi tiền ký quỹ ngắn hạn bằng tiền mặt: 13.000.000 đ Phiếu thu số 007 (15/1). Khách hàng ứng trước bằng tiền mặt: 25.000.000 đ Phiếu thu số 008 (20/1). Doanh thu hoạt động tài chính bằng tiền mặt: 5.000.000 đ Phiếu thu số 009 (20/1). Thu nhập khác bằng tiền mặt: 3.000.000 đ Phiếu thu số 010 (25/1). Doanh thu bán sản phẩm bằng tiền mặt: 17.600.000 đ (Thuế GTGT 1.600.000 đ) P Các khoản chi như sau: Phiếu chi 001 (1/1) trả lương cho công nhân viên 50.000.000đ PC số 002 (3/1) mua nguyên vật liệu bằng tiền mặt, hàng đã về nhập kho giá mua 60.000.000đ, thuế GTGT 6.000.000đ PC số 003 (5/1) Mua công cụ dụng cụ bằng tiền mặt, hàng đã về nhập kho giá mua 50.000.000đ, thuế GTGT 5.000.000đ C số 004 (6/1) Chi tiền mặt mua chứng khoán ngắn hạn 40.000.000đ C số 005 (9/1) Mua hàng hóa bằng tiền mặt, hàng đã về nhập kho của doanh nghiệp, giá mua 10.000.000đ, thuế GTGT 1.000.000đ PC số 006 (12/1) Xuất quỹ tiền mặt trả nợ người bán 40.000.000đ PC số 007 (15/1) Xuất quỹ tiền mặt chi cho công tác đầu tư XDCB 20.000.000đ C số 008 (23/1) Chi tiền mặt mua nguyên vật liệu, cuối tháng hàng về nhập kho, hóa đơn mua hàng đã về đến doanh nghiệp giá mua chưa thuế 30.000.000đ, thuế GTGT 3.000.000đ C số 009 (25/1) xuất quỹ tiền mặt mua TSCĐ hữu hình về sử dụng ngay vào hoạt động SXKD, tài sản được đầu tư bằng nguồn vốn đầu tư XDCB, giá mua 100.000.000đ. thuế GTGT 10.000.000đ C số 010 (31/1) Xuất quỹ tiền mặt nộp vào ngân sách nhà nước 2.000.000.000đ * Yêu cầu: Định khoản các nghiệp vụ kinh tế phát sinh. Lên sổ nhật ký thu chi tiền. 1.2. TẠO CÁC SHEET TRONG CHƯƠNG TRÌNH. 1.2.1. Sheet THU. Với phần mềm excel, đầu tiên ta tạo một cơ sở dữ liệu như hình dưới đây và đặt tên sheet là sheet thu. Bao gồm: ngày thu, số chứng từ, diễn giải, tài khoản đối ứng và số tiền: 1.2.2. Sheet CHI. Tương tự như sheet thu ta cũng tạo một cơ sở dữ liệu cho sheet chi cũng gồm các thông tin như: Kỳ kế toán, ngày chi, số chứng từ, diễn giải, tài khoản đối ứng và số tiền. Như hình dưới. 1.2.3. Sheet TỔNG KẾT. Tạo cơ sở dữ liệu cho Sheet tổng kết như sau: Số dư tồn đầu kỳ, nội dung chi, tài khoản đối ứng chi, nội dung thu, tài khoản đối ứng thu, số tiền thu, số tiền chi, tổng thu trong kỳ, tổng chi trong kỳ, tồn quỹ cuối kỳ. Xem hình. 1.3. THIẾT LẬP CÔNG THỨC CHO CÁC SHEET. 1.3.1. Thiết lập công thức cho Sheet THU. 1.3.1.1. Định dạng màu nền cho sheet Chọn khối từ A5 đến E50. Như hình dưới Sau khi chọn khối ta Vào menu Format / Conditional Formatting. + Xuất hiện hộp thoại Conditional Formating như hình bên dưới và ta điền thông tin như hình vẽ. cụ thể như sau. ở đây: chọn “cell value is” ở đây chúng ta chọn: “equal to” ở khung này ta đánh công thức: =”” + Sau khi điền đầy đủ thông tin trong bảng Conditional Formating ta click chọn format Hộp thoại Format xuất hiện, Trong ngăn font, ở vị trí color ta chon màu cho chữ. Xem hình bên dưới Thích màu gì chọn màu đó. ở đây em chọn màu nâu Sau khi hoàn tất các thao tác ở ngăn Font, ta click chọn tiếp ngăn patterns. Chức năng của ngăn này là tạo màu nền cho khối được chọn. Xem hình bên dưới Ở đây là những bảng màu cho bạn lựa chọn. ở đây em chọn màu xanh lá cây Đây là bảng màu cho bạn xem trước trước khi chọn OK + Sau khi chọn màu chữ, màu nền ta chọn OK. Hộp thoại bên dưới xuất hiện. Chọn OK để hoàn tất. Sau khi chọn OK thì khối từ ô A5 đến ô E50 sẽ có màu chữ và màu nền, nếu ta nhập dữ liệu vào ô nào thì ô đó sẽ không có màu nền, còn ô không nhập dữ liệu thì sẽ có màu nền, minh hoạ bằng hình ảnh sau: 1.3.1.2. Định dạng cột ngày chứng từ: Khối từ ô A5 đến ô A50 chỉ được nhập ngày trong tháng và ngày nhập sau phải lớn hơn hoặc bằng ngày nhập trước. Mời làm theo hướng dẫn bằng hình vẽ. gồm 2 bước. Bước 1: thiết lập công thức cho ô từ A5 đến A50 chỉ được nhập ngày trong tháng.Thao tác thực hiện như sau Chọn khối từ ô A5 đến ô A50, vào Data / Validation, như hình sau Xuất hiện hộp thoại Validation, hộp thoại gồm 3 ngăn: Setting, Input Massage và Error Alert, như hình sau: + Ngăn setting ta thao tác như sau: Allow: chọn Custom Formula: ta đặt công thức: = AND(A5>=$A$3, A5<=$B$3), như hình sau Ta gõ công thức vào ô này. Ý nghĩa công thức tại vị trí Formula: Có ý nghĩa phải lớn hơn hoặc bằng ô A3 (chứa dữ liệu đầu tháng) và nhỏ hơn hoặc bằng ô B3 (chứa dữ liệu cuối tháng) Câu nhắc nhỡ người dùng khi sử dụng nhập ngày chứng từ Ghi chú: cho người dùng biết là chỉ có thể nhập ngày trong tháng. + Ngăn Input Massage ta thao tác như hình sau: + Ngăn Error Alert ta làm như hình vẽ sau: Đặt lệnh: STOP Báo người dùng biết đã nhập sai Yêu cầu nhập lại Các thao tác đã được hoàn thành, ta chọn OK để hoàn tất Sau khi ta chọn OK, thì khi ta click chuột trong khu vực từ ô A5 đến ô A50 sẽ xuất hiện hộp thoại nhắc nhỡ chúng ta chỉ được nhập ngày trong tháng. Nếu ta nhập không đúng sẽ xuất hiện hộp thoại báo sai và yêu cầu chúng ta nhập lại. Cụ thể bằng hình vẽ như sau. Nhập sai Hộp thoại báo sai Nhắc nhỡ Bước 2: Qui định khối từ ô A5 đến ô A50 ngày nhập sau phải lớn hơn ngày nhập trước. Ta thực hiện các bước sau: Chọn khối từ ô A6 đến A50 vào Data / Validation, như hình sau: Sau đó, hộp thoại Data Validation hiện lên, ở đây ta chỉ thêm vào chút ít công thức tại vị trí Formula. Công thức được thêm đầy đủ như sau: = AND(A6>=$A$3,A6=A5). Cụ thể bằng hình ảnh dưới đây. Công thức thêm vào là:A6>=A5 Chọn OK để hoàn tất. * Ý nghĩa của công thức: Ngày nhập sau lớn hơn ngày nhập trước và chỉ được nhập ngày trong tháng. Nếu không thỏa mản hai điều kiện trên thì sẽ báo lỗi. Cụ thể bằng hình ảnh Ngày nhập sau nhỏ hơn ngày nhập trước: Báo lỗi 1.3.1.3. Định dạng cột TKĐƯ ( tài khoản đối ứng) Tại sheet tổng kết, ta chèn thêm cột phụ A. Bằng cách chọn cột A vào Insert/ Columns, như hình sau: + Sau khi chọn Columns tại sheet tổng kết sẽ được thêm 1 cột như sau: Cột mới được chèn Sau đó copy những tài khoản từ ô C9 đến ô C22 sang cột phụ A dán từ ô A9 đến ô A22 như hình vẽ Dữ liệu được copy Khối được chọn Sau khi copy dữ liệu qua cột phụ A, ta chọn khối từ A9 đến B22 và đặt tên cho khối được chọn này. Cụ thể như sau: Vào Insert / Name / Define, như hình sau: Kế tiếp là một hộp thoại được hiện ra, và ta đặt tên cho khối này là TKTHU. Như hình vẽ. Chọn OK Bây giờ thì khối từ A9 đến B22 có tên là TKTHU. Giống như hình sau. Tên khối Tại Sheet THU: Khối từ D5 đến D50 + Chọn khối từ D5 đến D50, vào Data/ Validation, như hình sau: Hộp thoại Data Validation xuất hiện. Chọn custom Vị trí đặt công thức + Tại ngăn Setting. Tại vị trí Allow chọn Custom, tại vị trí Formula ta nhập công thức =NOT(ISERROR(VLOOKUP(D5,TKTHU,1,FALSE))). minh họa bằng hình ảnh. + Tại ngăn Input Message, ta đặt câu cảnh báo cho người sử dụng biết là chỉ được nhập TKTHU + Tại ngăn Error Alert ta làm như hình sau: Chọn STOP Đặt câu “BẠN NHẠP SAI!” Đặt câu “HÃY NHẬP LẠI” Hộp thoại cảnh báo bạn đã nhập sai Hộp thoại nhắc nhỡ Hoàn tất các thao tác ta chọn OK, sau khi chọn OK thì ô từ D5 đến D50 xuất hiện hộp thoại cảnh báo như hình vẽ. và bạn chỉ có thể nhập vào những tài khoản thu, nếu nhập sai thi sẽ có hộp thoại thông báo sai không cho bạn nhập tiếp. Hình ảnh minh họa cụ thể như sau: 1.3.1.4. Lập công thức cột Diễn giải Tại ô C5 lập công thức: =IF(ISBLANK(D5),””,VLOOKUP(D5,TKTHU,2,FALSE)), như hình sau: Vị trí đặt công thức Sau đó, ta copy công thức tại ô C5 đến các ô từ C6 đến C50. Lúc bây giờ khi ta nhập số tài khoản thu vào cột TKĐƯ thì bên cột. Diễn giải sẽ hiện chi tiết những thông tin về tài khoản đó. Như hình chụp sau. 1.3.2. Thiết lập công thức cho Sheet CHI 1.3.2.1. Định dạng màu nền Chọn khối từ A5 đến E50, theo hình như sau: Sau khi chọn khối ta Vào menu Format / Conditional Formatting. + Xuất hiện hộp thoại Conditional Formating như hình bên dưới và ta điền thông tin như hình vẽ. cụ thể như sau. ở đây: chọn “cell value is” ở đây chúng ta chọn: “equal to” ở khung này ta đánh công thức: =”” Sau khi điền đầy đủ thông tin trong bảng Conditional Formating ta click chọn format Hộp thoại Format xuất hiện, Trong ngăn font, ở vị trí color ta chon màu cho chữ. Xem hình bên dưới Thích màu gì chọn màu đó. ở đây em chọn màu nâu Ở đây là những bảng màu cho bạn lựa chọn. ở đây em chọn màu xanh lá cây Đây là bảng màu cho bạn xem trước trước khi chọn OK Sau khi hoàn tất các thao tác ở ngăn Font, ta click chọn tiếp ngăn patterns. Chức năng của ngăn này là tạo màu nền cho khối được chọn. Xem hình bên dưới + Sau khi chọn màu chữ, màu nền ta chọn OK. Hộp thoại bên dưới xuất hiện. - Chọn OK để hoàn tất. Sau khi chọn OK thì khối từ ô A5 đến ô E50 sẽ có màu chữ và màu nền, nếu ta nhập dữ liệu vào ô nào thì ô đó sẽ không có màu nền, còn ô không nhập dữ liệu thì sẽ có màu nền, minh hoạ bằng hình ảnh sau: 1.3.2.2. Định dạng cột ngày chứng từ: Khối từ ô A5 đến ô A50 chỉ được nhập ngày trong tháng và ngày nhập sau phải lớn hơn hoặc bằng ngày nhập trước. Mời làm theo hướng dẫn bằng hình vẽ. gồm 2 bước. Bước 1: thiết lập công thức cho ô từ A5 đến A50 chỉ được nhập ngày trong tháng. Thao tác thực hiện như sau Khối được chọn từ A5 đến A50 Chọn khối từ ô A5 đến ô A50, vào Data / Validation, như hình sau - Xuất hiện hộp thoại Validation, hộp thoại gồm 3 ngăn: Setting, Input Massage và Error Alert, như hình sau: + Ngăn setting ta thao tác như sau: Allow: chọn Custom Ta gõ công thức vào ô này. Formula: ta đặt công thức: = AND(A5>=$A$3, A5<=$B$3), như hình sau Ý nghĩa công thức tại vị trí Formula: Có ý nghĩa phải lớn hơn hoặc bằng ô A3 (chứa dữ liệu đầu tháng) và nhỏ hơn hoặc bằng ô B3 (chứa dữ liệu cuối tháng) + Ngăn Input Massage ta thao tác như hình sau: Câu nhắc nhỡ người dùng khi sử dụng nhập ngày chứng từ Ghi chú: cho người dùng biết là chỉ có thể nhập ngày trong tháng. + Ngăn Error Alert ta làm như hình vẽ sau: Đặt lệnh: STOP Báo người dùng biết đã nhập sai Yêu cầu nhập lại Các thao tác đã được hoàn thành, ta chọn OK để hoàn tất Nhập sai Nhắc nhỡ Báo sai Sau khi ta chọn OK, thì khi ta click chuột trong khu vực từ ô A5 đến ô A50 sẽ xuất hiện hộp thoại nhắc nhỡ chúng ta chỉ được nhập ngày trong tháng, nếu ta nhập không đúng sẽ xuất hiện hộp thoại báo sai và yêu cầu chúng ta nhập lại. Cụ thể bằng hình vẽ như sau. Bước 2: Qui định khối từ ô A5 đến ô A50 ngày nhập sau phải lớn hơn ngày nhập trước. Ta thực hiện các bước sau: Chọn khối từ ô A6 đến A50 vào Data / Validation, như hình sau: Sau đó, hộp thoại Data Validation hiện lên, ở đây ta chỉ thêm vào chút ít công thức tại vị trí Formula. Công thức được thêm đầy đủ như sau: = AND(A6>=$A$3,A6=A5). Cụ thể bằng hình ảnh dưới đây. Công thức thêm vào là:A6>=A5 Chọn OK để hoàn tất. * Ý nghĩa của công thức: Ngày nhập sau lớn hơn ngày nhập trước và chỉ được nhập ngày trong tháng. Nếu không thỏa mản hai điều kiện trên thì sẽ báo lỗi. Cụ thể bằng hình ảnh Ngày nhập sau nhỏ hơn ngày nhập trước: Báo lỗi 1.3.2.3. Thiết lập công thức cho cột TKĐƯ (tài khoản đối ứng) * Tại Sheet TKET Khối được chọn + Chèn thêm 1 cột truớc cột E, chèn bằng cách chọn cột E và vào Insert/ columns, như hình sau: + Sau khi chọn Columns sẽ thêm 1 cột như sau: Cột phụ vừa tạo được Kế tiếp ta copy dữ liệu từ ô G9 đến G36 sang ô E9 đến E36. Như hình sau. Sau đó, chọn khối từ E9 đến F36, và đặt tên cho khối này là TKCHI. Bằng cách vào insert chọn Name/ Define. Như hình vẽ Chọn define xong thì hộp thoại sau xuất hiện. ta đặt tên là TKCHi Đặt tên là TKCHI Sau đó chọn OK Tên khối được chọn Tại Sheet CHI: + Chọn khối từ D5 đến D50, vào Data/ Validation, như hình sau: Khối được chọn Sau khi chọn Validation hộp thoại Data Validation xuất hiện. và làm theo hướng dẫn của hình sau. Ngăn settings Vị trí đặt công thức Tại Formula ta đặt công thức là: =NOT(ISERROR(VLOOKUP(D5,TKCHI,1,FALSE))) + Tại ngăn Input Message ta đặt câu chú ý như sau: Nhập “ hãy cẩn thận” Nhập câu chú ý: “chỉ được nhập TKCHI” + Tại ngăn Error Alert ta làm như hình sau: Chọn lệnh STOP Câu cảnh báo “nhập sai” Nhập lại Hoàn tất ta chọn OK, sau khi chọn Ok thì khối từ D5 đến D50 sẽ xuất hiện dòng chữ nhắc nhỡ chúng ta: rằng nó chỉ cho phép nhập Tk chi. Nếu chúng ta nhập sai nó sẽ báo lỗi. cụ thể bằng hình vẽ. Nhập sai Hộp thoại báo sai Hộp thoại nhắc nhỡ 1.3.2.4. Lập công thức cột Diễn giải. Tại ô C5 lập công thức: =IF(ISBLANK(D5),””,VLOOKUP(D5,TKCHI,2,FALSE)), như hình sau: Vị trí đặt công thức - Sau khi lập công thức tại cột diễn giải, thì khi ta nhập tài khoản tại khoản tại cột TKĐƯ thì cột diễn giải sẽ tự động hiển thị thông tin chi tiết tương ứng với tài khoản đó. Ví dụ minh họa 1.3.3. Thiết lập công thức cho Sheet TKET 1.3.3.1. Số tiền thu Nhập số phát sinh trong kỳ Ô F2 đặt tên là TN Tại sheet TKET tại ô F2 ta vào insert/ Name/ Define để đặt tên cho ô F2 là TN Sau khi chọn Define hộp thoại sau xuất hiện. làm như hình Vị trí đặt tên Tương tự ô F2, tại ô F3 ta cũng làm các thao tác trên và đặt với tên là DN Tại ô F3 đặt tên là DN Tại Sheet THU Tạo thêm cột phụ F {lấy TKĐƯ trong giai đoạn TN-DN (từ ngày - đến ngày)}, như nhình sau: Vị trí đặt công thức Kế tiếp ta copy công thức đến ô F50. Như hình sau Copy như hình này Tạo thêm cột phụ G (lấy số tiền trong giai đoạn TN-DN (từ ngày - đến ngày)), như hình sau: Vị trí đặt công thức Sau đó ta tiếp tục copy công thức đến ô G50. Như hính sau. Copy như hình này Chọn khối từ F5 đến F50 đặt tên khối này là CPTKTHU: bằng cách vào insert/ Name/ Define. Như hình sau Xuất hiện hộp thoại Define Name. Ta làm như sau. Vị trí đặt tên Chọn khối từ G5 đến G50 đặt tên khối này là STPTHU: bằng cách vào insert/ Name/ Define. Như hình sau Xuất hiện hộp thoại Define Name. Ta làm như sau. Đặt tên là STPTHU Tại ô D9 =SUMIF( CPTKTHU,A9, STPTHU) Sau đó copy đến D22, như hình sau: 1.3.3.2. Số tiền chi: Nhập số phát sinh trong kỳ, như hình sau: Tại Sheet CHI Tạo thêm cột phụ F (lấy TKĐƯ trong giai đoạn TN-DN (từ ngày - đến ngày), như nhình sau: Copy như hình này Copy từ F5 đến F50, như hình sau: Ô G5 ta viết công thức: =IF(AND(A5>=TN,A5<=DN),E5,0) - Tạo thêm cột phụ G (lấy số tiền trong giai đoạn TN-DN (từ ngày - đến ngày)), như hình sau: Ta copy công thức từ ô G5 đến G50 Bây giờ ta chọn khối từ F5 đến F50 và đặt tên là CPTKCHI: bằng cách vào Insert/ Name/ Define Xuất hiện hộp thoại Define Name. Như hình sau Vị trí đặt tên Tương tự ta đặt tên cho khối từ G5 đến G50 là STPCHI. Như hình sau Tên khối từ G5 đến G50 Tại Sheet TKET ta lập công thức tại ô H9, như hình sau: Tại ô H9 lập công thức =SUMIF (CPTKCHI,H9,STPCHI) Ta copy công thức từ ô H9 đến ô H36. Minh họa hình ảnh * Tại sheet TKET 1.3.3.3. Tính tổng số tiền thu. Ta tạo công thức tại ô D37: =sum(D9:D22) Tại D37 thiết lập công thức tính tổng số thu vào, như hình sau: 1.3.3.4. Tính tổng số tiền chi Tại H37 thiết lập công thức tính tổng số chi ra, như hình sau: Ô H37 ta tạo công thức: =sum(H10:h36) 1.3.3.5. Tính tổng số tiền tồn cuối kỳ: Tại H38 thiết lập công thức tính tồn cuối kỳ, như hình sau: Ô H39 ta đặt công thức như sau: =F4+D37-H37 CHƯƠNG 2: SỬ DỤNG EXCEL ĐỂ GIẢI BÀI TOÁN ĐIỂM HOÀ VỐN LÝ THUYẾT. Giả sử ta đặt: Q: Sản lượng hòa vốn B: Định phí P: Giá bán 1 sản phẩm V: Biến phí một sản phẩm Doanh nghiệp hòa vốn khi DOANH THU – CHI PHÍ = 0 Hay lợi nhuận = 0 P * Q – (V * Q + B ) = 0 Khi đó sản lượng hoà vốn: QHV = B / (P-V). BÀI TOÁN. Công ty Nhật Tân có bảng số liệu như sau: (ĐVT: Đồng) Chỉ tiêu Số tiền Doanh thu (DT) 1.000sp Biến phí (v) Định phí (B) LN 600.000.000 200.000 80.000.000 30.000.000 Xác định điểm hòa vốn. Vẽ đồ thị điểm hòa vốn. SỬ DỤNG EXCEL ĐỂ GIẢI QUYẾT BÀI TOÁN. Để Q trở thành QHV thì Q phải đạt giá trị sao cho lợi nhuận bằng không, để thực hiện trên Excel đầu tiên chúng ta cho Q một giá trị nào đó để tính ra kết quả lợi nhuận, sau đó chúng ta dùng hàm Goal seek để yêu cầu Q đổi thành một giá trị nào đó sau cho lợi nhuận bằng không, giá trị đó chính là Q hoà vốn, ta thực hiện các bước sau: Tạo vùng dữ liệu. Thiết lập chỉ tiêu và hàm mục tiêu. Ta phải thiết lập được 7 chỉ tiêu như sau: Sản lượng, giá bán, doanh thu, tổng biến phí, tổng định phí, tổng chi phí, lợi nhuận. Hàm mục tiêu: Giá bán lấy ở vùng dữ liệu. Doanh thu = Giá bán * Sản lượng. Tổng biến phí = Biến phí sản lượng * Sản lượng. Định phí lấy trên vùng dữ liệu. Tổng chi phí = Tổng biến phí + Định phí. Lợi nhuận = Tổng doanh thu – Tổng chi phí. Tại cell C7 để lấy giá bán, ta lập công thức như sau: Tại cell C8 để tính doanh thu, ta lập công thức như sau: Tại cell C9 để tính tổng biến phí, ta lập công thức như sau: Tại cell C10 để tính tổng định phí, ta lập công thức sau: \ Tại cell C11 để tính tổng chi phí, ta lập công thức sau: Tại cell C12 để tính lợi nhuận, ta lập công thức sau: Ta tính ra được lợi nhuận như sau: Vậy khi hòa vốn thì sản lượng đạt 200, doanh thu đạt 120,000,000. Sử dụng Hàm mục tiêu Goal seek để giải bài toán. Ý nghĩa của sử dụng hàm Goal seek: Hàm Goal seek tỏ ra tiện ích khi chúng ta không chỉ tìm sản lượng điểm hòa vốn mà còn xác định giá bán điểm hòa vốn, chi phí biến đổi điểm hòa vốn… Trong thực tế để tính ra lợi nhuận chúng ta sử dụng rất nhiều bảng tính liên kết nhau, thí dụ từ một sản lượng dự kiến sẽ căn cứ vào các tiêu hao nguyên vật liệu, nhân công, khấu hao… để tính chi phí, trong định phí sử dụng nhiều phương án như tỷ lệ giữa vay vốn và người mua ứng trước, mua máy hay thuê máy… giá bán cũng biến đổi theo số lượng sản phẩm tiêu thụ. Từ các số liệu dự kiến đó nhà tài chánh mới xác định lợi nhuận ban đầu, sau đó nhờ sử dụng Goal seek nhà tài chánh có thể xác định giá trị dự kiến của một trong nhiều thông số đó biến đổi để kết quả lợi nhuận đạt điểm hòa vốn hoặc một giá trị mong muốn nào đó mà nếu thực hiện bằng cách tính thông thường sẽ tốn nhiều công sức. Ý nghĩa tổng quát của chức năng Goal seek là với một kết quả là một công thức (thí dụ lợi nhuận) được hình thành từ nhiều thông số đầu vào thí dụ sản lượng sản xuất, tỷ lệ sản lượng tiêu thụ, giá bán, chi phí tiêu hao định mức NVL, lãi suất ngân hàng...(là những cell giá trị nhập vào), chúng ta có thể chọn một giá trị đầu vào cần phải biến đổi để phù hợp, khi đó hàm Goal seek sẽ xác định giá trị đầu vào này cho chúng ta. Sử dụng Goal seek để giải quyết bài toán: Đặt con trỏ tại cell Lợi Nhuận, vào Tools à Click chọn Goal seek: Ô đặt con trỏ (Lợi nhuận) Đến giá trị nào đó (Bằng 0) Ô sẽ thay đổi (Ô sản lượng) Xuất hiện giao diện của Goalseek: Sau đó lần lượt Click OK để hoàn tất. Sau đó tiếp tục Click OK Click OK để hoàn tất Hàm mục tiêu Goal seek tìm ra kết quả sẽ thông báo Goal seeking with C12 found a solution, click OK để hoàn tất, khi đó ta sẽ thấy ô C6 có giá trị là 200 nó là sản lượng điểm hoà vốn, và ô C12 có giá trị là 0 nó là lợi nhuận điểm hoà vốn. Ta được bảng sau Vậy khi VẼ Cty hòa vốn thì sản lượng đạt 200, doanh thu đạt 120,000,000. ĐỒ THỊ ĐIỂM HÒA VỐN. Dùng lệnh Copy vùng dữ liệu hòa vốn ở trên và cho thêm điểm để tạo thành một bảng dữ liệu mới sau đó thực hiện vẽ đồ thị hòa vốn. Chọn khối từ B16:E22, vào Insert à Click chọn Chart: Xuất hiện hộp thoại, tại thanh Standard Types ta lần lượt Click chọn dạng đồ thị như sau: Click chọn dạng đồ thị Click chọn Next Sau khi Next sẽ xuất hiện hộp thoại, tại thanh Data Range ta thực hiện các bước sau: Click chọn Next Click chọn Rows Tiếp tục xuất hiện hộp thoại, tại thanh Titles ta nhập tên đồ thị, trục sản lượng, trục doanh thu. Sau đó Click sang Gridlines, xuất hiện hộp thoại: Gỡ bỏ dấu ü để đồ thị trở nên đơn giản hơn Click chọn Next Chọn nơi lưu đồ thị: Lưu đồ thị tại Sheet mới Lưu đồ thị tại Sheet cũ Click chọn Finish để hoàn thành Đồ thị hòa vốn: Để cho đồ thị không mang giá trị âm ta nhấp đôi chuột vào trục doanh thu, ta được giao diện sau: Tại Minimum chọn giá trị 0: Click OK để hoàn tất Click chọn giá trị bằng 0 Ta được ĐỒ THỊ ĐIỂM HÒA VỐN hoàn chỉnh. Điều chỉnh ĐỒ THỊ HÒA VỐN theo ý muốn: Điều chỉnh nét đồ thị ta nhấp đôi vào những nét của đồ thị để xuất hiện hộp thoại, sau đó tại thanh Pattems ta điều chỉnh theo ý muốn: Click OK để hoàn tất Điều chỉnh màu nền của đồ thị ta nhấp đôi vào đồ thị để xuất hiện hộp thoại, sau đó tại thanh Pattems ta điều chỉnh theo ý muốn: Click chọn màu tùy ý Click OK để hoàn tất Chương 3: DỰ ÁN SẢN XUẤT GẠCH CHỊU NHIỆT 3.1. BÁI TOÁN 3.1.1. MỤC TIÊU CỦA DỰ ÁN Đầu tư xây dựng nh2 máy ban tự động sản xuất gạch Manhezi và gạch cao nhôm là loại gạch chịu nhiệt trên 18250, nhiều kích cở sử dụng để xây lò luyện cán thép, luyện ciment, luyện thủy tinh… thay thế gạch nhập khẩu ( chủ yếu từ Trung Quốc). Công suất dự kiến của nhà máy là 2000 tấn/ năm, công suất tối đa là 2500 tấn/ năm. 3.1.2. Mức đầu tư và nguồn vốn của dự án: Đầu tư trang thiết bị: STT TÊN THIẾT BỊ TRỊ GIÁ 1 Máy ép 400 tấn 650000000 2 Máy nghiền trục 210000000 3 Máy trộn + nghiền keo 38000000 4 Lò xấy + máy phun 92000000 5 Thiết bị điện 40000000 6 Máy vi tính 24000000 7 Công cụ nhuôn + cân 121000000 8 Máy ép 1500 tấn mới (Korea) 1210000000 9 Xe nâng 80000000 10 Xây dựng lò nung 25 tấn 950000000 11 Chi phí lắp đặt chuyển giao 70000000 12 Bình trung thế và hệ thống điên 3 pha 224000000 Tổng cộng 3709000000 Vốn đầu tư thiết bị 3709000000 Vốn đầu tư nhà xưởng 2300000000 Tổng vốn đầu tư 6019000000 Nguồn vốn đầu tư toàn bộ là vốn của chủ dự án 3.1.3. Chi phí sản xuất: Chi phí biến động cho 1 tấn gạch thành phẩm: Chi phí nguyên vật liệu 920000 Chi phí nhân công trực tiếp 348000 Chi phí phân xưởng 20000 Chi phí khác 150000 Tổng cộng 1618000 Chi phí cố định về quản lí trong 1 năm là 350 triệu đồng ( phục vụ cho việc sản xuất từ 1600 – 2500 tấn/ năm). Chi phí này chỉ tính chi phí khấu hao. Khấu hao thiết bị trong thời gian 5 năm, giá trị thanh lí của thiết bị khấu hao là 160000000đ. Khấu hao nhà xưởng trong thời hạn 7 năm, giá trị thanh lí sau khấu hao là 58000000đ. 3.1.4. Doanh thu Công suất sản xuất và tiêu thụ dự kiến là 2000 tấn/ năm, trong đầu năm thứ nhất đạt 80% đự kiến, năm thứ 2 đạt 905 đự kiến, từ năm thứ 3 trở đi đạt 100% đự kiến. Giá bán được tính là 2800000đ/ năm. Thuế doanh thu 4%; thuế lợi tức 35% lợi nhuận miễn 1 năm đầu. 3.2. Yêu cầu: Hãy tính NPV và IRR của dự án. Tỷ suất chiếc khấu NPV là 12%. Trong trường hợp sản lượng tiêu thụ dao động từ 1700 tấn – 2300 tấn/ năm và giá bán dao động từ 2.6 triệu – 3.2 triệu/ tấn, Hãy tính độ nhạy của NPV và IRR. 3.3. BÀI GIẢI: 3.3.1. Sheet thông số. Vào Excel ta tao sheet thông số nhập các giá tri thông số đã dự toán(chi phí cố định, biến đổi, sản lượng tiêu thụ, giá bán,…) để làm căn cứ tính chi phí, doanh thu và lợi nhuận.Ta có bảng thông số sau. =SUM(C4:C15) =SUM(C17:C1)8) Vốn đầu tư thiết bị, Tổng cộng =SUM(C4:C15) Tổng vốn đầu tư: Tổng cộng =SUM(C17:C18) Sản lượng tiêu thu: Tại B6 chúng ta lập công thức='Sheet thông số'!$C$22*Sheet2!B6/1000000. Sau đó Autofill kéo ngang đến Cell F6. Doang thu dự án tại B8, ta lập công thức='Sheet thông số'!$C$22*Sheet2!B6/1000000. Sau đó Autofill kéo ngang đến Cell F8.( trong công thức ta chia cho 1000000 để quy ra đơn vị tính triệu đồng). 3.3.3. Sheet khấu hao. Vào Excel ta tao Sheet khấu hao sau đó ta tính số khấu hao hằng nămcủa dự án sản xuất gạch chịu nhiệt. Ta có bảng khấu hao như sau. Khấu hao thiết bị trong vòng 5 năm tại B3 lập công thức=(('Sheet thông số'!$C$17/1000000)-'Sheet thông số'!$C$38)/'Sheet thông số'!$C$37. Sau đó Autofill kéo ngang tới Cell F3. Khấu haonhà xưởng trong vòng 7 năm tại B4 ta có công thức=(('Sheet thông số'!$C$18/1000000)-'Sheet thông số'!$C$36)/'Sheet thông số'!$C$35. Sau đó Autofill kéo ngang đến F3. Giá trị thu hồi tại năm thứ 5 gồm giá trị thanh lí thiết bị và nhà xưởng cộng thêm giá trị khấu hao nhà xưởng của 2 năm còn lại chưa khấu hao: Tịa F5 ta lập công thức:='Sheet thông số'!$C$38+('Sheet thông số'!$C$18/1000000)-SUM('Sheet khấu hao'!B4:F4) 3.3.4. Sheet chi phí. Vào Excel ta tạo Sheet chi phí sau đó tính chi phí sản xuất bao gồm biến phí và đinh phí của dự án trobg thời hạn vòng đời đự ánlà 5 năm. Tính chi phí cố định bằng khấu hao năm cộng với chi phí cố định khác: TaiB3 lập công thức='Sheet thông số'!$C$41+'Khau hao'!$B$3+'Khau hao'!$B$4. Sau đó Autofill kéo ngang đến F3. Tính chi phí biến đổi bằng biến phí để sản xuất 1 tấn sàn phẩm nhân với số lượng sản xuất( tiêu thụ )trong năm: tại B4 lập công thức=('Sheet thông số'!$C$38/1000000)*'Doanh thu'!B6. Tổng chi phí ta tính như sau tại B5 ta lập công thức =B3+B4 Sau đó Autofill kéo ngang đến F5. 3.3.5. Sheet lợi nhuận: Vào Excel ta tao Sheet lợi nhận để căn cứ vào chi phí sàn xuất doanh thu và thuế để tính lợi nhuận của dự án trong thời hạn vòng đới dự án là 5 năm. Từ đó ta có bản sau. Tinh doanh thu các năm ta lập công thức ='Doanh thu'!B8. Sau đó Autofill keo ngang đến cell F4. Thuế doanh thu bằng doanh thu nhân với tỷ suất, tại B4 ta lập công thức:=B3*'Sheet thông số'!$C$46. Sau đó kéo ngang đến F4 Tại B5 ta lập công thức =B3-B4 sao đó Autofill kéo ngang đến F5. Tại B7 ta lập công thức ='Chi phí'!B5 Sao đó Autofill kéo ngang đến F7. Tại B9 ta lập công thức =B5-B7 sau đó Autofill kéo ngang đến F9. Tai C10 ta lập công thức =IF(C9>0,C9*'Sheet thông số'!$C$47,0) sau đó Autofill kéo ngang đến F10. Tại B11 ta lập công thức =B9-B10 sau đó Autofill keo ngang đến F11. 3.3.6. Sheet dòng tiền: Vào Excel ta tạo bảng dòng tiền để căn cứ vào vốn đầu tư, lợi nhuận, số khấu hao và giá trị thu hồi để tính dòng tiền cua dự án trong thời hạn vòng đời dự án là 5 năm, qua đó xác định NPV và IRR của dự án. Để tính dòng tiền chi đầu tư nhà máy , tại Cell B4 ta lập công thức ='Sheet thông số'!$C$26/1000000 Năm chi đầu tư gọi là năm , trong công thức chúng ta chia cho 1000000 để quy về đơn vị triệu đồng. Tính dòng ngân lưu ra bằng tổng các dòng tiền ra tại B6 chúng ta lập công thức =SUM(B4:B5). Sau đó Autofill kéo ngang đến G6 Tính dòng ngân lưu vào từ hoạt động kinh doanh chính là lợi nhuận sau thuế của dự án: tại cell C7 ta lập công thức ='Lợi nhuận'!B11. Sau đó Autofill kéo ngang đến G7. Tính khấu hao: tại C10 ta lập công thức ='Khau hao'!B3+'Khau hao'!B4. Sau đó kéongang đênG10. Tính thanh lí và phần chưa khấu hao tại cell G11 ta lập công thức ='Khau hao'!F5. Tinh dòng ngân lưu vào bằng tổng các dòng tiền vào: tại cell B12 ta lập công thức =SUM(B7:B11). Sau đó Aotufill kéo ngang đến G12. Tính dòng ngân lưu thuần(cân đối dòng tiền) băng dòng ngân lưu vào trừ dòng ngân lưu ra: tai B13 chúng ta lập công thức =B12-B6. Sau đó Autofill Đến G13. Tính NPV của dự án: Tại B14 ta lập công thức =B13+NPV('Sheet thông số'!C48,'Dòng tiền'!C13:G13). Tính IRR :Tại B15 Ta lâp công thức=IRR(B13:G13). 3.3.7. Độ nhạy NPV. Bước 1: Tại Sheet Thông số, ta tạo Ô từ Cell E28:J34. Tại E28 ta nhập giá trị của NPV.Như hình sau: Bước 2: Chọn khối từ E28:J34. Sau đó vào menu Data=>Table. Dòng Row input cell lick vào Cell C28. Dòng Column input cell lick vào Cell C29. Sau đó lick OK Ta có kết quả sau: 3.3.8. Độ nhạy IRR. Bước 1: Tại Sheet thông số, ta tạo ô từ Cell E41:J48. Tại É ta nhập các giá trị của IRR. Như hình sau. Bước 2: Chọn khối từ E41:J48. Sau đó vào Menu Data=> Table. Dòng Row input cell lick vào cell C28. Dòng Column input cell vao cell C29. Ta có kết quả sau: Chương 4: SỬ DỤNG EXCEL ĐỂ THIẾT KẾ CÔNG THỨC TÍNH LƯƠNG NHÂN VIÊN THEO THỜI GIAN 4.1. YÊU CẦU TÍNH LƯƠNG CỦA CÔNG TY MINH KHANG: 4.1.1. Qui định về nhập bảng chấm công: Mỗi nhân viên đi làm sẽ được chấm công hàng ngày, làm cả ngày đánh dấu X, làm ½ ngày đánh dấu O, nghỉ có phép nhập chữ CP, nghỉ không phép nhập chữ KP. 4.1.2. Qui định tính lương như sau: Mỗi nhân viên khi vào làm việc sẽ nhận một mức lương cơ bản. Trong tháng nếu làm đủ ngày công (không có ngày nghỉ) sẽ được thưởng thêm 10% Lương cơ bản. Một ngày nghỉ không phép sẽ bị trừ 3 ngày lương cơ bản. Một ngày làm phụ trội được tính thêm 2 ngày lương cơ bản, một tháng được qui định 22 ngày công, số ngày phụ trội là số ngày dư ra so với 22 ngày qui định. Tuy nhiên số ngày phụ trội không được vượt quá 4 ngày. Trong một năm, mỗi nhân viên được nghỉ 12 ngày phép, nếu tổng số ngày nghỉ có phép của nhân viên từ đầu năm đến thời điểm tháng tính lương vượt quá 12 ngày thì mỗi ngày nghỉ vượt sẽ bị trừ một ngày lương cơ bản. Một ngày lương =Lương cơ bản / 22. Lương tháng = Lương cơ bản + Thưởng + Lương phụ trội – Nghỉ không phép – Nghỉ vượt ngày phép. + Lương phụ trội – Nghỉ không phép – Nghỉ vượt ngày phép. Tổng hợp ngày nghỉ của các nhân viên trong công ty như sau: Lương Sơn Bá (Giám đốc), ngày 13/02 làm ½ ngày. Chúc Anh Đài (PGĐ), nghỉ ngày 15/02, có phép. Lưu Chí Vỹ (PGĐ), ngày 14/02 nghỉ có phép Ngô Gia Bảo (Thư ký), ngày 13/02 nghỉ không phép Ngô Kiến Huy (Thủ quỹ), ngày 01/02 làm nữa ngày. Phạm Thanh Thảo (NV), ngày 20/02 nghỉ có phép. Phạm Khánh Hưng (NV), ngày 21/02 nghỉ không phép. Phan Tú Trinh (NV), ngày 13/02 nghỉ có phép. Lý Tiểu Long (NV), ngày 14/02 làm nữa ngày Lâm Thị Trà My (NV), ngày 20/02 làm nữa ngày Nguyễn Châu Tính (NV), đi làm đầy đủ Huỳnh Thị Ngọc (NV), ngày 01/01 làm nữa ngày 4.2. TỔ CHỨC TÍNH LƯƠNG TRÊN EXCEL: 4.2.1. Tổ chức Sheet bảng chấm công (BCCONG) Để tổ chức tính lương, chúng ta sẽ tạo Sheet bảng chấm công và hàng ngày sẽ nhập chấm công các nhân viên. Căn cứ vào thông tin và các yêu cầu trên chúng ta tổ chức sheet bảng chấm công như hình sau: 4.2.2. Tổ chức sheet tổng hợp nghỉ có phép (THNGHIPHEP) Do cần phải so sánh giữa số ngày nghỉ được phép (12 ngày) với tổng số ngày nghỉ có phép của mỗi nhân viên từ đầu năm đến tháng tính lương, nên chúng ta cần tổ chức một Sheet lưu trữ số ngày nghỉ có phép từng tháng. Căn cứ vào thông tin và các yêu cầu trên chúng ta tổ chức sheet tổng hợp nghỉ có phép như hình sau: 4.2.3. Tổ chức sheet lương (LUONG) Sau đó ta lập Sheet bảng lương vớí các công thức được tính theo qui định lương và theo ngày công của mỗi người trong bảng chấm công. Căn cứ vào thông tin và các yêu cầu trên chúng ta tổ chức sheet lương như hình sau: 4.3. THIẾT LẬP CÔNG THỨC CHO SHEET BẢNG CHẤM CÔNG (BCCONG). 4.3.1 Tạo một hàng chứa ngày trong tháng: Tại sheet BCCONG ta dấu các cột trong khoảng từ cột G đến cột AD. Tại ô D3 ta nhập vào ngày 1/2/2010 và kéo đến ô AH. Cụ thể như hình sau. Đây là hàng thể hiện ngày trong tháng 4.3.2. Tạo một hàng chứa thứ trong tuần. Từ ô D4 đến ô AE4, chúng ta nhập các thứ của tháng 2/2010 bằng cách nhập vào ô D4 giá trị MON là thứ 2 tương ứng với 1/2/2010 sau đó kéo đến ô AE4, minh họa bằng hình sau Đây là hàng thể hiện thứ trong tháng 4.3.3. Định dạng ngày thứ 7, chủ nhật trong tuần Định dạng ngày thứ 7và chủ nhật có màu để khác với các ngày thường khác. * Định dạng ngày thứ 7 Chọn từ cell D4 đến cell AE4, vào Format/ Conditional Formating, minh họa bằng hình sau: Hộp thoại Conditional Formating xuất hiện Điều kiện 1 chọn Cell Value Is, điều kiện 2 chọn equal to, điều kiện 3 nhập vào =”sat”, sau khi đã đặt đủ các điều kiện ta chọn Format, như hình sau: chọn “Cell Value Is” ở đây chọn “equal to” nhập vào =”sat” Nhập đủ 3 điều kiện, ta click chọn Format…, hộp thoại Format cell xuất hiện + Ngăn Font dùng để chọn màu chữ, như hình sau: 1. Ngăn Font. 2. Chọn màu chữ. + Ngăn Patterms chọn màu nền, như hình sau 1. Ngăn chọn màu nền 2. Chọn màu nền 3. Click chọn OK để hoàn tất. + Sau khi chọn màu nền và màu chữ ta chọn OK để hoàn tất thì thứ 7 sẽ có màu nền và màu chữ, như hình sau: 1. Màu nền và màu chữ được chọn. 2. Click chọn OK để hoàn thành việc định dạng màu chữ và màu nền + Sau khi Click chọn OK thì ngày thứ 7 sẽ có màu nền và màu chữ, như hình sau: * Định dạng ngày chủ nhật: Tương tự ngày thứ 7, nhưng ở hộp thoại Conditional Formating ta chọn Add để thêm vào ngày chủ nhật. minh họa. Chọn Add Sau khi chọn Add, thì xuất hiện thêm một điều kiện mới, như hình sau. Ta làm theo hình minh họa. 1. Chọn Cell value Is 2. Chọn equal to. Có nghĩa là khi nhập dữ liệu thì cell đó sẽ mất màu 3. Nhập =”sun”. Có nghĩa là ngày chủ nhật sẽ có màu nền và màu chữ do chúng ta qui định. Kế tiếp ta chọn Format để chọn màu chữ và màu nền cho ngày chủ nhật + Ngăn Font chọn màu chữ, như hình sau: Chọn màu xanh + Ngăn Pattens chọn màu nền như hình sau: 2. Chọn màu hồng Sau đó, ta chọn OK hoàn tất các thao tác. Xuất hiện hộp thoại sau. Ta tiếp túc chọn OK, lúc này ô chứa ngày chủ nhật sẽ có màu nền và màu chữ như hình sau: 4.3.4. Tạo màu nền chấm công Chọn khối từ Cell D5 đến Cell AE16 vào Format / Conditional Formating, như hình sau: Hộp thoại Conditional Formating xuất hiện và ta làm theo hình sau: 1.Điều kiện 1chọn“Cell Value Is” Ở đây chọn equal to 3. Click vào, và nhập công thức =”” (hai cặp nháy kép có ý nghĩa là khoản trống). Như vậy điều kiện condition 1 có ý nghĩa là khác trống Sau đó, chọn Format… để xuất hiện hộp thoại định dạng màu chữ (Font) và màu nền (Patterns), và làm theo hướng dẫn của hình sau. + Ngăn Font chọn màu chữ, như hình sau: 2. Chọn màu chữ. ở đây chon màu hồng + Ngăn Pattens chọn màu nền như hình sau: 2. Chọn màu nền 3. Cho xem trước Hoàn thành các thao tác, ta chọn OK, một hộp thoại xuất hiện Tiếp tục chọn OK, Khi chọn OK thì khối chấm công sẽ có màu chữ màu nền như ta đã định dạng. Nếu ta nhập dữ liệu vào sẽ mất màu nền, như hình sau: 4.3.5. Định dạng ký tự chấm công Khối được chọn Chọn khối từ Cell D5 đến AH16, vào Data / Validation, như hình sau Xuất hiện hộp thoại Data Validation, và làm theo hướng dẫn của hình sau: + Ngăn settings ta thực hiện các thao tác như hình sau ở đây chọn “custom” ở đây ta gõ công thức như sau: =OR(D5= “X”,D5= “X”, D5= “CP”,D5= “KP”) Sau đó ở ngăn Input Message để nhập câu nhắc, như hình sau Nhập câu CHÚ Ý! Câu nhắc “Chỉ được nhập X, O, CP, KP” Đến khung Error Alert nhập câu nhắc khi nhập sai,như hình sau: Nhập chữ nhập sai để nhắc nhỡ Yêu cầu nhập lại Khi OK thì khối từ Cell D5 đến Cell AE16 chỉ được nhập một trong các gia trị X, O, CP, KP. Nêu nhập sai sẽ báo lỗi, như hình sau: Nhập sai Hộp thoại báo sai Hộp thoại nhắc nhỡ 4.3.6. Tính ngày công. Bây giờ ta tiến hành chấm công cho nhân sự của công ty dựa vào số ngày đi làm của họ. Như hình sau. 4.3.6.1. Tính tổng số ngày công Tại cell AI5 ta nhập vào công thức =COUNTIF(D5:AE5, “X”)+COUNTIF(D5:AE5, “O”)/2, minh hoạ như hình sau: Vị trí đặt công thức: Ta copy công thức đến ô AI16. Như hình sau. 4.3.6.2. Tính tổng số ngày nghỉ có phép Vị trí đặt công thức. Tại cell AJ5 ta nhập vào công thức =COUNTIF(D5:AE5, “CP”), minh hoạ bằng hình sau: Ta copy công thức đến ô AJ16. Như hình sau. 4.3.6.3. Tính tổng số ngày nghỉ không có phép Tại cell AK5 ta nhập vào công thức =COUNTIF(D5:AE5, “KP”), như hình sau: Vị trí đặt công thức. Ta copy công thức đến ô AK16. Như hình sau. 4.4. THIẾT LẬP CÔNG THỨC CHO SHEET TỔNG HỢP NGHỈ CÓ PHÉP (THNGHICP) + Tại Sheet Bảng chấm công ta Copy khối từ ô AJ5 đến ô AJ16,như hình sau: Ta copy khối từ ô AJ5 đến ô AJ16. Như hình này Tại Sheet Tổng hợp nghỉ có phép (THNGHIP), đặt con trỏ tại ô E5 nhấp phải chuột chọn Paste Special, như hình sau: Chọn values Sau khi chọn Paste Special hộp thoại sau xuất hiện. Trong hộp thoại sau ta click chọn values, như hình sau: Hoàn tất ta chọn OK, khi chọn OK thì từ cell E5 đến E16 có giá trị như hình sau: + Tính tổng ngày nghỉ có phép. Tại cell P5 ta nhập vào công thức =SUM(D5:O5), như hình sau: Vị trí dặt công thức Ta copy công thức đến ô P16, như hình sau. THIẾT LẬP CÔNG THỨC CHO SHEET LƯƠNG (LUONG) 4.5.1. Thiết lập công thức cho cột thưởng. Tại cell E6 ta lập công thức =IF((BCCONG!AJ5+BCCONG!AK5)=0,10%*LUONG!D6,0),như hình sau: Vị trí đặt công thức Ta copy công thức đến ô E17. Minh họa hình ảnh 4.5.2. Thiết lập công thức cho cột Lương Phụ Trội (LPT) Tại cell F6 ta lập công thức =IF((BCCONG!AJ5-22)>0,(BCCONG!AJ5-22)*2*LUONG!D6/22,0), như hình sau: Vị trí đặt công thức Copy công thức đến ô F17. Như hình sau. 4.5.3. Thiết lập công thức cho cột Phạt. Tại cell G6 ta nhập công thức =BCCONG!AK5*3*LUONG!D6/22+IF((THNGHIP!P5-12)>0, Đặt công thức tại ô G6 (THNGHIP!P5-12)*LUONG!D6/22,0), như hình sau: Ta copy công thức đến ô G17. Như hình sau. 4.5.4. Thiết lập công thức cho cột Thực Lỉnh. Tại cell H6 ta nhập công thức = D6+E6+F6-G6, như hình sau: Công thức được đặt tại vị trí này Ta tiếp tục copy công thức đến ô H17. Như hình sau