Avoid a Liquidity Bottleneck

18 06 2014

To avoid a liquidity bottleneck at the end of a month you can use Apple’s iWork app Numbers. Once you created an appropriate spreadsheet with the necessary information you can predict your account balance more or less accurately (if the rare event of a crashed iOS device doesn’t happen and you are out of warranty claim).

Here is an example to create the spreadsheet using functions implemented in Numbers and some other tricky algorithms. The good news is, it’s synced with all your other devices via iCloud. So the information is available wherever you are.

The components …

What you need is

  • Apple’s iWork app Numbers
    It’s part of Apple’s iWork suite, the pendant to Microsoft’s Office suite with one important difference: it’s optimized for touch screens and seamless to use even on the small screen of an iPhone and all your documents are automatically synced with all your devices. You can even work on your documents if you don’t have access to one of your Apple devices because there is iWork for iCloud with which you can work on documents via browsers like Safari, Google’s Chrome, or Microsoft’s Internet Explorer.
    And it’s free for newly purchased devices.
  • A spreadsheet within Numbers
    which allows to predict the account balance
  • Access to your bank account
    to look up the transactions and the actual balance.

If you are not familiar with programming a spreadsheet don’t hesitate to download my template from my Box account. It’s free and there is no copyright on it. If you downloaded the template you can (easily) adapt it to your needs. The following description might help.

The spreadsheet …

The template contains two essential tables.
The first table is designed to enter the actual account balance, the performed transactions as well as periodical transactions. I call it the Dynamic table

The second table contains the dates for periodical transactions like rents or taxes to be paid in advance. I call it the Static Table.

Here is how it looks like:

20140314-185914.jpg

And here is the download link

Application File format
Apple Numbers numbers

Notes

If you tap on the link and downloaded the file you will see a control ‘Open in Numbers’ for a short time. If the message is gone just tap on the display again.

If you want to work with the template it’s highly recommended to do it with a copy of the original.

The dynamic table …

To predict the approximate liquidity at the end of a month you have to enter the actual account balance and monitor what’s expensed during the month by accessing the transactions with an online banking tool or a browser.

20140314-190023.jpg

The spreadsheet contains the necessary formulas to add or subtract incoming and outgoing money transfer. For cash payments (food, has, etc.) an approximate amount can be set on a weekly basis.

20140314-190850.jpg

Every time you get fresh cash from your bank you can reduce the remaining value with a so-called ‘Stepper’ control.
A Checkbox is used for payments on a monthly basis e.g. your health insurance. Just set it to true if the payment is expensed.

The static tables …

The first table is just for calculating a date 30 days prior to the actual date to point to an upcoming payment. The second table is for documentation purposes only and not used in any calculation.

The EOMONTH function returns a date 30 days prior to the actual date.

20140314-190209.jpg

This the conditional statement which sets the factor to -1 for all months (3, 6, 9, 12) with a due date. It can be easily adapted to other periods resp. months.

20140314-190303.jpg

This simple conditional statement sets a marker to emphasize upcoming payments.

20140314-190352.jpg

Summarized:

The kernel statement (image 2) sets the factor to -1 according to due dates.
Factor x Base Value defines the content in the column ‘Value’ which is part of the summarization to get the total liquidity.

The adaption …

You have to adapt the template by entering all the necessary values. A look on your last statements of account helps to find out the values. Some of them will be fixed values others vary and you have to define an average value.

You can use the COPY+PASTE feature to add more lines of similar type. In the context menu select ‘PASTE FORMULAS’ to let numbers adapt the clipboard content.

Summary …

If you run into the red it’s not my fault.
I’m sure the reason is your optimism bias or a shaking finger followed by wrong entries.

Related links …

An invoice created with numbers

If … Then … Else

Thanks for calculating with iNotes4You.





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.





    If … Then … Else

    8 03 2013

    IF your device crashed THEN

    restore it from a backup

    ELSE

    use it for improving your life

    END IF

    Software developers call this an IF-Statement.

    The part marked in red is the CONDITION and the ones in blue are ACTIONS.
    The condition is always a question to the system with an unexceptional True or False for the answer. If the condition would sound like ‘IF the car is expensive THEN’ the following action will be up to a subjective assessment of the meaning of ‘expensive’ and no rigorous True or False.

    It’s definitely the most used statement in programming and all of us are faced with this procedure over and over. In real life it’s sometimes hard to pursue the right path and we need time to decide and may go another way if our first decision was wrong. The main difference between IFs in software development and in real life is that the CONDITION in real life often cannot be reduced to a Yes or No.

    For a better understanding look at this video.
    It explains some lines of programming and the result on the iPhone’s screen.

    I’m sure this article is boring for developers but can be interesting for normal users as IF-Statements can be effectively used e.g. in Apple’s spreadsheet app Numbers.

    So let’s leave the magic activities of app developers behind us and return to daily life.

    Consider the following situations …

    Mark a line in my spreadsheet to emphasize

    • it’s important
    • it’s greater than 500
    • there are less than 30 days to an event

    Unlike in grown up spreadsheet applications installed on big brothers of the iPad conditional formatting is not available in Numbers for iOS. However here is an alternative to feature information.

    • Double tap the cell where a marker should be displayed
    • Select = from the input bar
    • Tap on Functions
    • Select Logical and Information and then If
      Numbers puts a generalized If-Statement into the input bar.
      Your task is now to fill it out with referrals to cells.
    • For defining the If-expression tap on cell C3 and add =High.
    • The action for if-true is to display the marker.
      To do this tap on “abc” on the keyboard. An input bar opens. Enter the marker e.g. from the iOS keyboard Emoji.
    • Repeat the procedure for the if-false part of the expression and in the input bar enter nothing so that nothing is displayed if the priority is not High.

    20130120-144509.jpg

    Here is the formula for an If-Statement comparing a value with a given value.
    The marker is set if the contents of cell C5 is greater than 500.

    20130120-145320.jpg

    Here is the formula for an If-Statement comparing a given date with the current date using the NOW-Function which provides the current date and time of your devices clock. Additionally the a function called DATEDIF is used which provides the difference between two dates in a selectable unit (day, month, etc.).

    20130121-053105.jpg

    If you have any questions don’t hesitate to contact me via comment or the contact form on my page ‘About’.

    Thanks for visiting http://iNotes4You.com. I hope you come back soon.





    Numbering in Apple’s Numbers

    6 03 2013

    Numbers is Apple’s app for creating spreadsheets.
    It’s part of the iWork Suite for iOS.

    From my point of view it’s the far away best app for working with spreadsheets on iOS devices.
    It’s simple but yet powerful. The UI is perfect and with the last update 1.7 (2012-12-04) Apple provided a functionality called ‘LOCK’ with which you may secure objects, e.g. tables so that their position and their content cannot be changed any longer until they are unlocked.

    I want to continue my series about the usage of Apple’s Numbers.
    The task is to automatically number lines in a table starting with a given value, to extend the numbering if lines are added and renumber them if lines are moved to new positions.

    Step 01 …

    Set the start value e.g. 100 in the upper left cell. The cell is identified by A1.

    Step 02 …

    Define a formula which tells Numbers how to generate the line number for the line following the first line with the start value.

    20130119-165031.jpg

    To define a formula for a cell double tap the cell and select ‘=’ from the input bar.
    The input field is prepared to enter a formula. Tap on the cell A1 which contains the start value 100. A symbol for this cell appears in the input field. Add 1 to this parameter and tap Done on yhe right side of the input field. 101 will appear in cell B1.

    Step 03 …

    I think you agree with me that you don’t want to repeat this procedure for the next 200 lines.
    So select cell B1 and tap on Copy in the upcoming context menu.
    Select cell C1 and expand the cell marker downwards for all lines you want to be numbered.
    Tap on Paste and then on Paste formulas.

    20130119-175324.jpg

    Step 04 …

    If you add lines to the table the new lines are firstly not numbered.
    To renumber all lines just copy the first cell B1 and repeat the Paste procedure described in Step (3). This can also be done after inserting new lines or changing places of lines.

    Thanks for visiting my blog.





    Update 1.7 of iWork for iOS

    7 12 2012

    On 2012-12-04 Apple’s iWork-Suite was updated to version 1.7.

    This update is a major improvement of iWork for iOS without destroying the simplicity of usage. The updated features are perfectly designed and retain the simplicity of the product. From my point of view that’s an intellectual stimulus for all people developing software.

    We will see how the competitor Microsoft will response.

    For now, advantage Apple!

    The feature changes …

    20121205-144043.jpg

    Pages for iOS …

    • Use Change Tracking to track changes to body text in a document
    • Accept and reject individual changes as you review a document
    • Import Pages and Microsoft Word documents with change tracking and continue to track changes to body text
    • Preserve tracked changes in documents exported in Microsoft Word or Pages format
    • Preserve calculations in tables when importing from and exporting to Pages for Mac

    Numbers for iOS …

    • Hide and unhide rows and columns
    • Import and export Numbers for Mac spreadsheets with filters, and turn filters on and off
    • Preserve rich text in tables when importing and exporting

    Keynote for iOS …

    • Import and export all Microsoft PowerPoint and Keynote for Mac slide sizes
    • Import and export presentation themes, complete with master slides and preset styles
    • Play back all Keynote action builds including Move, Rotate, Scale, and Opacity
    • Add new slide transitions including Shimmer and Sparkle
    • Preserve calculations in tables when importing from and exporting to Keynote for Mac
    • New print layouts include options to print with presenter notes, with builds, and without backgrounds

    Common improvements …

    • Lock and unlock objects
      For locked objects changing properties (size, position, content) cannot be changed until they are unlocked.
    • Add reflections to shapes
    • Improved compatibility with the pendants Microsoft Word, Excel, Powerpoint

    Some details …

    Locking of objects …

    On my point of view this is a great new feature, because faulty operations on touch screens cannot be avoided particulary on the small screen of an iPhone.

    20121205-160439.jpg

    Objects which can be locked in …

    Pages Numbers Keynote
    Shape ‘Curve’ only All Objects All Objects

    Improved page print layout in Keynote

    20121205-220739.jpg

    Lacks in Numbers …

    Locking …

    20121205-223906.jpg

    SIRI and the keys …

    20121206-160621.jpg





    Page layouts in iWork

    3 12 2012

    Before you print a Microsoft Office document you can quickly fine-tune it in the Page Layout view to achieve professional-looking results. As in Normal view, you can change the layout and format of data, but in addition, you can use the rulers to measure the width and height of the data, change the page orientation, add or change page headers and footers, set margins for printing, hide or display gridlines, row and column headings, and specify scaling options. When you finish working in Page Layout view, you can return to Normal view.

    Page Layout view is indispensable for many layout tasks that prepare your data for printing.
    You can see how your data will look when it is printed.

    iWork for iOS-Devices …

    iWork for iOS-Devices is designed as a powerful tool with basic functionalities for editing text (App Pages) , spreadsheets (App Numbers) and presentations (App Keynote). Working up complex data for professional-looking printer output is not in the foreground.

    However let’s have a look on the features oft he iWork-Apps.

    KEYNOTE for presentations …

    Keynote-Documents always consist of slides.
    Each slide simultaneously is printed as one page in landscape orientation.
    There are no further options except printing From-To-Page, defining the number of copies and (depending on the printer capabilities) printing on both sides of the paper.

    PAGES for text editing …

    Without further settings, you will not see any page brakes while editing text.
    Here is a brief description what you have to do …

    • Tap the Wrench-Symbol in the toolbar, and then tap Document Setup
    • Tap the area below the document body to insert a page footer.
      There are three sections available. Tap in one section, and then tap Page Numbers.
      Tap the page numbering example that matches the format you want.
    • Tap Done in the left corner of the screen.

    20121201-160750.jpg

    There are some other options (header, watermark) which are not discussed here.
    When you return to the normal view you will see the page numbering as an orientation what you write on which page.

    Note …
    These settings must be set for every new document.
    So the best practice is to design a template containing all your settings.

    NUMBERS for editing spreadsheets …

    Spreadsheets may contain a large amount of data organized in columns and rows.
    So options for adjusting the layout are obvious.

    20121201-204805.jpg

    Summary …

    iWork for iOS is not designed for labor-intensive usage in an office.
    There are only a few options to control the output but they meet the requirements.
    Apple found out the right level of simplicity.

    Here is a comparison between Apple’s iWork and Microsoft’s Office 365 for iOS

    Office 365 for Mobiles

    Thanks for dropping by.





    Linking sheets in NUMBERS

    29 11 2012

    If you want to link various sheets in Apple’s iWork-App NUMBERS here is an example how to do this.

    You may download the sample in the native NUMBERS-Fileformat.

    20121008-141900.jpg Linking sheets with NUMBERS (.numbers-Document)
    Properties EN .Numbers iDrive
    Alternative Download from Dropbox

    The essentials in brief …

    • 1 Tap on the cell in the destination sheet you want to link with a cell in the source sheet.
    • 2 Tap on ‘=’ for creating a formula.
    • 3 Tap on the small ‘Down Arrow’ on the right side.
      The sheets come up for selection.
    • 4 Select the sheet and cell containing the source data.

    20121120-063355.jpg

    Creating formulas with linked data works in the same way as in a standalone sheet.
    The only difference is that you have to select the sheet (Step 2) before you refer to cells of it in your formula.








    Follow

    Get every new post delivered to your Inbox.

    Join 186 other followers

    %d bloggers like this: