Blog

5 Types Of Excel Analysis To Achieve

With multiple user friendly Excel functions and features, Microsoft Excel can be used for a variety of different uses, including analysis purposes. There are different types of Excel analysis that one can achieve, here are some of what you can accomplish and how you can achieve them:

1. Scenario Analysis

Scenario Analysis is also known as What-if Analysis, in a sense that it allows you to try different values/scenarios for formulas and more. What you can do is to make use of Excel data tables to draw result variables from two assumed variables. Make use of the excel data table functions to create a structure for your what-if analysis, use constants for your changing scenarios and then carry on to perform your what-if analysis.

2. Trend Analysis

Select the chart you want to analyse. Locate the insert tab, toggle on chart and click on line chart. Once you have done so, it will display the menu tabs that you will need to get to trendline functions. If you have more than one data series in your chart, choose which one you want a trend line for and click it’s line in the chart to see. Figure out what type of trendline you want based on how your data is going to be analysed, once you have done that, set the range you want Excel analysis to forecast for and project it.

3. Quick analysis

Quick analysis is one of the most effective type of Excel analysis options you can choose. Quick analysis basically processes the data you want analysed and gives you several options to summarise and analyse so you can do so conveniently. The Quick Analysis Smart tag becomes available each time you select your data. Click on the option and choose from a range between formatting, data, charts and more. Each category offered would lead you to a preview of a format so you can see what it would look like before you select.

4. Table Analysis

Making use of Pivot Tables in Excel, you can extract and sort data out of a large group with ease. What you can do is locate the Insert tab and choose PivotTable, select the range you would like to apply it to. Save the new pivot table under a new worksheet to avoid compromising your current one and click okay. Activate PivotTable contextual tab and select options. Based on the series of options, you can choose to sort the data accordingly as you please.

5. Statistical Analysis

Statistical analysis is one of the easiest Excel analysis types, basically collecting and analysing batches of data that fit certain criteria. You can accomplish this through the use of excel functions. Some essential ones are AVERAGEIFS, STDEV, SLOPE, INTERCEPT, STEYX and more! Make sure you know what you need to consolidate before you type in the functions and you will be able to easily retrieve the necessary information you need.

These are the 5 types of Excel analysis that you will require sooner or later in your endeavors. You will be able to gain resources and add ins that can further save you some time when it comes to analysing values. If you are interested in learning more, why not consider signing up for some Excel classes in Singapore?

5 Advanced Excel Formulas To Prepare

Excel is a pretty straightforward software to use once you have gained experience and knowledge in it’s interface capabilities. Under usual circumstances, the simple functions and formulas you pick up along the way is sufficient to assist you in daily tasks. However, if you are keen to learn more, you can give these 5 advanced excel formulas a try:

microsoft-excel-formula

  1. VLookUp

Save a lot of your time by learning Vlookup for data processing purposes. With VLookUp, you don’t have to spend time sifting through information, you can ask Excel to sift through and perform a search automatically for you, so you can access the relevant details you need faster. You can make use of this formula to specify what you want as well (details matching the ones you are looking for and the like), so you don’t have to keep making repeat searches.

  1. SUMIF

As strange as it sounds, the Sumif formula is actually a pretty useful one. Especially when you need to sum up values that meet certain specified conditions from an entire list of details. If executed accurately, you will be able to retrieve the sum up of values from all the others that fulfill said condition without having to go through the trouble of doing so manually, hereby decreasing the possibility of error.

  1. SUMPRODUCT

SUMPRODUCT is a nifty formula that allows you to get the total sum of your products with corresponding numbers by referring to multiple arrays of numbers so when applied to areas, especially for sales data, you will be able to easily derive actual results and go on to process the information accordingly. While Sumproduct seems to be of little use, it can actually prove to be your greatest helping tool in others.

  1. Concatenate

The concatenate formula is an easy and quick way of adding multiple text values to different cells without combining the cells, just the contents. In this way, you can combine the text from multiple different rows or columns or insert a formula-calculate value somewhere in the text string. So if you are looking for easy organisation, why not give this a try and see?

  1. Iserror

If you are ever unsure of the details you have keyed in, you can make use of the Iserror formula to check if there is any error in the input. This way, you can effectively track down all your mistakes and correct them as quickly as possible to facilitate smooth progress. Now you don’t have to spend time checking each and every factor and just devote your attention to the ones that matter the most.
Here are 5 advanced excel formulas to get yourself acquainted to. Practice and experiment with the formulas to see how much smoother your excel workbook can function with these in place.

5 Functions Of Excel 2016

Excel 2016 has finally been introduced to the market for corporations and entrepreneurs alike to snatch up. The software comes with the latest upgrades, with a host of different features for the users to try. Here are 5 functions to try in Excel 2016:

microsoft-excel-graphs-and-charts

1. Autocomplete

The autocomplete feature in excel has been improved on so that it’s nowhere as specific as the previous versions were. The autocomplete feature has now been improved on such that it brings you all of the possible functions based on what you have typed. This way, you can save a lot of time and it’s easier to use, since you don’t have to spend time trying to remember what the function you require is named.

2. Chart types

Excel 2016 comes with 6 new chart types- all comprised of the same formatting methods/options for data visualisation. Amongst these new upgrades, there are the treemap and sunburst chart under hierarchy charts, the Waterfall and Stock charts under waterfall charts and histogram or pareto for statistical charts. Then there are also funnel charts designed to allow people to show things like a pipeline for sales and the like.

3. 3D Maps

Dedicated to geospatial visualisation (3D), 3D Maps has been made available to all users who buy and install Excel 2016. The feature comes built into Excel itself, so no add in is necessary. It can be located under visualisation tools and activated by selecting insert.

4. One Click Forecasting

Unlike the other versions of Excel where only linear forecasting is available, now in Excel 2016, the forecast features have been upgraded to enable forecasting based from exponential smoothing. You can also perform this function based on the new feature: one click forecasting. Locate the forecast sheet button under the data tab and create based on your data.

5. Data Loss Protection

Data loss can cause a heavy negative impact on your work, especially if it’s sensitive data. Hence, the DLP (Data Loss Protection) feature in Excel 2016 is highly sought after by corporations, since it enables a real time scan of information based on policies that are predefined, catered for sensitive data like customer information and the like. DLP also synchronises with DLP policies from other sources like Office 365 or Powerpoint.

These 5 features have been added into Excel 2016, amongst the rest of the features that were made available. Find out the full capabilities of Excel 2016 and trying it for yourself! Witness the improvement in your work performance with it’s aid.

Excel Workbook Protection Using Simple Steps

If your line of work requires you to keep track of sensitive data, then you may want to consider applying workbook protection to the relevant excel files you wish to protect. Excel versions like Excel 2010 comes with a built in command ‘Protect workbook’ that enables you to prevent your workbook layout to be changed.

However, do note that this alone is not enough, since this does not protect the contents of your file. What you can do for the contents section, is to make use of the ‘Protect sheet’ function that can be found under the Reviews tab so that your contents will not be able to be changed as well. After doing so, you can even come up with a password so that others are unable to access without one.

excel-workbook-protection

Here’s how you protect your excel workbook:

1. ‘Project Workbook’

Locate the protect workbook function by looking for changes, and then seeking out Review. Once you have the option selected, Excel will open up a project structure and window box. Select the structure option and you should be all set! If you want to go a step beyond, you can also select protect windows as well. But remember to size and position everything first before you have the option selected since it retains this format for you.

2. Password

Once the dialog box is out, take a look at the interface closely. To set up a password for your workbook, you can check the text box that is provided and type in your desired password before selecting ok. Excel will then ask you to confirm the password, just re-enter it again and hit proceed.

3. Protecting (Editable amongst shared)

The above steps were catered for individuals- but if you are protecting a workbook that is shared and updated by other users, you can make use of the ‘Protect and Share workbook’ function instead so that Excel knows and will track the changes that are made. This way, everyone will have the latest updated workbook access.

4. Protecting (Uneditable amongst shared)

Unlike the above, you can protect a shared workbook but this time, no one else can disable Excel from tracking changes. You can assign a password for users who want to make changes instead and they must be able to supply that password before being able to change the details.

And there you have it, simple, straightforward and efficient. By doing this, you can make sure to keep confidential data private and ensure that no one but yourself is able to change details or tweak the data protection services as you please!

MYOB Sales – Other Sales Reports

 

Aside from the other built-in sales reports in MYOB, another interesting report is the Other Sales Reports, which can be accessed under the Reports tab as per screenshot below:

other-sales-reports-img1

In the Sales tab of the Reports window, go to the drop-down arrow to get to the Other Sales Reports as per screenshot below:

other-sales-reports-img2

Other Sales Reports have the following specific reports:

  • Invoice Transactions
  • Referral Source

Invoice Transactions          

Sales Invoice Transactions shows the transactions affecting a sales invoice filtered for a specific customer or for all customers.  This is especially applicable if you have customers who usually pay invoices in installment amounts, making it difficult for you to prepare an Excel summary or schedule on when and how much payments are made for each payment transactions.

If you want to generate sales invoice transactions for a specific customer, you just need to indicate the customer name, period from and to.  Please refer to the screenshot below to have a view of how the filter works.

other-sales-reports-img3

Assuming we are going to get the Invoice Transactions for B Customer that needs to be indicated in the Customer field, you need to indicate the specific customer as per screenshot below:

other-sales-reports-img4

After clicking on Display, the Invoice Transactions, this is how this report appear:

other-sales-reports-img5

For sure, you also have the option to run this report for All Customers and in this case, you have to apply this filter to the Display field.  Depending on the report format you want to create, you can generate an Excel or send direct to Email, Fax, PDF, HTML, Tab-Delimited or Comma-Separated Value. To do this, you click on the Send To window on the lower left corner of the Report display as per screenshot below:

other-sales-reports-img6

Click on the Sent To icon and click on the selected report form or to send through the email as shown on the screenshot below:

other-sales-reports-img7

If you want the report to be in Excel format, you click on this to choose and the Excel format appears on your screen below:

other-sales-reports-img8

When you want the report to be generated in PDF format, you can also do so by clicking on the PDF and below is the sample report generated:

other-sales-reports-img9

These report formats are just proof of how MYOB have put much thought to make your reporting and financial information report available with just a click or two away.  So, you have to bear in mind that when you are reconciling customer statements and the customers would like to know which payments are applied to which invoices and the dates these payments come in, then you have to use the Sales – Invoice Transactions Report.

Referral Source

Another report sales invoices report is the Referral Source.  This report shows the invoices generated and coming in from the Referral Source.  If you are using the Referral Source to identify your sales channels or sales source – email, promotional program, referral, you will be able to use this report to evaluate which Referral Source contribute more to sales invoices activities.  This is a very helpful tool for you to arrive at sound business judgement on what advertising and marketing strategies to implement to successfully improve sales generation.

 

switch-to-system

Switching from Manual Accounting to System or Computerized System

One of the most challenging decision business owners face nowadays is whether to continue using manual process for accounting of business transactions or change into a computerized system.  To help business owners make a sound business judgment, one very important factor to consider is if the benefit to have this computerized system outweigh the cost to implement this. Computerized system can be further classified for those specifically created for the company and which usually cost more because of the planning and development stages to arrive at the best system design specifically applicable to the business operations’ unique processes.  Most often, this cost more than having a ready to install and ready to run software package like MYOB.  There are also accounting software which require you to pay monthly for the use of the system but if you add these costs along the many months you get to pay, you will realize that you save most in purchasing MYOB one-time than paying monthly for another software.  When you stop your subscription for these online software programs, you will lose your access too, except if you have sufficiently made back up to all your company files.

Cost to have an accounting software range from the cost of the software package, program development costs if you want your own accounting system designed specifically to your business operations, the time spent on having to plan and evaluate the adequacy of the system before implementation, training costs for your staff, any additional computer hardware requirement and licensing.  Also, there are other staff or business partners who are not comfortable having to change and you could possibly face resistance from them especially if they say that the cost would be so expensive and if you don’t know how to approach at the numbers and money involved, you cannot ascertain and appreciate the benefits of having this system if you just focus on the short-term cost associated with this and not looking at the long-term cost savings.  This holds true if you currently have 2 employees helping each other to manually keep track and record all business transactions who would also incur overtime works to finish the job. It is also possible that the workload is so voluminous that you as business owner is also involved in doing the clerical job, instead of you having to devote your time to growing the business more and being aware of the market and completion development in the area of your business. If you have a good accounting software like MYOB, the time to manually do the job should be decreased and also enable you to devote your time more on growing your business, aside from having timely financial reports.

The benefits of having an accounting software especially MYOB desktop version outweigh the cost on the long run to produce a wide range of reports and produce timely reports ranging from sales, tax, purchases, accounts and many other built-in reports. Also, you get to get away with the accounting system planning and development phase which surely cost you more than buying the MYOB system and have it running all at once and start to use it.  If you are undecided which MYOB program to choose contact us and visit our website at http://www.a1corp.com.sg/

excel

What You Should Know About Excel Vlookup

If you are a user of Excel, you would have heard about the Excel Vlookup function in passing. Not many people are clear on what the function actually does so here are some things you should know about Excel Vlookup.

The function is actually used for looking up values that belong in the vertical array of data, and returning the corresponding one from another column in the same array. It is best used for exact matching and also closest matching to the lookup value.

For Excel Vlookup to work, there are certain factors that are required to be followed. First of all, the data on the supplied table_array has to be organised in columns. Upper and lower case texts are perceived as equal but numbers and text are not perceived as equal, and all characters inclusive of spaces are included when you are searching for matches.

If you are using the function and you want an exact match, then the lookup range argument should be set to false while the Vlookup function searches for the exact match. If there are no exact matches to be found, the function will reveal an #N/A which is an error. If the results are conclusive, then Vlookup will use the first match that it finds. However, if the lookup value is a string of text, you can use characters that are considered wildcard- ? matches with any single character and * matches with any set characters.

If you are looking for the closest match, then the range look up argument should actually be set to true so that it can match to the closest value, depending on if it is below or equal to the value. The supplied array table should also be done in ascending order, otherwise, it may lead to unpredictable results. If the look up value is smaller than all of the supplied array table, then the #N/An error would be seen.

These are the basic rules needed to operate the Vlookup function without any errors or inaccurate results. It may seem confusing or complicated at first but the function is surprisingly easy to use. Once you have familiarised yourself with it, it would become like second nature to you.

Page 7 of 17« First...56789...Last »