Course & topics

Join Our 7452 Happy Students​ Today!

Test

U/S; 12998 – Produce Spreadsheets Using Accounting Related Information Technology  Copy

Produce Spreadsheets Using Accounting Related Information Technology 

Learning Unit1

US:12998 NQF Level 5 Worth 8 Credits

PRODUCE SPREADSHEETS USING ACCOUNTING RELATED INFORMATION TECHNOLOGY

Unit Standard Purpose

People credited with this unit standard are generally found within the accounting technician intermediate stage. On successful completion of the unit standard, people will be able to use information technology as an essential tool when undertaking accounting activities. This involves the ability to obtain information from a computerised management information system, produce spreadsheets for the analysis of numerical information, and contribute to the quality of the aforesaid management information system within an accounting environment.

Learning Assumed to be in Place

Unit standards for Computer literacy at NQF level 4

Unit standard, Work with information technology in an accounting environment, NQF level 5

Session 1

SO 1

Obtain information from a computerised management information system.

Learning Outcomes

(Assessment Criteria)

·        the principles of general information technology are described in relation to MIS, other IT applications and spreadsheets.

·        The required information is correctly located within the MIS structure.

·        Advice is sought from the appropriate person, where there are difficulties in obtaining the required information.

·        Additional authorisation is obtained for sensitive or confidential information in accordance with the organisation’s security regulations.

·        Information is checked for its accuracy and completeness.

·        Information is stored in a format that facilitates access and use by others.

·        Confidential information is kept secure and not disclosed to unauthorised people.

Obtain information from a computerised management information system

 What is computerized management information system?

A management information system (MIS) is a computerized database of financial information organized and programmed in such a way that it produces regular reports on operations for every level of management in a company. It is usually also possible to obtain special reports from the system easily.

What Is a Management Information System?

In business, management information systems (or information management systems) are tools used to support processes, operations, intelligence, and IT. MIS tools move data and manage information. They are the core of the information management discipline and are often considered the first systems of the information age.

MIS produce data-driven reports that help businesses make the right decisions at the right time. While MIS overlaps with other business disciplines, there are some differences:

  • Enterprise Resource Planning (ERP): This discipline ensures that all departmental systems are integrated. MIS uses those connected systems to access data to create reports.
  • IT Management: This department oversees the installation and maintenance of hardware and software that are parts of the MIS. The distinction between the two has always been fuzzy.
  • E-commerce: E-commerce activity provides data that the MIS uses. In turn, the MIS reports based on this data affect e-commerce processes.

The concept includes what computers can do in this field, how people process information, and how best to make it accessible and up-to-date. Cummings adds, “The ‘right information in the right place at the right time’ is what we are striving for. This discipline is much more eclectic than straight computer science.”

 Besides computer science, there are fields of study that overlap with MIS, both at the theoretical and practical levels:

  • Information Systems (IS): In IS, there is a greater emphasis on tools, while MIS places more emphasis on business processes and operations.
  • Information Technology (IT): IT is similar to IS, but it focuses solely on computers.
  • Informatics: A discipline that combines software engineering, information systems development, and networking.
  • Electrical Engineering and Computer Engineering: These fields focus on the development and improvement of hardware and software, respectively. MIS helps determine the practical and theoretical implications of these changes.

History of Management Information Systems

The technology and tools used in MIS have evolved over time. Kenneth and Aldrich Estel, who are widely cited on the topic, have identified six eras in the field.

After an era ends, the previous era’s hardware are still in use. In fact, mainframes (albeit much faster, cheaper, and easier to access than their predecessors) are still used today.

From Ledgers to Flash Memory

In the days where businesses recorded all transactions in a bound ledger, tallying and tracking what was going on took a lot of time and work. In the late 1800s, process automation began to appear in the form of punch cards. Associated machines tabulated the punch card data and printed results, which made it easier to capture transactions. The company that came to eventually be known as IBM was founded in the early 1900s and became the leader in business machines and punch cards. These cards evolved from a solution to automate pattern creation in weaving machines. The company adapted the idea to store and input data for applications from as simple as time for payroll to very complicated uses like recording census data. When general-purpose computers became available after WWII (originally developed for codebreaking, calculating shell trajectories, and other war-related needs), the punch card became an input method as well as a way to store outputs (though it required readers to decode and print the data so people could read it).

Later, magnetic media (such as tapes and floppy disks) took over the storage of input and output, and computers could read and write directly to their own memory. This eliminated the need for the specialized machines. Next, optical media (like CDs and DVDs) that could store much more data on a single disc came along. Today, we are transitioning to flash memory (which also goes by solid state, as in a solid state drive or SSD). Flash memory has a higher capacity, is less volatile, and you can reuse it thousands of times with little degradation in quality.

Each of these periods has brought an increase in storage capacity at a lower cost. In tandem with the constant increase in computing power, more and more powerful software, almost-ubiquitous connectivity via wifi and mobile devices, and ever-expanding networking that evolved into the internet, work that previously took many hours – like tabulating a company’s shipping costs over a year or population increases in a state over a century – now takes little time or human effort.

On the software side, the functions that paper ledgers performed moved to spreadsheet programs (the term spreadsheet came from the large sheets of paper spread out on tables). Microsoft Excel is the best-known example, but it wasn’t the first to become popular. VisiCalc, which was created for the Apple II in the late 1970s by Dan Bricklin and Bob Frankston, was the first to gain popularity. There were spreadsheet programs available for mainframes and minicomputers before VisiCalc, but they didn’t offer the ability to see results in real time.

Spreadsheets became more powerful in the 70s and 80s. When connected with databases, they gave users the ability to easily and quickly access and manipulate data. As users’ needs and desires changed, specialized programs were developed for different user groups, allowing innovative ways to use data.

Information technology and MIS used to be synonymous. Task automation (such as report creation) led to an expansion of the work that fell under MIS. Simultaneously, the definition of IT expanded even more, and it now encompasses areas beyond MIS, such as cyber security and network administration.

Categories of Management Information Systems

Management information system is a broad term that incorporates many specialized systems. The major types of systems include the following:

  • Executive Information System (EIS): Senior management use an EIS to make decisions that affect the entire organization. Executives need high-level data with the ability to drill down as necessary.
  • Marketing Information System (MkIS): Marketing teams use MkIS to report on the effectiveness of past and current campaigns and use the lessons learned to plan future campaigns.
  • Business Intelligence System (BIS): Operations use a BIS to make business decisions based on the collection, integration, and analysis of the collected data and information. This system is similar to EIS, but both lower level managers and executives use it.
  • Customer Relationship Management System (CRM): A CRM system stores key information about customers, including previous sales, contact information, and sales opportunities. Marketing, customer service, sales, and business development teams often use CRM.
  • Sales Force Automation System (SFA): A specialized component of a CRM system that automates many tasks that a sales team performs. It can include contact management, lead tracking and generation, and order management.
  • Transaction Processing System (TPS): An MIS that completes a sale and manages related details. On a basic level, a TPS could be a point of sale (POS) system, or a system that allows a traveller to search for a hotel and include room options, such as price range, the type and number of beds, or a swimming pool, and then select and book it. Employees can use the data created to report on usage trends and track sales over time.
  • Knowledge Management System (KMS): Customer service can use a KM system to answer questions and troubleshoot problems.
  • Financial Accounting System (FAS): This MIS is specific to departments dealing with finances and accounting, such as accounts payable (AP) and accounts receivable (AR).
  • Human Resource Management System (HRMS): This system tracks employee performance records and payroll data.
  • Supply Chain Management System (SCM): Manufacturing companies use SCM to track the flow of resources, materials, and services from purchase until final products are shipped.

Types of MIS Reports

At their core, management information systems exist to store data and create reports that business pros can use to analyze and make decisions. There are three basic kinds of reports:

  • Scheduled: Created on a regular basis, these reports use rules the requestor has provided to pull and organize the data. Scheduled reports allow businesses to analyze data over time (e.g. an airline can see the percentage of lost luggage by month), location (e.g. a retail chain can compare sales figures from different stores), or other parameters.
  • Ad-hoc: These are one-off reports that a user creates to answer a question. If the reports are useful, you can turn ad-hoc reports into scheduled reports.
  • Real-time: This type of MIS report allows someone to monitor changes as they occur. For example, a call center manager may see an unexpected spike in call volume, and find a way to increase productivity or send some of the calls elsewhere.

Benefits of Using Management Information Systems

Beyond the need to stay competitive, there are some key advantages of effective use of management information systems:

  • Management can get an overview of their entire operation.
  • Managers have the ability to get feedback about their performance.
  • Organizations can maximize benefits from their investments by seeing what is working and what isn’t.
  • Managers can compare results to planned performance by identifying strengths and weakness in both the plan and the performance.
  • Companies can drive workflow improvements that result in better alignment of business processes to customer needs.
  • Many business decisions are moved out of upper management to levels of the organization that is closer to where the knowledge and experience lie.

Components of MIS

The major components of a typical MIS long-form (Management Information System) are:

  • People – people who use the information system
  • Data – the data that the information system records
  • Business Procedures – procedures put in place on how to record, store and analyze data
  • Hardware – these include servers, workstations, networking equipment, printers, etc.
  • Software – these are programs used to handle the data. These include programs such as spreadsheet programs, database software, etc.

 Types of Information Systems

The type of information system that a user uses depends on their level in an organization. The following diagram shows the three major levels of users in an organization and the type of information system that they use.

 Transaction Processing Systems (TPS)

 This type of information system is used to record the day to day transactions of a business. An example of a Transaction Processing System is a Point of Sale (POS) system. A POS system is used to record the daily sales. 

Management Information Systems (MIS)

Management Information Systems abbreviated as MIS, are used to guide tactic managers to make semi-structured decisions. The output from the transaction processing system is used as input to the MIS system.

 Decision Support Systems (DSS)

Decision support systems are used by top level managers to make semi-structured decisions. The output from the Management Information System is used as input to the decision support system’s systems also get data input from external sources such as current market forces, competition, etc.

 Manual Information Systems VS Computerized Information Systems (MIS)

Data is the bloodstream of any business entity. Everyone in an organization needs information to make decisions. An information system is an organized way of recording, storing data, and retrieving information.

 Manual Information System

A manual information system does not use any computerized devices. The recording, storing and retrieving of data is done manually by the people, who are responsible for the information system.

 The following are the major components of a manual information system 

  • People –people are the recipients of information system
  • Business Procedures –these are measures put in place that define the rules for processing data, storing it, analyzing it and producing information
  • Data –these are the recorded day to day transactions
  • Filing system – this is an organized way of storing information
  • Reports –the reports are generated after manually analyzing the data from the filing system and compiling it.

The following diagram illustrates how a typical manual information system works

 

Advantages and Dis-advantages of a manual information system

 Advantages:

The following are the advantages of manual information systems

  • Cost effective – it is cheaper compared to a computerized system because there is no need to purchase expensive equipment such as servers, workstations, printers, etc.
  • Flexible –evolving business requirements can easily be implemented into the business procedures and implemented immediately

 Disadvantages:

The following are some of the disadvantages of a manual information system. 

  • Time consuming –all data entries need to be verified before filing, this is a time-consuming task when done by humans. Retrieving data from the filing system also takes a considerable amount of time
  • Prone to error – the accuracy of the data when verified and validated by human beings is more prone to errors compared to verification and validation done by computerized systems.
  • Lack of security – the security of manual systems is implemented by restricting access to the file room. Experience shows unauthorized people can easily gain access to the filing room
  • Duplication of data –most departments in an organization need to have access to the same data. In a manual system, it is common to duplicate this data to make it easy to accessible to all authorized users. The challenge comes in when the same data needs to be updated
  • Data inconsistency – due to the duplication of data, it is very common to update data in one file and not update the other files. This leads to data inconsistency
  • Lack of backups – if the file get lost or mishandled, the chances of recovering the data are almost zero.

Computerized information system

Computerized systems were developed to address the challenges of manual information systems. The major difference between a manual and computerized information system is a computerized system uses a combination of software and hardware to record, store, analyze and retrieve information.

Advantages and Disadvantages of a computerized information system (MIS)

The following are some of the disadvantages of a computerized information system.

Advantages:

The following are the advantages of computerized information systems

  • Fast data processing and information retrieval – this is one of the biggest advantages of a computerized information system. It processes data and retrieves information at a faster rate. This leads to improved client/customer service
  • Improved data accuracy – easy to implement data validation and verification checks in a computerized system compared to a manual system.
  • Improved security – in addition to restricting access to the database server, the computerized information system can implement other security controls such as user’s authentication, biometric authentication systems, access rights control, etc.
  • Reduced data duplication – database systems are designed in such a way that minimized duplication of data. This means updating data in one department automatically makes it available to the other departments
  • Improved backup systems – with modern day technology, backups can be stored in the cloud which makes it easy to recover the data if something happened to the hardware and software used to store the data
  • Easy access to information – most business executives need to travel and still be able to make a decision based on the information. The web and Mobile technologies make accessing data from anywhere possible.

Disadvantages:

  • It is expensive to set up and configure – the organization has to buy hardware and the required software to run the information system. In addition to that, business procedures will need to be revised, and the staff will need to be trained on how to use the computerized information system.
  • Heavy reliance on technology – if something happens to the hardware or software that makes it stop functioning, then the information cannot be accessed until the required hardware or software has been replaced.
  • Risk of fraud – if proper controls and checks are not in place, an intruder can post unauthorized transactions such as an invoice for goods that were never delivered, etc.

Session 2

SO 2

 

Produce spreadsheets for the analysis of numerical information.

Learning Outcomes

(Assessment Criteria)

The spreadsheet is titled in a way that clearly defines its use and purpose.

The arrangement of the spreadsheet is consistent with organisational conventions.

Spreadsheet produced for own work and area of responsibility and/ or on request from others

All rates and other numeric inputs and assumptions are stated to the correct number of decimal places.

Calculated values are checked for correctness when changes are made to input data.

The spreadsheet is used to carry out data modifications and for entry of related formulas.

Each cell is formatted clearly and accurately.

A method is selected to eliminate rounding errors which is suitable for the purpose of the spreadsheet.

Confidential information is kept secure and not disclosed to unauthorised people.

Produce spreadsheets for the analysis of numerical information

A spreadsheet is a computer application for organization, analysis and storage of data in tabular form. Spreadsheets were developed as computerized analogs of paper accounting worksheets. The program operates on data entered in cells of a table. Each cell may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells. A spreadsheet may also refer to one such electronic document.

Spreadsheet users can adjust any stored value and observe the effects on calculated values. This makes the spreadsheet useful for “what-if” analysis since many cases can be rapidly investigated without manual recalculation. Modern spreadsheet software can have multiple interacting sheets and can display data either as text and numerals or in graphical form.

Besides performing basic arithmetic and mathematical functions, modern spreadsheets provide built-in functions for common financial accountancy and statistical operations. Such calculations as net present value or standard deviation can be applied to tabular data with a pre-programmed function in a formula. Spreadsheet programs also provide conditional expressions, functions to convert between text and numbers, and functions that operate on strings of text.

Spreadsheets have replaced paper-based systems throughout the business world. Although they were first developed for accounting or bookkeeping tasks, they now are used extensively in any context where tabular lists are built, sorted, and shared.

How to Make a Spreadsheet in Excel

Spreadsheets are grid-based files designed to organize information and perform calculations with scalable entries. People all around the world use spreadsheets to create tables for any personal or business need.

However, spreadsheets have grown from simple grids to powerful tools, functioning like databases or apps that perform numerous calculations on a single sheet. You can use a spreadsheet to determine your mortgage payments over time, or to help calculate the depreciation of assets and how it will affect your business’s taxes. You can also combine data between several sheets, and visualize it in color-coded tables for an at-a-glance understanding. With all the new functionality, using a spreadsheet program can be intimidating for new users.

How to Create a Spreadsheet in Excel

The world’s most robust pure spreadsheet application, Excel, comes as part of both Microsoft Office and Office 365. There are two main differences between the two offerings: First, Microsoft Office is an on-premise application whereas Office 365 is a cloud-based app suite. Second, Office is a one-time payment, and Office 365 is a monthly subscription. Excel is available for both Mac and PC.

Unique Features of Excel

With over 400 functions, Excel is more or less the most comprehensive spreadsheet option when it comes to pure calculations. It also has strong visualization abilities, including conditional formatting, Pivot Tables, SmartArt, graphs, and charts. Home and business users alike can create powerful spreadsheets and reports to track data and inform their decisions.

One powerful Excel feature is Macro, little scripts and recordings you can create to make the program perform different actions automatically. While no other spreadsheet program has this type of feature, it is complex and can pose difficulty for beginners.

Excel also has close tie-ins with Microsoft Access, a database program, which can add power. In general, Excel integrates best with databases and any dataset requiring many calculations per workbook.

Understanding Your Main Screen

When you first open Excel in Office 365 or a newer version of Microsoft Office, you’ll see a basic screen. Here are the key features in this view:

A. Basic App Functions: From left to right along this top green banner you’ll find icons to: reopen the Create a Workbook page; save your work; undo the last action performed and display which actions were recorded; redo a step that’s been undone; select which tools appear below.

B. Ribbon: This grey area is called the Ribbon, and contains tools for entering, manipulating, and visualizing data. There are also tabs that focus on specific features. Home is selected by default; click on the Insert, Page Layout, Formulas, Data, Review, or View tab to reveal a set of tools unique to each tab. We’ll cover this more in the “Navigating the Ribbon” section later on.

C. Spreadsheet Work Area: By default the work area is a grid. Along the top are column headers A through Z (and beyond), and along the left side are numbered row headers. Each rectangle in the spreadsheet is called a cell, and they are each named according to their column letter and row number. For example, the cell selected here is A3.

D. Formula Bar: The Formula Bar displays the information contained within a highlighted single cell or range of cells. If in cell A1 you entered “1” as a value, “1” will appear in the Formula Bar. Plain text that you enter in a cell will also appear in the Formula Bar.

There are cases where what you see in the Formula Bar is different than what’s in the cell. For example, let’s say A1 = 1 and A2 = 2. If you create a formula in A3 that equals A1 + A2, then the A3 cell in your worksheet would show “3,” but the Formula Bar would show “=A1+A2.” This is important when you’re trying to move cells to other parts of your worksheet – remember that the display “value” of a cell isn’t necessarily what the cell contains.

That said, other formulas that reference a cell will take into account the current value of a cell. If A4 = A3 + 1, then it would be equal to 4, because it stacks the formula of A3 (A1 + A2) with A4 = A3 + 1. Formulas can reference other formulas multiple times.

E. Search Bar: Simply type the value you want to find to highlight all cells containing that value. It doesn’t have to be an exact match. For instance, if you searched for “o,” a cell labeled “Dogs” would appear among your search results.

F. Sheet Tabs: This is where the different sheets in your workbook can be found. Each sheet gets its own tab, which you can name yourself. These can be useful to separate out data so that one sheet doesn’t get too overwhelming. For example, you might have an annual budget, where each month is a column, and each row is a type of expense. Instead of keeping every single year you track on one sheet and scrolling horizontally, you can make each tab a different year containing 12 months only.

Note that data from different sheets in the same workbook can be referenced for formulas. For example, if you have two sheets, Sheet1 and Sheet2, you could bring Sheet2 data into Sheet1. If you wanted cell A1 in Sheet1 to equal the A1 in Sheet2,  you’d enter this formula into A1: “=Sheet2!A1”. The exclamation mark calls on the previous sheet referenced before locating the data.

G. Viewability Options: The left icon is Normal which shows the worksheet as it appears in the image above, and the right icon is Page Layout, which divides your worksheet into pages resembling how it would look when printed, with the option to add headers. The slider with the “-” and “+” on it is for scale or zoom-level. Drag the slider left or right to zoom in or out.

Navigating the Ribbon

The Home tab is where you manage the formatting and appearance of your sheet, along with some simple formulas you’ll always need.

  1. Copy and Paste Tools: Use these tools to quickly duplicate data and format styles in the spreadsheet. The Copy tool can either copy a selected cell or group of cells, or copy an area of the spreadsheet that you’ll use as a picture in another document. The Cut tool moves the selection of cells to a new destination rather than duplicating it.

The Paste tool can paste anything in your clipboard into the selected cell, and typically retains everything including the value, formula, and format. However, Excel has a wealth of pasting options: you can access these by clicking the down arrow next to the Paste icon. You can paste what you’ve copied as a picture. You can also paste what you’ve copied as values only, so that instead of duplicating the formula of a copied cell, you duplicate the final value shown in the cell.

The Format paintbrush copies everything related to the formatting of a selected cell. When you select a cell and click Format, you can then highlight a whole range of cells, and each one will take on the formatting of the original cell, without changing their values.

  1. Visual Formatting Tools: Many of these tools are similar to those found in Microsoft Word. You can use the formatting tools to change the font, size, and color of typed words, and make them bold, italicized, or underlined. It also has a couple spreadsheet-specific formatting options. You can choose which sides of the cell get additional borders, and their style and thickness. You can also change the highlight color of the entire cell. This is useful for creating visually-appealing borders or differentiating rows or columns on large sheets, or for highlighting a particular cell that you want to accentuate.
  2. Position Formatting Tools: Align cell data to the top, bottom, or middle of the cell. There is also an option for angling the values displayed, which can make it easier to read. The bottom row has familiar options for left, center, and right alignment. There are also indent right and left buttons.
  3. Multi-cell Formatting Features: This section contains two very important features that solve common problems for new Excel users. The first is Wrap Text. Normally, when you enter text into a cell that extends beyond the size of the cell, it spills into the next cell. For example, if you type “Budgeted Items” into A1, some of the word “Items” spills into B1. Then, if you type into B1, you cover up any characters from A1 that extended into B1. The extra text from cell A1 still exists, but now it is hidden. If you don’t want to widen the cells, click the Wrap Text icon on A1 – this will split “Budgeted Items” into two stacked lines instead of one within A1. This makes the entire row taller to accommodate the content. Now, typing into B1 won’t cover up existing text.

The other tool in this section is Merge and Center. There are instances when you may want to combine several cells and have them act as one long cell. For example, you might want a header for an entire table to be clear and easy to read. Select all the cells you want combined, click Merge, and then type your header and format it. Though the default setting for headers is centered text, simply click the drop-down arrow to select different merging and unmerging options.

  1. Numbers-based Format Settings: A drop-down menu has options for number formatting. For example, currency places everything you select into “$0.00” format, and percent turns .5 or ½ into “50%”, date options. These are the basic format options, but you can select More Number Formats from the drop-down menu to get more specialty use cases (different countries’ currencies, or adding the “(xxx)xxx-xxxx” formatting to phone number sequences). Often, you may use these tools on entire columns to make all data in one category behave the same way.
  2. Table or Sheet Formatting: Format as Table and Cell Styles allow you to use presets or customize tables (for example, with alternating row colors and highlighted header bars). Select your data range and choose a style to standardize formatting.

Conditional formatting is a bit more complex. Use the drop-down menu to select from a range of options, like inserting helpful visual icons to represent status or completion, or changing the color of different rows. Most important are the conditional rules, which are created with a simple logic. For example, let’s say you have a column with data in A1 through A3, and A4 holds the sum of these three cells. You could place formatting on A4 with a rule that says “if A4 > 0, then highlight A4 green.” Then, you could add another rule that says “if A4 < 0, then highlight A4 red.” Now you have a quick visual reference where green = a positive number and red = a negative number, which will change based on what you enter into A1, A2, and A3.

  1. Row and Column Formatting Tools: The Insert drop-down menu puts cells, rows, or columns before or after a selected area on the sheet, and Delete removes them. The Format drop-down lets you change the height of rows and the width of columns. It also has options for hiding and unhiding certain sections.
  2. Miscellaneous Tools: Starting at the top left, there’s AutoSum, which allows you to select a swath of cells and place the sum in the cell located right below or directly to the right of the last selected data point. You can use the drop-down to change the function to calculate the average, display the maximum, minimum, or the count of numbers selected.

Use Fill to take a cell’s contents and extend them in any direction for as many cells as you want. If the cell contains a value, Fill will simply copy the value over and over again. If it contains a formula, it will recalculate its relative position for each new cell. If the first cell equals A1+B1, then the next would equal A2+B2, and so on.

The Clear button lets you either clear the value, or just clear cell formatting.

Sort & Filter tools let you choose what to display, and in what order. At the base level, this tool sorts cells containing text from A to Z, and cells containing numbers from lowest to highest. It can also sort by color or icon. Sorting and filtering helps surface only the data you need.

Use the Insert tab to add extra elements to your Excel workbook that go beyond text and colors.

  1. These tools control PivotTables, an important Excel function. Think of PivotTables as “reports,” a quick way to view all your data, analyze trends, and draw conclusions. By selecting at least two rows of data and clicking on PivotTable, you can quickly generate a visually-appealing table. Going through this process launches the PivotTable Builder, which helps you select columns to include, sort them, and drag-and-drop them to quickly construct your table. They can include collapsible rows to make reports interactive and uncluttered. There is also a button for Recommended PivotTables, which can help when you don’t know where to start.

Table builds a simple table that includes any number of columns you select. Rather than placing the table elsewhere on the worksheet, it turns the data into a table on the spot, and applies customizable color formatting.

  1. This section lets you insert visual elements, like picture files, pre-built shapes, and SmartArt. You can add shapes and resize, recolor, and reposition them to create intuitive data sets and reports. SmartArt objects are prebuilt diagrams that you can insert text and information into. They’re great for representing what the data says in another place on your workbook.
  2. These tools are for inserting elements from other Microsoft products, like Bing Maps, pre-built information cards about People (from Microsoft accounts only), and add-ins from their store.
  3. Use these tools to create charts and graphs. Most of them work only if you select one or more data sets (numbers only, with words for headers or categories). Charts and graphs function like you’d expect – just select the data you want to visualize, then select your desired type of visual (bar charts, scatter plots, pie charts, or line graphs). Creating one will bring up formatting options where you can change the color, labels, and more.
  4. Sparklines are more simplistic graphs that can fit in as little as one cell. You can place them next to data for a small, quick visual representation.
  5. Slicers are big lists of buttons that make your data more interactive. You can select a PivotTable you’ve created, and then create a slicer from it – this allows a viewer to click on buttons that correlate to the data they want to filter.
  6. This hyperlink tool allows you to make a cell or table into a clickable link. Once a viewer clicks on the affected cell(s), they’ll be taken to whatever website or intranet site you select.
  7. Recent versions of Excel allow for better collaboration – insert comments on any cell or range of cells to add more context. You can open or close the comments so the worksheet doesn’t get too cluttered.
  8. A Text Box is useful when you’re creating a report and don’t want typed words to behave like cells. It makes it easy to move your text around, rather than cutting and pasting cells (which could potentially mess up the formatting of real data). The next area is for Headers & Footers, which will take you to the page layout view – here you can add headers and footers for the entire page. WordArt, on the other hand, lets you embellish text. Insert Object lets you place entire files (Word documents, PDFs, etc.) into the worksheet.
  9. This section lets you insert Equations and Symbols. Use equations to write a math equation with fractions, variables, and more that you can place in your sheet like a Text Box. For instance, this can be helpful for explaining how a portion of a table was calculated in a report. Symbols, on the other hand, can be inserted directly into cells, and include all non-standard characters from most languages, as well as emojis.

The Page Layout tab has everything you need to change the structural parts of your worksheet, especially for purposes of printing or presenting.

  1. Use these buttons to quickly adjust the visual style of your entire sheet. You can regulate the fonts and colors, and use the Themes section to quickly apply it to every table, PivotTable, and SmartArt element for a clean, well-designed sheet.
  2.  These are print options. You can change the margin for printing, whether you want a vertical or horizontal print alignment, which cells in your sheet you want to print, where you’d like page breaks, and whether it has a background (to place your company name, for example). You can also start giving each page a heading using the Print Titles button, and the order to print each section.
  3. This lets you choose how many pages across and how many pages down you’d like to print.
  4. This section lets you toggle whether the automatic grids appear for working on the sheet and for printing it, along with the row and column headings (A, B, C, 1, 2, 3, etc).

The Formulas tab stores nearly everything related to Excel’s reputation as “complex.” Because this article is intended for beginners, we won’t cover every function is this section thoroughly.

  1. The Insert Function button is useful for those who don’t know all the shorthand. This brings up a side Formula Builder section that describes each function, and you can select the one you want to use.
  2. These buttons divide all the functions by category.

AutoSum works the same as it does in the Home tab.

Recently Used is helpful for bringing up frequently used formulas to save time looking through menus.

Financial includes everything related to currency, values, depreciation, yield, rate, and more.

Logical includes conditional functions, like “IF X THEN Y.”

Text functions help clean, regulate, and analyze plain text cells, such as displaying the character count of a cell (helpful for Twitter posts), combining two different rows via Concatenate, or pulling out numerical values from text entries that aren’t formatted correctly.

Date & Time functions help make meaning out of time-formatted cells, and include entries like “TODAY,” which enters the current date.

Lookup & Reference functions help pull information from different parts of your workbook to save you the trouble of looking for them.

Math & Trig functions are just what they sound like, involving every sort of math discipline you can imagine.

More Functions includes Statistical and Engineering data.

  1. This section contains tagging options. If there’s a range of cells or a table you frequently need to refer to in formulas, you can define its name and tag it here. For example, say you had a column that contained the entire list of products you sell. You could highlight the names in that list and Define Name as “Product List.” Every time you want to refer to that column in a formula, you can simply type “Product List” (rather than finding that collection of data again or memorizing their cell positions).
  2. This contains error checking tools. With Trace Precedents and Trace Dependents, you can see which cells contain formulas that refer to a given cell and vice versa. Show Formulas reveals the formulas inside all cells, rather than their display values. Error Checking automatically finds broken links and other issues with your spreadsheet.
  3. Should you have a large sheet with a massive series of interconnected formulas, tables and cells, you can use this section to trigger calculations, and also to choose which types of data don’t run. A good example is a mortgage or asset depreciation sheet.

The Data tab is for performing more complex data analysis than most beginners will need –

  1. These are database import tools, allowing you to import data from any web, file, or server-based database.
  2. This section helps you fix database connections, refresh data, and adjust properties.
  3. These are Sort and Filter options similar to those for data you have within your sheet, applied to data feeds. They’re especially crucial here as a database is sure to have more data than you can or care to use.
  4. These are data manipulation tools. You can take a single long string, like those separated by commas or spaces, and divide them into columns with Text to Columns. You can seek and remove duplicates, consolidate cells, and validate whether data meets certain criteria to assess its accuracy. What-if Analysis helps you fill in gaps with incomplete data using existing data and trends to determine likely outcomes for new scenarios.
  5. These tools help you manage how much data you have to deal with at once and group them by whatever criteria you deem necessary. It’s similar to sorting, but you can choose any range of columns or rows and make them collapsible, each with their own label. Use Subtotal to create automatic calculations along a data set by different categories, which is helpful for financial sheets.

The Review tab is part of the Ribbon that helps with sharing and accuracy checks.

  1. These are simple text-based checks (like in Word) that allow you to locate cells with spelling errors, or find more appropriate words via the Thesaurus.
  1. Check Accessibility pulls up errors that can make it difficult to access the data in other programs, or just for reading purposes. It might find that your sheet is missing alt text, or that you’re using defaults for sheet names that can make navigation less intuitive.
  2. The commenting tools allow collaborators to “talk” to each other within the sheet.
  3. Protecting and sharing tools allow you to invite collaborators and restrict access to certain parts of the sheet. You can manually assign different levels of access – for example, you might allow a contractor to edit just the cells related to the hours they worked, but not the cells that calculate their pay. As with Word, sharing a sheet with Tracked Changes means you can see everything that’s been done to the sheet.
  4. When you’ve shared a workbook, you can restrict permissions later on using this button and selecting individual contributors.

Use the tools in the View tab to change settings related to what you can see or do.

  1. This is your basic view where you can see your default sheet view, how it’ll look when printing, and in custom ways you set yourself.
  2. Use these buttons to choose whether you want to see the grids, headings, formula bar, and ruler.
  3. This is another way to control zooming in and out of cells.
  4. Freeze Pane controls are an important part of making a usable spreadsheet. Using these tools, you can freeze a number of rows and/or columns while you scroll around. For example, if the first row had all your column headings and remained frozen, you’ll always know which column you are looking at as you scroll down.
  5. Macros are a way of automating processes in Excel. It is far beyond Excel 101, however.

How to Create a Simple Budget Spreadsheet in Excel

Now that you’ve learned about the tools in Excel, let’s practice making our own spreadsheet from scratch. This guide will cover basics, with a few intermediate techniques to get you more comfortable with spreadsheets.

Step 1: Create a Workbook

When you open Excel, you’ll be presented with a screen like this. Create a new workbook by clicking the New tab on the sidebar. The Recent tab below that will bring up any workbooks you’ve recently opened. Below that is the Shared tab, which shows workbooks that other Excel users have sent to you directly through the app (we won’t focus on that right now). The final tab is Open, which opens a file browser so you can select an existing workbook.

On the New tab, you can see a number of templates available, which can help you jump straight into making specific types of spreadsheets, like budgets and task lists. In this example, however, we’re going to build a spreadsheet from scratch. Click Blank Workbook on the top left corner, then click Create.

Step 2: Plan Your Needed Data

Before you can create any kind of spreadsheet, you need to plan what it’ll include so you can structure and format it accordingly. While it is possible to change the spreadsheet structure later on, the more data you’ve added, the more inconvenient it becomes. Plus, moving around entire rows and columns increases the chances of accidentally changing formulas. In this example, we’re making a monthly budget, so we’ll use a monthly time stamp. As we explored above, we can use other sheets in this workbook to track other time increments, like weeks or years. Of course we want to add all of our different expenses together, but we should also think of categories for comparison. We could have one for necessities, and one for luxuries. We’ll need subtotal rows, along with a comparison of budget to actual spending. At the end, we’ll also want to easily compare the different parts of the budget together.

Now we know the elements we need, and can organize them accordingly.

Step 3: Create Headings

Since we know we want to compare month to month, we should use months as our column headings – horizontally is usually best for time comparison. Since we know we’ll also have categories of spending to label and sublabel, we should leave the A and B columns open, and start at cell C1.

Here’s a useful trick: if you type a number or timestamp with a logical next entry, you can click the lower right corner of that cell and drag in any direction to autofill the rest of the sequence as far as you want. For this example, after typing “January” in C1, you can drag it across to N1 and watch it fill in the rest of the months. To create the diagonal names in the screenshot, navigate to the Home tab and find and click the formatting option with a diagonal rising appearance. This makes the headings stand out without changing the column width. We’ll also need an area on the sheet where we can get row totals for more useful data, so create the heading Total in cell O1.

Step 4: Label the Rows

Create three blocks of entries on column B. Name the first block Necessities, which will include everything you see, and end with a subtotal. Name the second block Luxuries and include a few categories; also end with a subtotal. The last block will have our Total, the budget to compare it to, and the difference between the two, which we’ll call Saved (this amount represents the difference between the expected and actual spent). To makes them stand out, use the Paint Bucket tool and select a color (grey in this example).

For column A, we’ll create labels that clearly line up with our grey blocks, and position the writing vertically so it doesn’t take too much space. To make the width of the column smaller, grab the right edge of the A column and drag it to the left. To combine all the cells for our category labels, highlight A2 through A7, and Merge & Center. To get the writing vertical, navigate to the Home tab, find the formatting option and click vertical writing. Finally, choose the height alignment as centered so the vertical text will appear in the middle. Repeat this with cells A9 through A13.

Step 5: Add Boundaries

Add boundaries to the spreadsheet using the icon in the above graphic. Select each collection of cells, and don’t adjust the spaces between the grey block groupings. Click All Borders to draw distinct grids. Now, make the outer boundary of each block thicker by selecting the entire area and choosing Thick Box Border. Finally, do the same around the inner row of each box labeled Subtotal, to make these visually distinct. Apply a Thick Box Border to Column O, Total, and leave spaces between each row grouping. All of this improves spreadsheet readability.

Step 6: Create a Results Table

Use the side of your nicely-formatted spreadsheet to create the outlines of a simple table which will contain your main results. This information will assist you in creating a chart, later. Give it an appropriate label, and label its rows for the total from Necessities, the total from Luxuries, and the total Saved for the year.

Step 7: Format and Write Formulas

This is where the spreadsheet gets a lot more powerful. It involves a series of steps:

  1. First, select every cell that will contain a number, underneath columns C through O, and also in the table for Portion of Budget. Now click the “$” on the keyboard to format the cells with the standard dollar format with two decimals for cents.
  2. Select cells C2 through C7 and click AutoSum. This creates a formula that adds everything in this column, and places the sum into C7 (the last selected cell).
  3. Use your cursor to grab the bottom right corner of cell C7, and drag it to the right toward column O. This will duplicate your formula down the entire Subtotal row. This means that while C7 = sum of C2 through C6, D7 = sum of D2 through D6, and so on. Repeat the process for Luxuries.
  4. For the Total, Budget, and Saved area, the process is a little different. Click cell C15, and enter this formula: =C7+C13. This totals the two subtotals. Like you did with the other formulas, drag and duplicate it across to column O.
  5. Click cell C17, and input this formula: =C16-C15. This will make the Saved row equal to the difference between Budget and Total.
  6. Finally, add formulas to each empty cell of your Portion of Budget table. Q3=O7, which will bring the yearly subtotal of all Necessities items to the Necessities part of this table. Do the same for the Luxuries table annual subtotal and the Saved annual total.

Step 8: Script Conditional Formatting

Before entering data, there’s one more bit of set up: conditional formatting. To do this, click the drop down arrow on Conditional Formatting and click Manage Rules. Next, click + to add a rule, which takes you to a new popup menu. Click Style: Classic. Then choose Format only cells that contain, and click Cell Value greater than 0. Format this with a standard option, green fill with dark green text. Now you’ll be returned to the Manage Rules section, where you can select which range of cells it applies to. Choose C17 through O17 to have it affect the Saved row only.

Now repeat the steps, but this time Format only cells that contain the Cell Value equal to or less than 0. Use the standard option light red fill with dark red text, and apply it to the same range of cells.

Now you have a conditional format for all the final calculated Saved row entries. If it’s greater than 0, it gets marked green, and if it’s 0 or less, it gets marked red. When your data is entered, you can instantly see which months you saved money in, and which you didn’t.

 Step 9: Enter Data and Watch the Calculations

First, enter an assumed budget, and copy it across the Budget row by dragging it from its bottom right corner. In this case, the assumed budget is $3,000.00.

Next, enter your data for each month of last year, totaled from receipts and bank statements, and categorized accordingly. Now for the magic of spreadsheets: as you enter each bit of data, you’ll see your Subtotals, Totals, Saved rows filling in, as well as the Portion of Budget table – all calculating and updating in real-time.

Step 10: Create a Pie Chart

Create a simple visualization. Select the entire Portion of Budget table, and then click the Insert tab on the Ribbon, and choose Pie Chart. Because of how the table is set up, the chart will be titled Portion of Budget, and each dollar amount will appear as a percent of the pie chart with the necessary label. Don’t forget to click Save on the top left of the Excel app.

Session 3

SO 3

 

Contribute to the quality of the management information system.

Learning Outcomes

(Assessment Criteria)

Potential improvements to the MIS are identified and considered for their impact on the quality of the system and any interrelated systems.

Suggestions for changes are supported by a clear rationale as to how they could improve the quality of the system.

The reliability of assumptions and judgements made is assessed and clearly stated.

The benefits and costs of all changes are described accurately.

Suggestions are presented clearly and in a way that assists people to understand and act on them.

Contribute to the quality of the management information system.

Characteristics of Management Information System (MIS)

Following are the key characteristics of MIS:

  • System approach:

MIS follows the system approach, which implies a step by step approach to the study of system and its performance in the light of the objective for which it has been constituted. It means taking an inclusive view at sub-systems to operate within an organization.

  • Management-oriented:

The management-oriented characteristic of MIS implies that top-down approach needs to be followed for designing MIS. A top-down method says the initiation of system development determines management requirements as well as business goals. MIS implies the management dynamically to the system development towards the completion of management decision.

  • As per requirements:

The design and development of MIS should be as per the information required by the managers. The required design and development information is at different levels, viz., strategic planning, management control and operational control. It means MIS should cater to the specific needs of managers in the hierarchy of an organization.

  • Future-oriented:

The design and development of MIS should also be future purpose so that the system is not restricted to provide only the past information.

  • Integrated:

A complete MIS is a combination of its multiple sub-components to provide the relevant information to take out a useful decision. An integrated system, which blends information from several operational areas, is a necessary characteristic of MIS.

  • Common data flows:

This concept supports numerous basic views of system analysis such as avoiding duplication, combining similar functions and simplifying operations. The expansion of common data flow is a cost-effectively and logical concept.

  • Long-term planning:

MIS should always develop as a long-term planning because it involves logical planning to get success of an organization. While developing MIS, the analyst should keep future oriented analysis and needs of the company in mind.

  • Relevant connection of sub-system planning:

The MIS development should be decomposing into its related sub-systems. These sub-systems must be meaningful with proper planning.

  • Central database:

it contains data in tabular form. The data base is responsible to operations like insertion, deletion, updating of records. This database covers information related to inventory, personnel, vendors, customers, etc. the data stored in the database.

Advantage & Disadvantages of MIS

Modern businesses have been leveraging management information systems (MIS) to manage, order, organize and manipulate the gigabytes and masses of information generated for various purposes. MIS helps businesses optimize business processes, address information needs of employees and various stakeholders and take informed strategic decisions. However, budget allocation and monitoring issues can affect the efficacy of MIS. It has its advantages and disadvantages depending on organizational deployment and usage.

ADVANTAGES

An MIS provides the following advantages:

  • It Facilitates planning: MIS improves the quality of plants by providing relevant information for sound decision – making. Due to increase in the size and complexity of organizations, managers have lost personal contact with the scene of operations.
  • In Minimizes information overload: MIS change the larger amount of data in to summarize form and there by avoids the confusion which may arise when managers are flooded with detailed facts.
  • MIS Encourages Decentralization: Decentralization of authority is possibly when there is a system for monitoring operations at lower levels. MIS is successfully used for measuring performance and making necessary change in the organizational plans and procedures.
  • It brings Co-ordination: MIS facilities integration of specialized activities by keeping each department aware of the problem and requirements of other departments. It connects all decision centers in the organization.
  • It makes control easier: MIS serves as a link between managerial planning and control. It improves the ability of management to evaluate and improve performance. The used computers has increased the data processing and storage capabilities and reduced the cost.
  • MIS assembles, process, stores, Retrieves, evaluates and disseminates the information.

DISADVANTAGES:

  • Highly sensitive requires constant monitoring.
  • Budgeting of MIS extremely difficult.
  • Quality of outputs governed by quality of inputs.
  • Lack of flexibility to update itself.
  • Effectiveness decreases due to frequent changes in top management

Characteristics of a Good Management Information System

The purpose of a management information system, often referred to simply as MIS, is to help executives of an organization make decisions that advance the organization’s goals. An effective MIS assembles data available from company operations, external inputs and past activities into information that shows what the company has achieved in key areas of interest, and what is required for further progress. The most important characteristics of an MIS are those that give decision-makers confidence that their actions will have the desired consequences.

Relevance of Information

The information a manager receives from an MIS has to relate to the decisions the manager has to make. An effective MIS takes data that originates in the areas of activity that concern the manager at any given time, and organizes it into forms that are meaningful for making decisions. If a manager has to make pricing decisions, for example, an MIS may take sales data from the past five years, and display sales volume and profit projections for various pricing scenarios.

Accuracy and Reliability of Information

A key measure of the effectiveness of an MIS is the accuracy and reliability of its information. The accuracy of the data it uses and the calculations it applies generally determine the effectiveness of the resulting information. However, not all data needs to be equally accurate.

For example, payroll information needs to be precise, but employee hours spent on a given task can be based on reasonable estimates. The sources of the data determine whether the information is reliable. Historical performance is often part of the input for an MIS, and also serves as a good measure of the accuracy and reliability of its output.

Usefulness of Information

The information a manager receives from an MIS may be relevant and accurate, but it is only useful if it helps him with the particular decisions he has to make. For example, if a manager has to make decisions on which employees to cut due to staff reductions, information on resulting cost savings is relevant, but information on the performance of the employees in question is more useful. The MIS has to make useful information easily accessible.

Timeliness of Information

MIS output must be current. Management has to make decisions about the future of the organization based on data from the present, even when evaluating trends. The more recent the data, the more these decisions will reflect present reality and correctly anticipate their effects on the company. When the collection and processing of data delays its availability, the MIS must take into consideration its potential inaccuracies due to age and present the resulting information accordingly, with possible ranges of error.

Data that is evaluated in a very short time frame can be considered real-time information. For example, information on an increase in product defects may be flagged for instant management attention.

Completeness of Information

An effective MIS presents all the most relevant and useful information for a particular decision. If some information is not available due to missing data, it highlights the gaps and either displays possible scenarios or presents possible consequences resulting from the missing data. Management can either add the missing data or make the appropriate decisions aware of the missing information. An incomplete or partial presentation of information can lead to decisions that don’t have the anticipated effects.

 

End

Add Your Heading Text Here

Scroll to Top