Membuat laporan hutang piutang dan stok di Excel

Last updated on March 19th, 2021 at 06:51 am

ganti ke bahasa inggris

Contoh kasus:
Pak Adi mempunyai toko kecil, UD. MAJU JAYA. Selama ini dia mencatat di buku mengenai hutang-piutang dan stok tokonya tsb. Seiring dengan usahanya yang makin berkembang, ada tiga hal yang menyebabkan pak Adi ingin membuat laporan hutang piutang dan stok di Excel.

Yang pertama, dia tidak usah lagi menghitung secara manual pakai kalkulator. Kedua, dia bisa melihat laporan hutang piutang dan stok toko di aplikasi Excel secara cepat. Dan yang ketiga, dia bisa mencetak nota/invoice saat terjadi penjualan/pembelian.

Akhirnya pak Adi membeli sebuah komputer, dan pak Adi menyiapkan segala data data yang dia perlukan dalam membuat laporan hutang piutang dan stok di aplikasi Microsoft Excel.

Menyiapkan data:

  • customer/supplier (PD/HD)
  • barang / item yang diperjual-beli-kan
  • jumlah barang barang yang dijual (stok awal)
  • PD/HD yang masih ada hutang piutang-nya (PDHD awal)

Setelah point point diatas sudah disiapkan, Pak Adi mulai melakukan persiapan di file Excel.

1. Membuat daftar Customer, Supplier dan Item.

Menamakan halaman : double-click halaman “Sheet1”, lalu ganti namanya menjadi “TABEL”. Double-click halaman “Sheet2”, lalu ganti namanya menjadi “DATA”.

menamakan halaman untuk persiapan membuat laporan hutang piutang di excel
Menamakan halaman di Excel

Dengan membuat halaman TABEL lalu membuat daftar nama nama Customer, Supplier dan Item, ini akan memudahkan peng-inputan data karena daftar nama nama nya sudah tersedia.

Daftar nama customer, supplier dan produk.
Contoh daftar nama nama piutang-hutang dagang dan item

2. Membuat tabel transaksi

header tabel data untuk laporan hutang piutang di Excel
Header tabel transaksi penjualan pembelian dan stok

Selanjutnya pak Adi membuat table transaksi di halaman “DATA”. Adapun header tabel yang pak Adi buat adalah sbb:

  • TGL = tanggal terjadi-nya transaksi jual, beli dan bayar
  • NOTA = nomor invoice (nota/kwitansi)
  • NAMA = nama PiutangDagang / nama HutangDagang
  • BUKU = stok / pembayaran
  • POS = PD / HD / Stok Awal
  • ITEM = nama item
  • QT = kwantitas item
  • HARGA = harga item
  • SUM = perkalian QT * HARGA (plus/minus)
  • QTY = kwantitas item (plus/minus)
  • D/K = Debet / Kredit
  • BlnThn = kategori tahun dan bulan apa
  • LUNAS = di isi apabila nota sudah lunas

3. Mengisi halaman DATA dengan stok awal

saldo awal item untuk laporan stok
Contoh stok awal

4. Mengisi halaman DATA dengan saldo awal hutang-piutang dagang

Contoh saldo awal untuk laporan hutang piutang di Excel
Contoh saldo awal piutang dagang
contoh saldo awal hutang dagang non lunas
Contoh saldo awal hutang dagang

KETERANGAN:

  • Kolom NOTA, format nomor nota-nya berupa :
    • Karakter pertama adalah huruf P ataupun H. Ini berguna sebagai tanda bahwa nomor terkait adalah nota Piutang Dagang (PD) ataukah nota Hutang Dagang (HD).
    • Karakter kedua bebas bebas saja mau di isi huruf apa, tetapi harus selalu tetap sama.
    • Enam digit pertama setelah tanda “-” adalah format waktu berupa YYMMDD (Tahun 2 digit, Bulan 2 digit, Tanggal 2 digit).
    • Tiga digit terakhir adalah nomor nota PD yang keluar / dicetak pada tanggal terkait.
  • Untuk kolom SUM dan QTY nilainya minus untuk transaksi penjualan, nilainya plus untuk transaksi pembelian.
  • Sedangkan kolom D/K berisi text “kredit” ataupun “debet”
  • Dan terakhir, kolom BlnThn selalu tanggal 1, namun bulan dan tahun-nya seturut yang terdapat di kolom TGL.

5. Membuat Dynamic Named Range

Agar cakupan baris di daftarnya bertambah/berkurang secara otomatis/dinamis, berikut cara-nya: pada tab menu Formulas —> klik Name Manager.

Pada tab menu Formulas, klik Name Manager
Di window Name Manager, klik New

Untuk Scope dan Comment biarkan begitu saja, sedangkan untuk Name: dan Refers to: nya isi (copy – paste) seperti dibawah ini :

Name : Customer
Refers to : =OFFSET(TABEL!$B$2,0,0,COUNTA(TABEL!$B$2:$B$10000),1)
Klik OK – klik New

Name : data
Refers to : =OFFSET(DATA!$B$1,0,0,COUNTA(DATA!$B:$B),13)
Klik OK – klik New

Name : Item
Refers to : =OFFSET(TABEL!$F$2,0,0,COUNTA(TABEL!$F$2:$F$10000),1)
Klik OK – klik New

Name : Supplier
Refers to : =OFFSET(TABEL!$D$2,0,0,COUNTA(TABEL!$D$2:$D$10000),1)
Klik OK – klik Close

Buat Named Range-nya satu per satu. Setelah membuat empat Named Range, hasilnya seperti terlihat pada gambar dibawah ini.

named range customer supplier item di Name Manager untuk persiapan dalam membuat laporan hutang piutang di Excel
Contoh dynamic named range di Name Manager Excel

Demikianlah 5 langkah awal pak Adi membuat laporan hutang piutang dan stok di Excel. Berikutnya pak Adi akan membuat salah satu fitur aplikasi Excel, yakni Pivot Table. Pivot Table ini akan dia buat pada halaman yang dia namakan “PDHD”. Gunanya adalah untuk melihat daftar hutang piutang usaha-nya.

Apabila ada pembaca yang tidak jelas ataupun ada yang ingin ditanyakan terkait topik ini, silahkan tulis di kolom Comment. Saya akan berusaha untuk bisa menjawabnya.

285 replies on “Membuat laporan hutang piutang dan stok di Excel”

dolorem non recusandae sed et quisquam consectetur modi voluptate incidunt consequuntur quia sint id neque veniam illo in. voluptatem ut repellendus consectetur quos omnis est deleniti aspernatur voluptas ut ut saepe omnis dolorum qui quasi. qui neque itaque repellendus. vero et blanditiis optio commodi iste sit.

Thanks for every other informative blog. The place else could I getthat kind of information written in such a perfect means?I’ve a challenge that I’m just now working on, and I’ve been at the look outfor such info.

九州娛樂城與Leo娛樂城即將結束運營，玩家該如何應對？近期娛樂城行業的消息引發了玩家的廣泛關注，其中「Leo娛樂城無法登入」和「九州娛樂城即將倒閉」成為熱門話題。根據官方公告，九州娛樂城及其旗下品牌，包括Leo娛樂城和THA娛樂城，將於2024年12月31日中午12點正式結束營業並關閉官網。這一消息不僅讓玩家感到震驚，也暴露出行業潛在的問題。 Leo娛樂城無法登入的原因Leo娛樂城近期頻繁出現登入問題，這與其即將退出台灣市場的計劃息息相關。以下是造成登入困難的主要原因： 帳號或密碼問題許多玩家因忘記密碼或帳號被盜導致登入失敗。然而，由於官方客服系統已停止服務，這些問題無法得到解決。網頁技術問題官網伺服器的不穩定或維護期間，可能出現「404錯誤」，讓玩家無法正常登入。營運關閉計劃隨著九州娛樂城宣布結束營業，Leo娛樂城的伺服器和備用網址也逐步停止運行，這是玩家無法登入的最大原因。為何九州娛樂城退出台灣市場？九州娛樂城決定退出台灣市場的原因可以歸納為以下幾點： 市場收益不足台灣市場對九州娛樂城而言並不具備高盈利性，難以支撐長期運營。第三方支付系統受阻多次傳出第三方現金支付渠道中斷，影響存提款操作。法律風險增加九州娛樂城頻繁遭遇法律問題及負面新聞，導致營運壓力倍增。玩家需要注意什麼？隨著九州娛樂城及其旗下品牌逐步退出市場，玩家應及時採取以下措施： 提領餘額官方已停止存款服務，但提領功能仍開放。玩家應儘快提領餘額，避免損失。轉移到穩定平台針對Leo娛樂城和九州娛樂城的退出，玩家可選擇更穩定且具信譽的平台，以保障自己的娛樂體驗。防範詐騙小心冒名客服或假網站，官方已停止客服系統，不會主動聯繫玩家索取個人資料。未來哪些娛樂城值得信賴？隨著九州娛樂城退出市場，玩家可以關注一些以穩定性和合法性著稱的娛樂平台。選擇時應注意以下幾點： 合法牌照選擇擁有合法經營許可的娛樂平台。穩定的支付系統平台應提供多種穩定且快速的支付方式。用戶口碑瀏覽玩家的評價和反饋，選擇高評價的平台。結論九州娛樂城及其旗下品牌的關閉標誌著一個時代的結束，同時也提醒玩家關注行業的合規性和穩定性。在此轉型時期，玩家應保持警惕，選擇可靠的平台，以確保自己的資金和遊戲體驗不受影響。

Hi, I do believe this is an excellent blog. I stumbledupon it 😉 I am going to return yet again since I bookmarked it. Money and freedom is the greatest way to change, may you be rich and continue to help others.

When I initially commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get three emails with the same comment. Is there any way you can remove people from that service? Thanks!

Hey There. I found your blog using msn. This isan extremely well written article. I’ll be sure to bookmark it and come back to read more of your useful information. Thanks for the post.I’ll certainly comeback.

Hello there! Do you know if they make any plugins to assist with Search Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good success. If you know of any please share. Thanks!

Greetings! I know this is kinda off topic but I waswondering if you knew where I could locate a captcha plugin formy comment form? I’m using the same blog platform as yours and I’m having difficulty finding one?Thanks a lot!

An intriguing discussion is worth comment. I do think that you ought to publish more on this issue, it may not be a taboo subject but typically folks don’t discuss such subjects. To the next! Many thanks!!

Hey There. I found your blog using msn. This is an extremely well written article. I will make sure to bookmark it and return to read more of your useful information. Thanks for the post. I will certainly comeback.

I discovered your blog site on google and check a few of your early posts. Continue to keep up the very good operate. I just additional up your RSS feed to my MSN News Reader. Seeking forward to reading more from you later on!…

I’m truly enjoying the design and layout of your blog. It’s a very easy on the eyes which makes it much more enjoyable for me to come here and visit more often. Did you hire out a designer to create your theme? Excellent work!

Hi! This post couldn’t be written any better! Reading this post reminds me ofmy previous room mate! He always kept chatting about this.I will forward this article to him. Pretty sure he willhave a good read. Thank you for sharing!

Pretty nice post. I just stumbled upon your blog and wished to say that I’ve really enjoyed browsing your blog posts.After all I will be subscribing to your feed and I hope you writeagain soon!

Hello There. I found your blog the use of msn. This is a really neatly written article. I’ll be sure to bookmark it and return to learn more of your helpful information. Thank you for the post. I will certainly comeback.

Im not sure where you are getting your information, but great topic. I needs to spend some time learning much more or understanding more. Thanks for excellent info I was looking for this info for my mission.

I have to thank you for the efforts you have put in writing this blog. I am hoping to view the same high-grade content by you later on as well. In fact, your creative writing abilities has motivated me to get my own blog now 😉

I don’t even know the way I finished up right here, however I thought this submit was great.I don’t recognise who you are but certainly you’re goingto a famous blogger for those who aren’t already. Cheers!Also visit my blog :: kebe.top

at web, except I know I am getting familiarity all the time by reading thes pleasant posts.|Fantastic post. I will also be handling some of these problems.|Hello, I think this is a great blog. I happened onto it;) I have bookmarked it and will check it out again. The best way to change is via wealth and independence. May you prosper and never stop mentoring others.|I was overjoyed to find this website. I must express my gratitude for your time because this was an amazing read! I thoroughly enjoyed reading it, and I’ve bookmarked your blog so I can check out fresh content in the future.|Hi there! If I shared your blog with my Facebook group, would that be okay? I believe there are a lot of people who would truly value your article.|منشور رائع. سأتعامل مع بعض هذه|

It provides great, practical tips that are easy to implement, making it an excellent resource for both beginners and experts. The examples used are very effective in clarifying each point, and the way the content is broken down makes it so much easier to digest. The layout is also clean and easy to navigate, which makes for a pleasant reading experience.

Hi, I do think this is an excellent blog. I stumbledupon it 😉 I may revisit onceagain since i have saved as a favorite it. Money and freedom is the best way to change, may you be rich and continue to guide others.

Thank you for some other magnificent article. The place else could anybody get that kind of information in such a perfect method of writing? I’ve a presentation subsequent week, and I am on the search for such information.

Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?

Nice post. I learn something totally new and challenging on sites I stumbleupon every day.

It will always be interesting to read articles
from other authors and practice something from other web sites.

Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?

The brutal reality of war comes to the forefront as the districts unite to overthrow President Snow. Katniss Everdeen finds herself as the reluctant face of the uprising, filming propos to inspire the rebels. If you are eager to read this gripping conclusion, a Mockingjay PDF offers a seamless way to dive into the story immediately. Digital formats are excellent for students and book clubs who need to reference specific chapters quickly. The psychological depth of the characters is explored further in this volume, making it a crucial read. Experience the finale that defines a generation of readers.

Finally, the sheer entertainment value of this book cannot be overstated. It has everything: magic, romance, action, mystery, and humor. A Queen of Shadows pdf is a ticket to hours of escapism. It transports the reader to a world that is vibrant and dangerous. It is the kind of book that reminds you why you fell in love with reading in the first place. It is a blockbuster movie in written form, waiting to be devoured.

The relationship between Aelin and Lysandra is tested but ultimately strengthened. They are two sides of the same coin, both wearing masks to survive. Their sisterhood is a pillar of the story. Witness their bond in the kingdom of ash pdf. It is refreshing to see a fantasy series that prioritizes female friendship so highly. They rely on each other to lead, to fight, and to govern.

The Road is a novel of shadows. The gray light hides the dangers. The father and son must navigate the darkness. McCarthy’s atmosphere is unmatched. This book is a gripping read. Many readers look for the pdf version to keep on their devices. It is a story that pulls you in.

Morrison’s use of color, particularly the lack thereof followed by an explosion of it, is significant. To track the color imagery, a Beloved PDF is extremely helpful. From the grey and white of the house to the colors Baby Suggs contemplates on her deathbed, visual descriptions carry thematic weight. A digital search for color terms within the ebook can reveal patterns about life, vitality, and the numbness of grief that might be missed during a casual reading of the physical book.

Aelin’s wardrobe and her love for finery, even in the midst of war, is a fun character detail that remains consistent. It shows that she refuses to sacrifice her femininity to be a warrior. Descriptions of her attire in the Queen of Shadows pdf add color and texture to the narrative. It is a subtle rebellion against the idea that a strong female character must be devoid of traditional feminine traits. She fights in exquisite gowns and terrifying armor alike, making her an icon of multifaceted strength.

The intricate legal maneuverings regarding the property in the novel are often overlooked. A Wuthering Heights PDF helps you understand how Heathcliff legally steals the estate. It is a plot point that turns on the laws of the time. The digital text allows you to slow down and parse the details of the inheritance. It adds a layer of realism to the gothic drama. The ebook format is perfect for this kind of close reading, where every detail matters to the overall understanding of the villain’s master plan.

The plot of this book is a masterclass in pacing and character development. You will watch Liz grow from a fantasy-obsessed teenager into a young woman who appreciates real connection. Finding a Better Than the Movies PDF is a simple step toward enjoying this transformative story. It is a narrative that stays with you long after the final page, reminding you that life doesn’t need a script to be beautiful.

There is a tactile pleasure in reading a physical book, but a Wuthering Heights PDF offers the pleasure of immediacy. You can start reading within seconds of deciding to do so. The barrier to entry is removed. You do not need to wait for shipping or go to a bookstore. The moors are just a click away. This convenience is what keeps classic literature alive in the digital age. It ensures that the story continues to be read and loved by new generations who live their lives on screens.

The psychological depth of the characters in this novel was ahead of its time. To explore the mental landscapes of the Earnshaws and Lintons, download a Wuthering Heights PDF. The book delves into themes of abuse, trauma, and obsession with a frankness that is startling. Reading digitally allows you to focus on the character development without distractions. You can trace how childhood trauma shapes Heathcliff’s adult cruelty, providing a psychoanalytic reading of the text that is facilitated by the convenience of a searchable, portable ebook format.

The richness of the minor characters, from the local sheriff to the auto mechanic, creates a living, breathing world. No character is wasted. Steinbeck is a master of the vignette. Aspiring writers can learn from his economy of characterization. An East of Eden PDF helps you study these minor roles. Our site highlights the supporting cast, showing how they contribute to the main themes. We appreciate the depth of the world-building in the novel.

Literary critics and fans agree that this novel is a masterpiece of structure and wit. The parallel love stories of Jane and Bingley versus Elizabeth and Darcy offer a fascinating contrast. To study these dynamics, having a search-friendly text is helpful. That is why many scholars look for a Pride and Prejudice PDF. Our platform is built around the appreciation of this book, offering resources for deep reading. whether you are dissecting the text for a class or enjoying it on a rainy afternoon, the digital format provides flexibility. Experience the genius of Austen in a way that fits your life.

The chemistry in this novel is palpable, making it impossible to put down once you start. Liz and Wes go from enemies to co-conspirators to soulmates in a way that feels earned and organic. A Better Than the Movies PDF is often sought after by readers who want to experience this emotional rollercoaster immediately. It is a book that celebrates the power of music, the importance of family, and the unpredictability of love.

For those who love to annotate their books but hate damaging physical copies, a Wuthering Heights PDF is the perfect solution. You can highlight the beautiful descriptions of the landscape and the fierce declarations of love without making a mark on paper. This is particularly helpful for students writing term papers on Emily Brontë’s work. You can organize your notes and quotes digitally, streamlining your study process. The novel is rich with metaphors and recurring motifs that are easier to track and analyze when working with a digital file.

There is a timeless quality to the struggle between good and evil depicted in this novel, making it relevant to every generation. The choices the characters make have ripple effects that last for decades. For modern readers accustomed to digital consumption, finding an East of Eden PDF is often the most convenient way to access this timeless wisdom. We offer a modern perspective on these classic themes, ensuring that the heavy topics discussed in the book are approachable. Our guides and articles are perfect for accompanying your reading, helping to clarify the complex web of relationships Steinbeck created.

This book is a delightful romp through the ups and downs of high school romance. Lynn Painter has a gift for writing dialogue that sounds authentic and incredibly funny. For those who prefer screen reading, finding a Better Than the Movies PDF is the key to unlocking this gem. The story of Liz and Wes is a reminder that sometimes the person you are looking for has been right there all along, waiting for the script to change.

Steinbeck’s portrayal of women in the novel, particularly Cathy and Abra, offers a stark contrast between destruction and healing. Abra becomes the symbol of a new, unburdened future. Gender studies students often critique these representations. An East of Eden PDF is essential for close reading and citation in academic papers. Our site provides a balanced analysis of the female characters, discussing their agency and roles within the patriarchal society of the time. We support critical engagement with the text, offering diverse perspectives on Steinbeck’s characterizations.

There is a joy in the predictability of the classic romance structure. You know they will get together, but the journey is the fun part. To enjoy the ride, read the book. A Pride and Prejudice PDF delivers the expected pleasure. Our platform is dedicated to the satisfaction of the genre. We ensure that you get the happy ending you crave.

Understanding the historical context of the Victorian era enhances the reading experience of this novel. When you download a Wuthering Heights PDF, you are engaging with a text that defied the conventions of its time. Critics were originally shocked by the raw emotion and perceived savagery of the book. Today, it is celebrated for those exact qualities. A digital copy allows you to easily reference footnotes or look up archaic terms, making the rich language more accessible to contemporary readers. Dive into the history and the passion that makes this book a must-read.

Jane Austen’s ability to create distinct voices for her characters is unmatched. You can tell who is speaking just by the style of the dialogue. To appreciate this skill, the written text is required. A Pride and Prejudice PDF is a great way to study these character voices. Our platform is dedicated to the literary excellence of the book. We provide a way for readers to access the novel and enjoy the masterful characterization. Listen to the chorus of voices that make up this classic.

For a story that feels like sunshine, look no further. Jenny Han delivers the ultimate summer vibe. Finding the PDF is easy for tech-savvy readers. The Summer I Turned Pretty is about the joy of being young and free. But it also acknowledges the shadows that lurk at the edges of the light. It is a story with depth and nuance. It is a perfect beach companion.

There is nothing quite like the anticipation of a summer at the beach. For Belly, it is the highlight of her existence. Jenny Han captures that specific feeling of waiting for life to begin. Whether you read the physical book or find a PDF, the story remains just as powerful. “The Summer I Turned Pretty” deals with the realization that parents are human and that crushes can break your heart. The Fisher boys represent two very different paths for Belly. It is a compelling read from the very first page to the last.

Tool proficiencies for “Gaming Sets” allow you to gamble and cheat intelligently. Xanathar’s Guide to Everything gives rules for winning gold at the tavern. Players looking for a PDF on gambling rules within D&D will enjoy our minigame collection. We explain how to use your proficiency bonus to stack the deck in your favor. Turn a tavern brawl into a high-stakes poker game with these expanded tool rules.

The “Kings of Sin” series promises seven books, but the first one remains special. It sets the world and the tone. Dante and Vivian are the original King and Queen. If you are looking for a King of Wrath PDF, you are starting at the source. It is the foundation upon which the rest of the series is built. It remains a fan favorite for its intensity and heart.

The darkness is coming, but so is the light of the lightning wielder. The Onyx Storm epub is a beacon for fantasy lovers. This sequel is everything you hoped for and more. E-books allow you to customize your reading experience for maximum comfort. Dive into the politics and the passion of the Empyrean. The storm is here, and it is magnificent.

If you are looking for the perfect blend of romance and fantasy action, look no further. The Onyx Storm epub delivers everything fans loved about the first two books and more. Violet is stronger, the dragons are fiercer, and the villains are more terrifying. E-books allow you to carry this epic tale in your pocket. Whether you are waiting for a bus or relaxing at home, the story is always with you. Rebecca Yarros has created a world you won’t want to leave.

Prepare to fly back into the danger zone with the latest release from Rebecca Yarros, a book that promises to shatter hearts and rebuild them. As the war intensifies, the need for a reliable digital version of Onyx Storm becomes clear for voracious readers. E-readers allow fans to adjust fonts and lighting for those late-night marathons that this genre demands. The search for the epub version highlights just how eager the community is to consume this story. From the chemistry between the protagonists to the political intrigue of the dragon riders, this book is set to be a defining moment in modern fantasy literature.

Leave a Reply

Your email address will not be published. Required fields are marked *