Archive for the ‘MS Excel Tips & Tricks’ Category

h1

Intelligence Store – Inventory of Solutions in Store

December 5, 2011

Inventory of Solutions found in the Intelligence Store are as per groups below:

(Click to download copy)

  1. Sage Accpac
  2. Sage Pastel
  3. SagePeachtree
  4. Sage Simply Accounting

………….find these solutions @

Advertisements
h1

Inventory Ordering Tool – Sage Pastel

November 20, 2011

APPLICATION 

This solution is used to make quick, effective inventory ordering and replenishment decisions. Current SalesOrder, PurchaseOrder, and QuantityonHand are factored in with Minimum Order Quantities as well as Inventory Turns based on a user-defined number of periods.  Even Inventory Turnover Qty, along with Minimum OnHand and Preferred Supplier are displayed.  This handy tool makes Inventory Management a streamlined and efficient task for the purchasing manager.
SOLUTION FEATURES
This solution has three performance variants:
V1.0 Recommended Order Quantity, which considers stock necessary to fill orders as well as factoring in Supplier Discounts by satisfying minimum order quantities.  All necessary and current inventory statistics are present for the 1st 30 items setup in your inventory module
V1.1 Recommended Order Quantity, which considers stock necessary to fill orders as well as factoring in Supplier Discounts by satisfying minimum order quantities, Purchase Orders and Sales Orders.
V.1.2 Recommended Order Quantity, which considers stock necessary to fill orders as well as factoring in Supplier Discounts by satisfying minimum order quantities.  Taking Sales Orders, Purchase Orders, Avg Sales Qty over a user-defined date range, Stock on Hand and Minimum Levels into consideration.
All of these variants contain common dynamic features:
• Items, with Description and Preferred Supplier grouped by Item Type
• Current Stock Level details including QOH, QOPO, QOSO
• Quick Glance at Required Stock and off you go to place a Purchase Order and satisfy all open Sales orders
All variants available here.
Platform: Pastel Partner
Excel Version: 2007+

 

h1

Applying filters to a Pivot Table – Excel 2007+

August 8, 2011

This blog post provides readers with a basic introduction to a pivot table and using the field list and filtering features in pivot tables.

A pivot table is an Excel object which is embedded within an Excel worksheet and affords the user the opportunity to use the mouse to move and filter fields from the underlying data source in a very quick and easy way. Below is an image of a typical pivot table, in this case a list of customers with associated information. This post assumes that you have been presented with a pre-built pivot table and are interested in augmenting it.

The field list contains a list of fields available in the data set that the pivot table is based on. The ‘Pivot Table Tools’ tab becomes active in the ribbon when the pivot table is selected and in the sub-tab ‘Options’, the ‘Field list’ button toggles the visibility of the field list. Once this list is visible, the fields in this list can be used in the pivot table by simply selecting the checkbox beside each field or dragging the field into the appropriate section.

The image below provides a closer look at the field list toggle button and field list.

Each field in the pivot table exposes a filter button which allows basic checkbox filtering.

The filter button exposed by a field also provides more advanced filtering options. The example below filters the report by all phone numbers beginning with ‘*604*’.

By selecting the filter on the first field in the row section of the pivot table, the report can be filtered by any of the value fields. In this example the report is filtered to display only customers having a last sale exceeding $23,000.

Hopefully this introduction to using pivot tables provides you with a starting point to apply your creativity to interrogating and augmenting your Sage Intelligence reports.

h1

THE ‘IF STATEMENT’

November 22, 2010

The ‘If Statement’ is often used for basic reporting in Excel. It provides an easy method of assessing the truth of an  assertion and then returning one of two values depending on whether the assertion is true or false.

Syntax: IF(logical_test, [value_if_true], [value_if_false])

In this example an ‘If Statement’ tests whether the value ‘MyDate’ is larger or equal to the date above the target cell; the result is either an actual or a budget value depending on the truth of the assertion:

h1

VLOOKUP FOR REPORTING

November 22, 2010

The ‘vlookup’ function is often used for basic reporting in Excel. It provides an easy method of returning an associated value from another location based on a ‘lookup value’ common to both locations.

In this example a ‘vlookup’ function returns custom categories to the Income Statement that has been generated through the Sage Intelligence menu; the common ‘lookup value’ in this case is the GL account number:

h1

EVALUATING COMPLEX FORMULAS

October 5, 2010

We have all received a formula from a colleague trying to be smart and found it difficult to understand the make-up thereof. Excel provides a tool to evaluate the results and make-up of a formula in the form of the ‘evaluate formula’ button in the ‘formulas’ tab in the ribbon. With the cell selected, click the ‘evaluate formula’ button and then click the ‘evaluate..’ button multiple times. Each time it is clicked the next parameter in the formula/function is evaluated.

 

Evaluate Formula

Evaluate Formula

 

This is a very useful feature for understanding a complex formulas and for troubleshooting such formulas.

h1

NAMED RANGES

October 5, 2010

Named Ranges are an essential part of Excel reporting, among other reasons, for the purpose of making formulas understandable to all users and to keep the workbook neat.

For naming cell/range references, the ‘name box’ can be used to create a named range.

By using the ‘Name Manager’ in the ‘Formulas’ tab a name can also be a placeholder for all of the objects listed in the illustration below.

Names

Names