copy of soal excel 4.xlsx

30%

5%

22%18%

12%

11%3%

Volume

Anggur Total Apel Total Jeruk Total Mangga Total Nangka Total Pepaya Total Sirsak Total

ROBI FRUITSLAPORAN PENJUALAN BUAH

No. Nama Buah Volume Total Diskon

1 B006 Anggur 78.05 Kg Rp 15,000 Rp 1,170,739 Rp 175,611 2 B006 Anggur 59.54 Kg Rp 15,000 Rp 893,100 Rp 133,965 3 B006 Anggur 73.34 Kg Rp 15,000 Rp 1,100,089 Rp 165,013 4 B006 Anggur 64.36 Kg Rp 15,000 Rp 965,417 Rp 144,813 5 B006 Anggur 76.60 Kg Rp 15,000 Rp 1,149,058 Rp 172,359

Anggur Total 351.89 Kg6 B003 Apel 52.02 Kg Rp 45,000 Rp 2,340,956 Rp 351,143 7 B003 Apel 2.43 Kg Rp 45,000 Rp 109,306 Rp -

Apel Total 54.45 Kg8 B002 Jeruk 64.45 Kg Rp 30,000 Rp 1,933,601 Rp 290,040 9 B002 Jeruk 17.62 Kg Rp 30,000 Rp 528,619 Rp 52,862

10 B002 Jeruk 86.40 Kg Rp 30,000 Rp 2,591,922 Rp 388,788 11 B002 Jeruk 29.97 Kg Rp 30,000 Rp 899,208 Rp 89,921 12 B002 Jeruk 47.83 Kg Rp 30,000 Rp 1,434,956 Rp 143,496 13 B002 Jeruk 7.76 Kg Rp 30,000 Rp 232,724 Rp -

Jeruk Total 254.03 Kg14 B001 Mangga 91.85 Kg Rp 25,000 Rp 2,296,210 Rp 344,432 15 B001 Mangga 43.05 Kg Rp 25,000 Rp 1,076,363 Rp 107,636 16 B001 Mangga 12.02 Kg Rp 25,000 Rp 300,562 Rp 30,056 17 B001 Mangga 63.38 Kg Rp 25,000 Rp 1,584,445 Rp 237,667

Mangga Total 210.30 Kg18 B005 Nangka 94.07 Kg Rp 28,000 Rp 2,633,873 Rp 395,081 19 B005 Nangka 21.83 Kg Rp 28,000 Rp 611,318 Rp 61,132 20 B005 Nangka 26.16 Kg Rp 28,000 Rp 732,377 Rp 73,238

Nangka Total 142.06 Kg21 B007 Pepaya 7.29 Kg Rp 50,000 Rp 364,746 Rp - 22 B007 Pepaya 54.47 Kg Rp 50,000 Rp 2,723,402 Rp 408,510 23 B007 Pepaya 51.83 Kg Rp 50,000 Rp 2,591,334 Rp 388,700 24 B007 Pepaya 10.89 Kg Rp 50,000 Rp 544,477 Rp 54,448

Pepaya Total 124.48 Kg25 B004 Sirsak 41.06 Kg Rp 9,000 Rp 369,499 Rp 36,950

Sirsak Total 41.06 KgGrand Total 1178.27 Kg

Jml Kg Terjual Jml Uang Jml Kg

Mangga 17.84844358474 210.3032181 4537789.65 CashJeruk 21.55990652871 254.0343478 6655923.67 Mangga 134.9029259Apel 4.621197466234 54.45027709 2099119 Jeruk 246.2768728Sirsak 3.48437312861 41.05539391 332548.691 Apel 52.02125039Nangka 12.0563002483 142.0560134 3448117.9 Sirsak 41.05539391Anggur 29.86522544806 351.8935975 4486643.37 Nangka 94.06688612Pepaya 10.56455359534 124.4791799 5372300.85 Anggur 137.5892645Total 100 1178.272028 26932443 Pepaya 0

Total 705.9125937

Tabel Buah NAMA Buah=VLOOKUP(KodeBuah,TabelBuah,2)

KodeBuah

Harga Satuan

Prosentase(Kg)

Kode Harga / KgHARGA SAT=VLOOKUP(KodeBuah,TabelBuah,3)

B001 Mangga 25000 TOTAL VOLUME × HARGAB002 Jeruk 30000 DISKONB003 Apel 45000 Kuantiti DiscountB004 Sirsak 9000 <=10 0B005 Nangka 28000 >10 and <=50 10% × HargaB006 Anggur 15000 > 50 and <=100 15% × HargaB007 Pepaya 50000 PembayaranTOTAL - DISKON

LAMA PENJKondisiKredit DATEDIF(Tgl Transaksi;Tgl Proses;"D")Cash 0

nama buahvolume volumenangka >=25 <=100


11 B005 Nangka 94.07 Kg Rp 28,000 Rp 2,633,873 Rp 395,081 15 B005 Nangka 26.16 Kg Rp 28,000 Rp 732,377 Rp 73,238


11 B005 Nangka 94.07 Kg Rp 28,000 Rp 2,633,873 Rp 395,081 15 B005 Nangka 26.16 Kg Rp 28,000 Rp 732,377 Rp 73,238

Nama Buah

KodeBuah

Harga Satuan

KodeBuah

Harga Satuan

Tgl Proses 9/30/1991

Jumlah Harga

Rp 995,128 Cash 8-Sep-91 0 Rp 759,135 Cash 24-Jul-91 0 Rp 935,076 Credit 14-Aug-91 47 Rp 820,605 Credit 28-Jul-91 64 Rp 976,699 Credit 1-Sep-91 29 Rp 4,486,643 Rp 1,989,813 Cash 7-Aug-91 0 Rp 109,306 Credit 24-Jul-91 68 Rp 2,099,119 Rp 1,643,561 Cash 4-Sep-91 0 Rp 475,757 Cash 16-Jul-91 0 Rp 2,203,134 Cash 11-Aug-91 0 Rp 809,287 Cash 3-Sep-91 0 Rp 1,291,461 Cash 11-Aug-91 0 Rp 232,724 Credit 24-Jul-91 68 Rp 6,655,924 Rp 1,951,779 Cash 25-Sep-91 0 Rp 968,727 Cash 21-Jul-91 0 Rp 270,506 Credit 1-Sep-91 29 Rp 1,346,778 Credit 4-Sep-91 26 Rp 4,537,790 Rp 2,238,792 Cash 30-Aug-91 0 Rp 550,187 Credit 20-Aug-91 41 Rp 659,139 Credit 14-Aug-91 47 Rp 3,448,118 Rp 364,746 Credit 4-Aug-91 57 Rp 2,314,892 Credit 26-Aug-91 35 Rp 2,202,634 Credit 22-Aug-91 39 Rp 490,030 Credit 4-Jul-91 88 Rp 5,372,301 Rp 332,549 Cash 11-Jul-91 0 Rp 332,549 Rp 26,932,443

Jml Kg Jml Uang

Credit Cash Credit75.40029218344 2920505 16172847.757475024027 6423199 232724.252.429026695791 1989813 109306.2

0 332548.69 047.98912728038 2238792 1209326214.304332927 1754263 2732380

124.4791798615 0 5372301472.3594339721 15659121 11273322 1178.272027664

=VLOOKUP(KodeBuah,TabelBuah,2)

Pembayaran

Tgl Penjualan

Lama Penjualan (Hari)

=VLOOKUP(KodeBuah,TabelBuah,3)

DATEDIF(Tgl Transaksi;Tgl Proses;"D")

Jumlah Harga

Rp 2,238,792 Cash 30-Aug-91 0 Rp 659,139 Credit 14-Aug-91 47

Jumlah Harga

Rp 2,238,792 Cash 30-Aug-91 0 Rp 659,139 Credit 14-Aug-91 47

Pembayaran

Tgl Penjualan


Pembayaran

Tgl Penjualan


ROBI FRUITSLAPORAN PENJUALAN BUAH

No. Nama Buah Volume Total Diskon Jumlah Harga

1 B002 Jeruk 7.76 Kg Rp 30,000 Rp 232,724 Rp - Rp 232,724 2 B007 Pepaya 7.29 Kg Rp 50,000 Rp 364,746 Rp - Rp 364,746 3 B002 Jeruk 64.45 Kg Rp 30,000 Rp 1,933,601 Rp 290,040 Rp 1,643,561 4 B002 Jeruk 17.62 Kg Rp 30,000 Rp 528,619 Rp 52,862 Rp 475,757 5 B006 Anggur 73.34 Kg Rp 15,000 Rp 1,100,089 Rp 165,013 Rp 935,076 6 B002 Jeruk 86.40 Kg Rp 30,000 Rp 2,591,922 Rp 388,788 Rp 2,203,134 7 B002 Jeruk 29.97 Kg Rp 30,000 Rp 899,208 Rp 89,921 Rp 809,287 8 B001 Mangga 91.85 Kg Rp 25,000 Rp 2,296,210 Rp 344,432 Rp 1,951,779 9 B006 Anggur 64.36 Kg Rp 15,000 Rp 965,417 Rp 144,813 Rp 820,605

10 B001 Mangga 12.02 Kg Rp 25,000 Rp 300,562 Rp 30,056 Rp 270,506 11 B005 Nangka 94.07 Kg Rp 28,000 Rp 2,633,873 Rp 395,081 Rp 2,238,792 12 B003 Apel 52.02 Kg Rp 45,000 Rp 2,340,956 Rp 351,143 Rp 1,989,813 13 B005 Nangka 21.83 Kg Rp 28,000 Rp 611,318 Rp 61,132 Rp 550,187 14 B004 Sirsak 41.06 Kg Rp 9,000 Rp 369,499 Rp 36,950 Rp 332,549 15 B005 Nangka 26.16 Kg Rp 28,000 Rp 732,377 Rp 73,238 Rp 659,139 16 B003 Apel 2.43 Kg Rp 45,000 Rp 109,306 Rp - Rp 109,306 17 B006 Anggur 76.60 Kg Rp 15,000 Rp 1,149,058 Rp 172,359 Rp 976,699 18 B002 Jeruk 47.83 Kg Rp 30,000 Rp 1,434,956 Rp 143,496 Rp 1,291,461 19 B006 Anggur 78.05 Kg Rp 15,000 Rp 1,170,739 Rp 175,611 Rp 995,128 20 B001 Mangga 43.05 Kg Rp 25,000 Rp 1,076,363 Rp 107,636 Rp 968,727 21 B006 Anggur 59.54 Kg Rp 15,000 Rp 893,100 Rp 133,965 Rp 759,135 22 B001 Mangga 63.38 Kg Rp 25,000 Rp 1,584,445 Rp 237,667 Rp 1,346,778 23 B007 Pepaya 54.47 Kg Rp 50,000 Rp 2,723,402 Rp 408,510 Rp 2,314,892 24 B007 Pepaya 51.83 Kg Rp 50,000 Rp 2,591,334 Rp 388,700 Rp 2,202,634 25 B007 Pepaya 10.89 Kg Rp 50,000 Rp 544,477 Rp 54,448 Rp 490,030

Total 26,932,443

Jml Kg TerjualJml Uang Jml Kg

Mangga 17.8484436 210.30322 4537789.65 Cash CreditJeruk 21.5599065 254.03435 6655923.67 Mangga 134.9029259 75.400292183Apel 4.62119747 54.450277 2099119 Jeruk 246.2768728 7.757475024Sirsak 3.48437313 41.055394 332548.691 Apel 52.02125039 2.4290266958Nangka 12.0563002 142.05601 3448117.9 Sirsak 41.05539391 0Anggur 29.8652254 351.8936 4486643.37 Nangka 94.06688612 47.98912728Pepaya 10.5645536 124.47918 5372300.85 Anggur 137.5892645 214.30433293Total 100 1178.272 26932443 Pepaya 0 124.47917986

Total 705.9125937 472.35943397

Tabel Buah NAMA Buah=VLOOKUP(KodeBuah,TabelBuah,2)

Kode Harga / KgHARGA SAT=VLOOKUP(KodeBuah,TabelBuah,3)

B001 Mangga 25000 TOTAL VOLUME × HARGAB002 Jeruk 30000 DISKONB003 Apel 45000 Kuantiti DiscountB004 Sirsak 9000 <=10 0B005 Nangka 28000 >10 and <=50 10% × HargaB006 Anggur 15000 > 50 and <=10015% × Harga

KodeBuah

Harga Satuan

Prosentase(Kg)

Nama Buah

B007 Pepaya 50000 PembayaranTOTAL - DISKONLAMA PENJKondisi

Kredit DATEDIF(Tgl Transaksi;Tgl Proses;"D")Cash 0

nama buahvolume volumenangka >=25 <=100


11 B005 Nangka 94.07 Kg Rp 28,000 Rp 2,633,873 Rp 395,081 Rp 2,238,792 15 B005 Nangka 26.16 Kg Rp 28,000 Rp 732,377 Rp 73,238 Rp 659,139


11 B005 Nangka 94.07 Kg Rp 28,000 Rp 2,633,873 Rp 395,081 Rp 2,238,792 15 B005 Nangka 26.16 Kg Rp 28,000 Rp 732,377 Rp 73,238 Rp 659,139

KodeBuah

Harga Satuan

KodeBuah

Harga Satuan

Tgl Proses 9/30/1991

Credit 24-Jul-91 68Credit 4-Aug-91 57Cash 4-Sep-91 0Cash 16-Jul-91 0Credit 14-Aug-91 47Cash 11-Aug-91 0Cash 3-Sep-91 0Cash 25-Sep-91 0Credit 28-Jul-91 64Credit 1-Sep-91 29Cash 30-Aug-91 0Cash 7-Aug-91 0Credit 20-Aug-91 41Cash 11-Jul-91 0Credit 14-Aug-91 47Credit 24-Jul-91 68Credit 1-Sep-91 29Cash 11-Aug-91 0Cash 8-Sep-91 0Cash 21-Jul-91 0Cash 24-Jul-91 0Credit 4-Sep-91 26Credit 26-Aug-91 35Credit 22-Aug-91 39Credit 4-Jul-91 88

Jml Uang

Cash Credit2920505 16172846423199 232724.251989813 109306.2

332548.69 02238792 12093261754263 2732380

0 537230115659121 11273322 1178.272027664

Pembayaran

Tgl Penjualan


DATEDIF(Tgl Transaksi;Tgl Proses;"D")

Cash 30-Aug-91 0Credit 14-Aug-91 47

Cash 30-Aug-91 0Credit 14-Aug-91 47

Pembayaran

Tgl Penjualan


Pembayaran

Tgl Penjualan


copy of soal excel 4.xlsx

Documents