An invoice created with Numbers

20 02 2014

Apple made the iWork suite for free when buying a new iOS device to improve the user experience by providing all the basic features users expect. The iWork Suite for iOS consists of

  • Keynote
    the presentation tool for creating slides and present them to an audience
  • Pages
    the text editor to write letters and more
  • Numbers
    the spreadsheet application for calculations, stats, and other stuff for which a table is the appropriate approach

This post is about an application using Numbers for creating an invoice.

Two versions …

There is a simple approach with a template showing all products in a table. The invoice then is created by deleting unwanted rows and adjusting the quantities.

Another version is more complex and allows to refer to an additional sheet where products are listed with their prices. It uses the embedded function ‘Lookup’ to retrieve the unit price from a sheet where all products are listed.

Both versions work with a control called Stepper to adjust the desired quantity for each item (see image below). I used Apple’s template ‘Invoice’ which is available after the installation of Numbers.

The simple version …

The kernel of this version is a table where all products are listed. A calculation for the total net value, the tax, and the gross amount has already been added by Apple. If you use the template just tap on the cells to see how the calculation is done.

20131122-130320.jpg

Steps to create the invoice:

  • 1 Tap on the tab ‘Invoice’ and select ‘Duplicate’ from the context menu.
    This step is necessary to not loose the template for further invoices.
  • 2 Delete unwanted rows by tapping on the table (to select the table object).
    Bars appear above the columns and to the left of the rows.

  • 3 Tap on the row in the left bar to delete it.
    The whole invoice is immediately recalculated.
  • 4 Repeat this step for all unwanted items.

Note

If product properties have to be updated select the template ‘Invoice’ and make the necessary changes for product description or unit price.

The more complex version …

An alternate version uses a separate sheet where all products are listed, the price list. Another sheet contains the template for the invoice with some rows where the desired products must be inserted by referring to the sheet ‘Products’.
This version uses two embedded features:

  • 1 the function Lookup
    to search for a product in the sheet ‘Products’ and to insert its unit price in the column ‘Unit Price’. Calculations are done by the same formulas as in version 1.
  • 2 a control called Pop-Up Menu which contains all product descriptions.
    The values for choosing a product must be added manually when creating the control. All values have to match the original product description. Otherwise the ‘Lookup’ function doesn’t return a valid unit price.
    The ‘Pop-Up Menu’ is bound to each line of the column ‘Description’.

Here are the steps to create the Stepper Control to access the unit price from the sheet Products.

  • 1 Tap on the first cell in the column ‘Description’.
  • 2 Tap on the ‘Edit’ symbol in the task bar of Numbers (top left).
  • 3 Select the tab ‘Format’ and then ‘Pop-Up Menu’.
  • 4 Create the proper values by entering the product description.
    You have to enter the exact description used in the sheet ‘Products’. Otherwise the item cannot be found.

20131122-130439.jpg

  • 5 Use the function Lookup within the cell Unit Price to refer to the unit price in the table Products.
  • 5.1 This is done by double-tapping on the cell Unit Price. The input bar of Numbers comes up.
  • 5.2 Tap on ‘Functions’ and then on ‘Reference’.
  • 5.3 Select Lookup.
    The LOOKUP function finds a match for a given search value in one collection, and then returns the value in the cell with the same relative position in a second collection.

    LOOKUP(search for, search where, resultvalues)

    •search tor:
    The value to find. Search value can contain any value.

    Select the first parameter by tapping on it.
    Select the cell Description.
    The coordinate is overtaken as the first parameter.

    •search where:
    The collection containing the values to be searched, search where can contain any values.

    Select the second parameter by tapping on it.
    Select the sheet ‘Products’.
    Select the whole column Description by tapping on the upper bar (which comes comes up when selecting the table). The coordinates of this column are overtaken as the second parameter.

    •resultvalues:
    An optional collection containing the value to be returned based on the search, result values can contain any values.

    Select the third parameter by tapping on it.
    Select the whole column Unit Price.
    The coordinates of this column are overtaken as the third parameter.

  • 5.4 Tap on ‘Finish’ (white check mark with green background).
  • 20131122-131010.jpg

    The usage …

    If you now want to create a new invoice, copy the template, enter the customer data, and compose the product list to be calculated. The latter is done by using the Stepper Controls for Description (Step 1) and Quantity (Step 2).

    20131122-131245.jpg

    The pros and cons …

    Version 1 is simple whereas version 2 is more suitable when it comes to a large number of products. The sheet ‘Products’ can also be used as a price list. Just style it with some added images of products and add the terms and conditions.

    Another benefit of the 2nd version is that the maintenance of products is centralized. With added templates for offers, delivery notes, and sales confirmation there is just one data basis, the sheet ‘Products’. If you change details of a product it will immediately be changed in all the other templates.

    Send and archive …

    Invoices can be mailed to customers as PDFs. For archiving documents it’s recommended to save a PDF on a cloud storage for which encryption is offered, in transit as well as at rest. iDriveSync for example offers client-side encryption and can be accessed via WebDAV protocol from within iWork for iOS.

    For further information about cloud storages and WebDAV access please refer to ‘Related links’ below.

    Summary …

    For small businesses Apple’s Numbers might be useful. Legislation in nearly all countries require to save invoices for a longer period e.g. in Germany it’s 10 years. Additionally it’s required to use a strictly ascending invoice number. To fulfill all the advices mentioned in the legislation can be a hard job when using Numbers for iOS.

    But as always it’s a question of accurate work and an appropriate workflow.

    Related links …

    Numbers for iOS Devices

    Numbers for the Mac

    Risky free clouds

    WebDAV basics

    Thanks for stopping by.


    Actions

    Information




    %d bloggers like this: