Data Files samples / Shop
Larkspur Corner Store
Products, the till and its receipts, VAT, stock, supplier orders, deliveries, loyalty customers and running costs for a neighbourhood shop.
Overview
Larkspur Corner Store is an invented neighbourhood shop open from seven in the morning to nine at night, every day of the week. It sells bread, milk, fruit and vegetables, drinks, snacks, groceries, household goods and newspapers, from about forty lines bought from six suppliers.
The sample holds a year of trading made up a day at a time. The till rings up a sale every few minutes, more at lunchtime and after school and more on Saturdays, and every sale has a numbered receipt. The evening before each supplier's delivery day the owner sends it an order for what has run low, so stock goes down between deliveries and back up again; now and then a supplier is short of a line. At each month end the rent, wages, power and card fees are paid, so the takings can be set against what the shop costs to run.
Prices on the shelf include VAT, as a shop's do. Most food is zero-rated, while soft drinks, alcohol, sweets, crisps, household goods and greeting cards carry the standard rate of twenty percent, and stamps and phone top-ups carry none. Each product has its VAT code, each receipt shows the VAT within its total, and a query adds up the VAT on the month's sales for the VAT return.
Use it to see how a till, stock and suppliers fit together in one database, or start from it empty for a shop of your own.
In Rutba Data Files, choose Shop on the start screen, then With sample records to look around, or Empty for the same design with nothing in it.
It suits, with small changes
- A convenience store or minimarket
- A farm shop
- A newsagent
- A gift shop
- A pharmacy counter for the goods it sells off the shelf
- A market stall
Read on
- Tutorial: the everyday tasks: 6 everyday tasks, step by step, each naming what to open.
- Tables, fields and relationships: every field of 14 tables, and how they are related.
- Queries, forms, reports and print templates: what each one shows, its criteria and calculated columns, and when to print it.
Things you should know
- Everything in a sample's records is invented: the business, the people, their addresses, e-mail addresses on example domains that reach nobody, and phone numbers from the ranges kept for drama and fiction. Tax, company and bank numbers are placeholders of zeros.
- The dates count back from the day the file was made, and some run on into the weeks after it, as real bookings and due dates do. Made from the Data Files start screen, a sample is dated from that day. Queries that compare with today (overdue, due this week) show more as a downloaded file gets older.
- To start empty, choose the same business on the start screen and then Empty: the same tables, queries, forms, reports and print templates, with nothing in them but the Start Here guide.
- Your records live in one file on your computer. Keep copies: File, Save As makes one, and a copy on another drive or in your own cloud storage keeps it safe if this computer is lost. Close the database before copying the file by hand.
- Every change to the design (a new field, a renamed table) is a numbered step that Data Files keeps, so you can see what was changed and when.
- Prices in Products and Sale Lines include VAT, as the shelf labels do. Cost Price and Unit Cost on orders and deliveries are before VAT, as suppliers invoice them. Profit by category takes the VAT off the takings before it takes off the cost.
- A sale's Total, VAT, Standard Rated and Saved are what the till worked out and printed when the sale was made, each line's VAT rounded to the penny and then added up. They are stored, not worked out again, so the receipt reprints exactly as it was; the query Receipt totals works them out from the lines when you enter a sale by hand.
- Only one VAT rate other than nought is used: twenty percent. The receipt's VAT summary and the VAT queries count everything with a rate above nought as standard rated. If you sell goods at a reduced rate, see the ways to adapt it.
- In Stock goes down as the till sells and up as deliveries come in, in the sample's records. Entering a sale or a delivery by hand does not change it by itself: change In Stock in the Products form, or count it at a stock take.
- The print templates print every record of their table unless you choose one or a few: print a receipt from the Sales form for the sale on screen, and an order from the Supplier Orders form.
- Refunded sales stay in the Sales table with their receipt numbers, so no number is missing; they are left out of the takings, the VAT and the best sellers.
Starting with your own records
Start with Business, the shop's own name, address and VAT number and the returns policy in Document Footer, which every receipt prints. Then Categories and Suppliers, then your Products with their VAT code, tax rate, prices and reorder levels, then your Staff. Sales, supplier orders and deliveries come in as you trade. Put your running costs in Expenses as you pay them.
Making it yours
Every change to the design is a numbered step in Data Files, so a change can be seen and undone.
- Sell by weight: add a Unit field to Products (each, kg, 100 g) and make Quantity in Sale Lines a Number rather than a whole number.
- Track sell-by dates: add a Best Before field to Delivery Lines and a query for lines within three days of it.
- Sell at a reduced VAT rate as well: add a Reduced Rated field to Sales beside Standard Rated, and lines to the receipt's totals and the VAT query for it.
- Keep more than one till or shop: add a Tills table, point Sales at it, and group the takings queries by till.
- Reward points differently: change Points to count one for every two spent, or add a Points Redeemed field to Sales.
- Record the VAT you pay: add a VAT field to Deliveries for the VAT on each supplier invoice, and a query that sets it against VAT on sales by month.
Questions people ask
- How do I reprint a customer's receipt?
- Find the sale in the Sales form, by its receipt number or by the date and time, and press Print with the print template Sale receipt. It prints from the stored totals, so it reads as the first one did.
- Where do I see how much VAT I owe?
- Open the report VAT on sales: the VAT column is the VAT within your takings each month. Add up the months of your VAT quarter and take off the VAT on your suppliers' invoices, which this database does not keep.
- Why is there no VAT on bread or milk?
- Most food is zero-rated, so its Tax Rate is 0 and its VAT Code is Z. Soft drinks, alcohol, sweets, crisps and household goods carry the standard rate. Check the rules for your own country and set each product's VAT Code and Tax Rate to match.
- How does the shop know what to order?
- Each product has a Reorder Level. The query Low stock and the report Shopping list show everything at or below it, by supplier, and the supplier order is made from that list.
- How are loyalty points worked out?
- One point for each whole pound spent on a sale, stored in the sale's Points Earned and added to the customer's Points. A refunded sale earns none.
- A supplier did not bring everything. How do I show that?
- Enter only what came in the delivery's lines, and set the order's Status to Part delivered. The lines left out are still on the order, so you can see what to chase.
- Can I start with my own products and none of the sample's sales?
- Yes. Open the sample empty, fill in Business first, then Categories, Suppliers and Products, then Staff. Sales, orders and deliveries come in as you trade.
The other samples
- Food and beverages
- Cafe or restaurant
- Takeaway
- Florist
- Pharmacy counter
- Wholesale and trading
- Workshop or maker
- Builder
- Used car dealer
- Salon
- Barber shop
- Repair shop
- Garage and MOT centre
- Cleaning company
- Consultancy or agency
- Gym and fitness studio
- Clinic
- School or training centre
- Charity or club
- Property rentals
- Estate and letting agent
- Guest house or B and B
- Building front desk
- HR, payroll and timesheets