Report on Computer Application in Business PDF

Preview

Summary

We have discussed several mandatory functions of Exel used in business in this report...

Description

Department of Finance University of Dhaka A Report on “Applications of Excel in Business”

Course Name: Computer Applications in Business Course Code: F-102

Submitted to: Muhammad Kamrul Hasan Lecturer Department of Finance University of Dhaka

Submitted by: Group No: 8 BBA 24th Batch, Section: A Department of Finance University of Dhaka Date of Submission: 09.07.2018

Group Profile

Sl.

Name

ID

1

Nashat Mufrajah Twinkle

24-017

2

Mostafa Ishmam

24-050

3

Parsha Sanjana

24-091

4

Shahriar Hasan

24-188

5

Jamilur Reza Efty

24-194

Remarks

Letter of Transmittal

July 09,2018 Muhammad Kamrul Hasan Lecturer Department of Finance University of Dhaka

Subject: Submission of Course Report on "Application of Excel in Business." Dear Sir, With great pleasure we would like to submit our report on "Application of Depreciation Methods in Business of Bangladesh " as per course requirement. It was our immense pleasure to have such a report. It was wonderful experience to work on this report. As you will see in the following pages that how & in which method the businesses of Bangladesh charge depreciation on their plant asset to run their business flawlessly Therefore, we beg your kind consideration in this regard. We will be very helpful if you accept our report and oblige thereby.

Sincerely, Md. Jamilur Reza Efty On behalf of the group members

Acknowledgement First of all, we would like to express our gratitude from heart to the Beneficent, the Merciful & Almighty Allah for giving us the strength and patience to prepare this report within the scheduled time. Finally, we are especially grateful to our honorable course teacher for giving us valuable suggestions and precious contribution. The knowledge that he provided to us in our course has worked as a beacon while preparing this report. This report is prepared by us only to meet our academic purpose, not for any other reason. Many illustrates and examples were collected from numerous people. We could not get all their permission. We would like to acknowledge them and inform that we do not intend to violate any copyright information.

Contents • • • • •

•

Executive Summery……………………………………………….……. 7 Introduction ……………………………………………………….….... 8 Functions of Excel ……………………………………………………..10 Analysis Tools ………………………………………………………......18 Implementation of Functions and tools …………………………….....24 • AB Bank……………………………………………………….24 • BRAC Bank …………………………………………………..26 • EXIM Bank …………………………………………………..28 • Mercantile Bank …………………………………………….30 • Trust Bank …………………………………………………..32 Conclusion ……………………………………………………………34

Page |8

Executive Summary The course Computer Applications in Business introduced us with Business Information System (BIS) which comprise the analysis and organization of business information through the application of technology. As such bit blends core concepts of management, operations and information system theory with computer science and engineering methods and technologies to manage an organization’s data. As a course Business Information System contributes learners with many things like using different kinds of software and applications, understanding their formation to imply them in our regular activities. Business Information System have created a strong bond between business and information system by opening the of working effectively with efficiency. As this course is about information system in business so it concludes some functions of software to analyze or store data. One of the most significant which is used in this course is Microsoft Excel. It is a spreadsheet software developed by Microsoft Corporation to do calculation more efficiently. In recent time Excel is the most reliable tool for doing business calculations. Generally, Excel includes many common functions that can be used to quickly find the sum, average, count, maximum value and minimum value for a range of cells. Here a function is a predefined formula that performs calculations using specific values in a particular order. According to our assignment we used several functions and tools to solve different types of business problems. We also showed how to use those functions and why using them instead of functions. Here we showed several functions like AND, OR, IF, VLOOKUP, HLOOKUP, AVERAGE, SUM etc. we also showed the uses of different tools like Goal Seek, Regression, Graph, Scenario Analysis etc. For which we took data of five particular company registered on Dhaka Stock Exchange to use them in our assignment. Those companies are AB Bank, BRAC Bank, Mercantile Bank, Trust Bank and EXIM Bank. We are really very grateful to have the data from DSE in completing our project. We believe that these Applications of Excel will give us a good experience and good control over this program in our professional life as well as in Business Information System.

Page |9

Introduction Origin of the study The BBA Program under the Department of Finance offers a course named “Computer Applications in Business (F-102)” which requires submitting a report on a specific topic determined by the course teacher. The report under the headline “Applications of Excel in Business” has been prepared for this purpose.

Importance of this Study The study helps us gain hands on knowledge in understanding the importance of using applications of Excel to improve the efficiency and accuracy making business decisions. Above all, the study has academic implications, which indicate the academicians can develop their knowledge and skill in Excel.

Objectives of the Study Upon completion of this report, we will be able to: •

understand different tools & functions of Excel

•

demonstrate understanding of how to use these tools

•

use appropriate formulas to solve analytical problems

•

use Excel in practical situations

•

Apply Excel in business in professional life

P a g e | 10

Microsoft 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. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has replaced Lotus 1-2-3 as the industry standard for spreadsheets.

Why use Microsoft Excel? Microsoft Excel has the basic features of all spreadsheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations like arithmetic operations. It has a battery of supplied functions to answer statistical, engineering and financial needs. In addition, it can display data as line graphs, histograms and charts, and with a very limited three-dimensional graphical display. It allows sectioning of data to view its dependencies on various factors for different perspectives (using pivot tables and the scenario manager). It has a programming aspect, Visual Basic for Applications allowing the user to employ a wide variety of numerical methods, for example, for solving differential equations of mathematical physics and then reporting the results back to the spreadsheet. It also has a variety of interactive features allowing user interfaces that can completely hide the spreadsheet from the user, so the spreadsheet presents itself as a so-called application, or decision support system (DSS), via a customdesigned user interface, for example, a stock analyzer or in general, as a design tool that asks the user questions and provides answers and reports. In a more elaborate realization, an Excel application can automatically poll external databases and measuring instruments using an update schedule analyze the results, make a Word report or PowerPoint slide show, and e-mail these presentations on a regular basis to a list of participants. Excel was not designed to be used as a database.

P a g e | 11

Functions of Excel Excel includes many common functions that can be used to quickly find the sum, average, count, maximum value and minimum value for a range of cells. Here a function is a predefined formula that performs calculations using specific values in a particular order. According to our assignment we used several functions and tools to solve different types of business problems. We also showed how to use those functions and why using them instead of functions. Here we showed several functions like AND, OR, IF, VLOOKUP, HLOOKUP, AVERAGE, SUM etc.

Function of SUM SUM is the most known function of Excel. It is used for add up two or more data which result shows in a particular cell. In business calculation the function of SUM is used broadly. If we take the example of income statement of a particular company we can see that the company uses function of SUM in adding up total revenue or expenses.

It is very easy to use SUM function in Excel. For a example we have several numbers in worksheet from the cell of B2 to B10 which we want to sum up in B11. For that we have to first select B11 cell and input this formula =SUM(B2:B10) and press enter. Then we

P a g e | 12

will have the result in our desired cell. Here in this formula B2:B10 indicates the cell range which we are going to sum up.

Function of AVERAGE Average is also a most popular function in Excel. We use this function to find average of several related numbers quickly. In business operation we use this function to analyze financial statement of organization.

Here we used AVERAGE function in determining the average marks of 11 students in a particular cell. To get this result firstly we have to select particular cell where we want the result then input the formula =AVERAGE(B2:B12) and press enter. Here B2:B12 indicates the cell range where the data is.

Function of AND AND is a function that is used for determining logical result. By inputting this function, the output returns TRUE is all of the arguments evaluate to true otherwise the output will return FALSE. We use the AND functions to test multiple conditions at the same time, up to 255 conditions. Each logical condition must evaluate to TRUE or FALSE, or be arrays or references that contain logical values. In AND function if Text Values or Empty cells supplied as arguments are ignored. The AND function will return #VALUE if no logical values are found or created during evaluation.

P a g e | 13

Here we have the EPS and NAV of 5 years and their average values in B7 cell. Now we can use AND function to identify the year which EPS and NAV is more than average. To have the result by selecting the cell (D2) in which we want the result and write down the formula =AND(B2>$B$7,C2>$C$7)

Here after writing the AND function we have to input the first logic which is the EPS have to be more than the average EPS and the second logic is the NAV must be more than average NAV. Than by pressing enter we will have the result on that cell and by dragging down the cell we will have the result for the other cells.

P a g e | 14

Here in this worksheet in the year of 2013 the EPS is more than the average value but on that year the NAV is less than average that’s why in the result cell the value shows FALSE. Because in AND function if all logic or arguments fulfills its requirement the result cell will show TRUE as showed in the year of 2015 where the EPS and NAV is more than average.

Function of OR OR function is used to determine if any of the arguments or logic is true or false. By putting this function, the output become TRUE if any of the arguments appear true. In OR function if Text Values or Empty cells supplied as arguments are ignored. The OR function will return #VALUE if no logical values are found or created during evaluation.

To apply this function we took the same EPS and NAV which average value is shown. After that to calculate if there is any year when the EPS or NAV is more than average or not. For that after written =OR we have to input the logic.

P a g e | 15

In this worksheet our logic is to find the year which EPS or NAV is more than average. By pressing inter after written the function the result will be shown in D2 cell. Than drag down the cell to have result of other years.

Though in this worksheet in the year of 2013 the EPS was more than average and the NAV is less than average the results shows TRUE because one of the arguments fulfilled the condition. In 2017 none of them fulfilled the condition that’s why the result shows FALSE.

Comparison between OR and AND function Both function plays very important role in business calculations. They both are logical function. Though they operate relied on logic there is slight and most significant difference between them. In AND function the output shows TRUE if all the arguments or logic

P a g e | 16

fulfills all the conditions or it remain FALSE. On the other hand, in OR function if any of the arguments fulfilled only one condition the output shows TRUE but any of them did not fulfilled the conditions the result will be shown FALSE.

Here in this worksheet we have the used both the function to clarify the difference between these two functions. In 2013 the EPS fulfilled the condition where the NAV didn’t that’s why in AND function the result is shown false because it only shows TRUE if all the conditions became fulfilled. On the other hand in OR function the result shows TRUE because in OR function if any of the condition is fulfilled the result shows TRUE.

Function of IF The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. In this function the user can determine for which result which value should be shown.

By inputting IF function we have to select the cell in which basis the function will operate or input the logic. Than we have put the value for the TRUE result in an inverted comma and then put value for the FALSE result.

P a g e | 17

Here in this worksheet we are going to separate the years into two categories one is more than average which is defined as A category and another category is B category which is less than the average. That’s why we input A under inverted comma to show the category A if the result is TRUE in D2 cell and input “B” if the result is FALSE after completing the function press enter and drag down the cell to have the result in other cells also.

LOOKUP Function It is one of the lookup and reference functions, when you need to look in a single row or column and find a value from the same position in a second row or column. V-LOOKUP: It looks up data that has been formatted by columns. The format of the VLOOKUP is: =VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact match or approximate match – indicate as 0/FALSE or 1/TRUE)

P a g e | 18

H-LOOKUP: It looks up data that has been formatted by rows. The function is same as VLOOKUP.

Here in this worksheet we have used VLOOKUP function to lookup the value of DSEX of a particular date. To lookup the value firstly we have to select and lock the cell where the search value will show than select the entire data where the search value and lookup value will be. Than write the column number in which the lookup value will be or in which column value we want, than write TRUE if we want approximate result and FALSE for exact result.

P a g e | 19

Analysis Tools Microsoft Excel have many analysis tools to help in analyzing data. Users of every sector can use these tools easily. These tools can help to identify more secure investment or more profitable company by analyzing their data with these tools. Sometimes they work as an executive information system to help the manager to take more practical decisions. There are some analysis tools that are broadly used to analyze data, they are Regression, Goal Seek, Scenario Analysis, Pivot Table etc.

Regression Analysis In a statistical modeling, regression analysis is a set of statistical processes for estimating the relationships among variables. Regression analysis is also used to understand which among the independent variables are related to the depended variable, and to explore the forms of these relationships.

To do regression analysis in Excel, first go to data analysis in Data section then find regression from the chart and click OK.

P a g e | 20

Then a box will appear in which we have to select some cells. In “Input Y Range” we have to input the dependent variables or the cell which values are dependent and in “Input X Range” we have to input the independent variables or the cell which values are independent.

P a g e | 21

Here we used the closing price as dependent value and input them in Y Range and DSEX as independent variable and input them in X Range.

There is a range that supplies some basic regression statistics, including the R-square value, the standard error and the number of observations. Below that information, the regression tool supplies analysis of variance (or ANOVA) data, including information about the degrees of freedom, sum of square value, mean square value, the f-value and the significance of F. Beneath the ANOVA information, the regression tool supplies information about the regression line calculated from the data, including the coefficient, standard error, t-stat and probability values for the intercept – as well as the same information for the independent variable, which is the number of ads. Excel also plots out some of the regression data using simple scatter charts. Here, Coefficient: Gives you the least squares estimate. Standard Error: the least squares estimate of the standard error. T Statistic: The T Statistic for the null hypothesis vs. the alternate hypothesis. P Value: Gives you the p-value for the hypothesis test. Lower 95%: The lower boundary for the confidence interval.

P a g e | 22

Upper 95%: The upper boundary for the confidence interval.

Scenario Analysis A scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results. Suppose we have two situations in a income statement, one is the maximum profit and the other one is the minimum profit.

Here we have an income statement of a particular year. Where the net profit after tax is 3019942223tk. If we want to see a maximum profit and a minimum profit by changing the total operating expense. For that firstly we have to select the scenario manager tool from what if analysis under the data ribbon. After clicking Scenario Manager, a new box will appear. By this box we can add, edit or remove scenarios.

P a g e | 23

Firstly, click on add to add new analysis. Then another box will appear where in a name section write the scenario name. And in the changing cell we will select the result cell which result should be changed here in this worksheet we select after that a new box will appear where the new minimum value we want than click add to add another scenario which the maximum value will be. After this the summary option will be available in the box. So, by clicking on summary we will get the summary of the analysis in a new sheet or in a pivot table.

Then after clicking show the result will ...