May 2019 Tip Of The Month

May 2019 News & Tips

NEWS		SAGE 50		EXCEL
July release planned for Sage 50 version 2020		Easily project your cash balance with Cash Flow Manager		Using the Fill command to speed up entering text and numbers.

News

Sage 50 2020: Sage recently announced plans to release version 2020 of Sage 50 in July, 2019. They haven't published a new features list yet, but they did give us notice that it will install the new version of Pervasive (now called Actian Zen v13). Pervasive/Actian Zen is the database engine Sage 50 uses to manage data files. Most users probably won't notice the change, but those who use multiple versions of Sage 50 (such as accountants supporting clients on old versions) need to be aware that after installing Sage 50 2020, any Sage 50 installations older than version 2019 on that PC will no longer work. If you still need access to version 2018.2 or older you will need a separate computer or you can set up a virtual PC using Hyper-V, VMWare, or a similar tool.

You can read the full announcement on Sage City's Sage 50 Support and Insights blog.

Steve Collins
IQ Accounting Solutions LLC
918-851-9713
scollins@iqaccountingsolutions.com
www.iqaccountingsolutions.com

A complete archive of these tips is available at http://www.iqaccountingsolutions.com/blog

Sage 50/Peachtree Tip - Cash Flow Manager

One of the surprises to people who are new to business is that profit does not always result in surplus cash. To help you know when you will have surpluses and shortages of cash, Sage 50 provides a tool called the Cash Flow Manager.

To get to this tool, click on the Analysis menu at the top of the main Sage 50 window and choose Cash Flow Manager. The “Using cash flow manager” window may pop up. Review it if you want and then close it.

At the top of the screen you can set the time frame. A drop down box that lets you choose how many days or months out you want to project. The minimum is 7 days, the maximum is 12 months.

To the right is a button labeled “Change Cash Flow Settings”. This lets you make some important choices about how your projections are made:

Projections based on: Open transactions or historical transaction.
Open transactions is the most common methods but the historical option may be helpful if you business is very cyclical.
Starting Cash: Here you can choose to whether or not you want to show inactive cash accounts.
Expected Incoming Cash/Expected Outgoing Cash:
These two sections are only available if projecting based on open transactions. They let you choose which types of transactions should be used to project incoming and outgoing cash. The most common choices are automatically selected. There's also an option to show or exclude invoices over 30 days past due.
Expected Receipt date based on: For incoming cash, you can choose to base your projection on transaction due date or on average days to pay. If you choose average days to pay, Sage 50 calculates the average number of days each customer takes to pay.

You can click the Update Cash Flow button to apply your settings or click Cancel to undo any changes you have made.

Back on the main window are 3 tabs: Starting Cash, Expected Incoming Cash, and Expected Outgoing Cash. Starting Cash will already be selected. You will have a list of cash accounts. You can select which accounts you want to include in your calculations. You can also add lines to run “what if” scenarios. Below that will be a table of numbers and a bar chart summarizing the current projections for cash balance, incoming cash, and outgoing cash. Any changes that you make are immediately reflected here.

Moving on to the Expected Incoming Cash tab, you will see that it looks basically like the Starting Cash tab, but instead of selecting cash accounts, you can select invoices and other transaction that you want included in your projection. You can change the amount if you expect a partial payment. Also notice that the first line is empty so that you can enter other items that you want to include in the projection that aren’t in Sage 50, such as a cash from a loan or customer deposit. To enter more than one item, click on “Add new row” at the top right.

The Expected Date here is either the due date or an estimated date based on that customer’s average days to pay, depending on what you chose in the cash flow settings. If an invoice is past its expected date, it will not be included in your projected cash. If you think it should be included, you can change the expected date to a date within your forecast time frame and check the Include box. You can also uncheck invoices that you think should not be included, such as invoices that are in dispute.

The last tab is Expected Outgoing Cash. It looks identical to the incoming cash tab and works the same way, except that it list AP and payroll transactions. If your projection is based on open items, the only payroll transactions that will show up are payroll checks that are already posted and that fall in the selected date rage. To include payroll estimates, you need to enter them manually in the top line of Expected Outgoing Cash. Click on “Add new row” to enter additional payroll or other estimates.

As you make changes on each tab, the balance in the bottom half of the window is updated immediately so you can see results for different scenarios. When you are done you can print your projections, send them to Excel, save them so you can come back to them later, or Reset to start fresh.

Forward To A Friend

Excel Tip - Make the Fill Command Work for You

Excel’s Fill command lets you fill an adjacent group of cells with the same text or numbers, or with text or numbers that form a series. In its simplest form it’s like a faster version of copy and paste. For example, enter a name in cell A1, then highlight cells A1 through A5 and press Ctrl+D (the keyboard shortcut for Fill Down) and all the selected cells will be filled in with the name from A1. But it can do much more.

First let’s talk about the mechanics of using Fill, then we’ll get into what else it can do. There are several ways to access Fill:

Keyboard Shortcuts – Ctrl+D is the shortcut for Fill Down. Ctrl+R is the shortcut for Fill Right. Both require you to first highlight a range of cells, starting will the cell containing the text or numbers you want copied to the other cells.
Fill Button – With the Home tab selected on the ribbon you will find the Fill button in the Editing group at the right end of the ribbon. Clicking it will give you options to fill Down, Right, Up, or Left, which are self explanatory, as well as Across Worksheets, Series, Justify, and Flash Fill. To use the Across Worksheets option, select some cells on the current worksheet, then Shift+Click or Ctrl+Click on one or more tabs to select multiple worksheets. Then choose Fill – Across Worksheets to have the selected cells copied to the other worksheets. I’ll explain the Series option later in this tip and Justify will be covered another time.
Fill Handle – The fill handle is the small black square at the bottom right corner of the bold outline around the currently selected cell(s). Dragging it is the same as using Fill or Fill Series, depending on the situation. Dragging it with the right mouse button will present you with a menu of options. Double-clicking it will automatically do a Fill Down as far as the contents of the adjacent column without you having to select the destination cells.

As I mentioned before, the basic fill command is like a streamlined version of copy and paste. You highlight one or more cells along with the cells you want to fill in, then select the appropriate Fill-Down/Right/Up/Left/Across Worksheet command. That’s nice but the Fill Series option let’s you do much more.

A series can be numbers, dates, days, months, etc. Excel just needs to be able to determine the pattern for the series. Some series are programed into Excel and are automatically recognized when using the fill handle or the Fill – Series button, such as:

Months – Enter January and Excel with fill with February, March, etc. Enter Jan and you will get Feb, Mar, etc.
Days of the Week – Again use the full name (Monday) or the abbreviation (Mon)
Ordinals – 1st, 2nd, 3rd, etc
Text followed by a number – The number will be incremented as in Year 1, Year 2, Year 3…
Dates – dragging the fill handle will increment the day, but using the Fill – Series button will let you choose Day, Weekday, Month, or Year.

To fill a series of numbers you have to tell Excel how you want the numbers incremented. If you enter 1 in a cell and drag the fill handle, it will fill all the selected cells with 1. But if you enter 1 in a cell, then 2 in the cell below it, highlight both cells and drag the fill handle down, it will increment the number in each cell by one. If you did the same thing using 10 and 20 instead of 1 and 2, Excel would continue the series as 30, 40, etc.

If you use the Fill – Series button instead of the fill handle, you can enter just your starting number, then click Fill and choose Series. Here you can enter a Step Value (the number added to each cell in the fill range). You can also set a Stop Value if you want to stop filling once a certain number is reached. If you didn’t highlight a fill area before clicking the Fill button, you can also choose to have your series filled in a row (will fill to the right) or column (will fill down). If you set the series Type to Growth instead of Linear, each cell in the series will be multiplied by the step value. Set the Type to Date and the Date Unit section allows you to choose whether the step value applies to the Day, Weekday, Month, or Year.

Try out all three methods and you will quickly see how Fill works. Don’t forget to try right-dragging the fill handle as a quick way to get to the series options.

Next month I’ll explain how you can create your own custom series such as list of departments or sales reps.

Forward To A Friend

If you know other Sage 50/Peachtree users, do them (and me) a favor and pass this along to them. If you are someone that this has been passed on to, and would like to be added to my email list, you can subscribe for free at www.iqaccountingsolutions.com/signup.html or send me an email at newsletter@iqaccountingsolutions.com. If you need to change your email address or would like to unsubscribe, you can use the links at the end of this email.

Steve Collins
IQ Accounting Solutions LLC
918-851-9713
10611 E 17th Place
Tulsa, OK 74128