Tracking Layaway Payments

[Maurice]

I need help seting up a spreadsheet(s) to track Layaway payments for my store.

I would like to record information about the customer and the order of
course. Also, some customers may have several orders in layaway.

I also need to track deposit, payments with the date, amount owed, date
contract started, date order delivered and picked up, etc.

I also eventually would like to track things like length of payment period,
weekly, monthly, yearly totals, etc.

Could someone please help me to layout a spreadsheet to do all that. I have
tried unsuccesfully to set up several permutations, but nothing seems to work
very well.

Thanks in advance

 

[RyanR]

Given Excel is not a dedicated accounting package, that has 'accounts'
features, and it's not a database, you're not really going to the most
efficient result using excel for this type of thing....

My approach would be a '2-column' per order type setup e.g.

A B
Order# 123abc
Customer J. Doe
Start Date 1/1/01
Total Purchase $1000
Amount Owing $800 (Formulated)
Delivery Date 1/12/01
Picked Up
Payments Details
Date Amount
1/1/01 $100
1/2/01 $100

This allows you to keep track of your orders at a glance at the top, and
still have a 'limitless' number of rows to record payments.

The amount owing field would simply be a sum of the payment amounts, e.g.
=B4-SUM(B$10:B$50).... or to prevent potential errors, you could use sumif
etc.... but that's up to you.. (I use absolute references on the rows but not
columns so you can easily copy the header rows across for your next order)

This is my 2 cents worth on a "quick and simple" way to achieve what you
want.... the next step would be to design your own excel 'database' setup,
using macros etc.