Computer Applications in Management
Tutorial No. 1 : About MS Excel
Introduction
About MS Excel :
Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications (VBA).
DBMS Concepts
Data : Data may be any fact related to any object/entity which is in consideration. For example, your name, age etc. are the examples of some data related to you. A picture, image, file, pdf, etc can also be considered as data.Data may be simple and at the same time can be unorganized.Data is always interpreted, by a human or machine, to derive a meaning.
Information : Information is a collection of data which have some meaning.Information is processed, structured, or presented in a given context to make it meaningful and useful.It is processed data which includes data that may have some link, context, relevance, and purpose. It also involves manipulation of raw data.Information cerates some meaning and improves the reliability of the data.e.g. the Age of student is 18 years
Database : A database is an organized collection of structured information, or data, typically stored electronically in a computer system. e.g. Database of a School or College contains the data and/or information about the student,attendance and exaimnation details of the student.
Database File : Database file is a file which stores data and objects such as tables, indexes, stored procedures, and views.
Field : A database field which can store a single piece of information. e.g. 18 value,stored in database.
Record : A database record is a set of fields. e.g. Personal Information of a single student in the database.
DBMS(Database Management Systems) : A DBMS is computerized system which manages the database according to the user's requirement.
Primary Key : is a field or set of fields which uniquely indentifies the value from the given table/relation.There are two properties of Primary Key :1) should not be null and 2) should be unique.e.g. Student ID is the paimary key in Student information table because in ideal condition and at a time a student has always unique ID in the college/school.
Foreign Key : : is a field which references the primary key of some othere table/relation.There are two properties of Primary Key :1) It may be duplicated or null and 2) The possible value should be in range from the Primary Key value only.e.g. StudentID in student attendance table. where StudentID field references the StudentID field of StudentInfor table.
Decision Making using Solver
About MS Excel Solver :
MSExcel Solver is a special set of commands. It is primarily used for simulation and optimization of various business and engineering models.
The Excel Solver add-in is useful for solving linear programming problems, like linear optimization problems, and it is sometimes called a linear programming solver. In addition to that, it can handle smooth nonlinear and non-smooth problems.
Solver is really helpful when dealing with all kinds of optimization problems where you require to make the best decision. For example, it can help you to determine the return of investment, choose the optimal budget for your advertising campaign, make the best work schedule for your employees for efficient work, minimize the delivery costs, and so on.
How to add a solver ? :
File -> Options -> Add ins -> Solver Add In
In Data Tab you will find solver symbol
How to use Solver using Excel ? :
Before running the Excel Solver add-in, define the model you want to solve in a excel worksheet. In this example, we will try to find a solution for the following simple optimization problem.
Supposing, you are the owner of a car workshop and you are planning to provide a paint service to your clients. For this, you need to buy a new equipment that costs Rs. 5,00,000, which should be paid by installments within 12 months.
Goal: Calculate the minimal cost per service that will let you pay for the new equipment within the specified timeframe.
Perform following steps :
Run Excel Solver
On the Data tab, in the Analysis group, click the Solver button.
Define the problem
The Solver Parameters window will open where you have to set up the 3 primary components:
Objective :
The Objective cell is the cell containing a formula that represents the objective, or goal, of the problem. The objective can be to maximize, minimize, or achieve some target value.
In this example, the objective cell is C9, which calculates the payment term using the formula =C5/(C6*C7)and the result of the formula should be equal to 12.
Variable Cells :
Variable cells are cells that contain variable data that can be changed to achieve the objective mentioned above. Excel Solver allows specifying up to 200 variable cells.
In this example, following are the cells
Projected clients per month (C6) that should be less than or equal to 5; and
Cost per service (C7) that we want Excel Solver to calculate.
Constraints :
The Excel Solver Constrains are restrictions or limits of the possible solutions to the problem. To put it differently, constraints are the conditions that must be met.
In the above example following will be the output.
Excel Solver algorithms :
When defining a problem for the Excel Solver, you can choose one of the following methods in the Select a Solving Method dropdown box:
GRG Nonlinear :Generalized Reduced Gradient Nonlinear algorithm is used for problems that are smooth nonlinear, i.e. in which at least one of the constraints is a smooth nonlinear function of the decision variables.
LP Simplex :The Simplex LP Solving method is based the Simplex algorithm created by an American mathematical scientist George Dantzig. It is used for solving so called Linear Programming problems - mathematical models whose requirements are characterized by linear relationships, i.e. consist of a single objective represented by a linear equation that must be maximized or minimized.
Evolutionary :It is used for non-smooth problems, which are the most difficult type of optimization problems to solve because some of the functions are non-smooth or even discontinuous, and therefore it's difficult to determine the direction in which a function is increasing or decreasing.
Statistical Analysis : Co-relation Coefficient, ANOVA, Z-Test etc.
Co-relation Coefficient :
The correlation coefficient (a value between -1 and +1) shows how strongly two variables are related to each other. We can find the correlation coefficient using CORREL function or the Analysis Toolpak add-in in Excel.
A correlation coefficient of +1 indicates a perfect positive correlation. As variable X increases, variable Y increases. As variable X decreases, variable Y decreases.A correlation coefficient of -1 indicates a perfect negative correlation. As variable X increases, variable Z decreases. As variable X decreases, variable Z increases.A correlation coefficient near 0 indicates no correlation.
How to add a solver ? :
File -> Options -> Add ins -> Analysis Tool Pack
In Data Tab you will find symbol
