1. Explain Excel and its advantages .
MS Excel is a Microsoft Office application developed by Microsoft. The Excel Spreadsheet Software supports multiple operating systems such as Windows, Mac OS, Android, Ios, and iPad OS. It is used to perform calculations and computations using various functions and formulas present in Excel. Users can store and analyze the data whenever they needed.
Advantages of Using MS Excel
The advantages of using MS Excel are as follows:
Data Storage: MS Excel is widely used in many organizations as a large amount of data is stored in Excel. The user can edit and retrieve the data whenever needed. Hence filtering the data is a quick and easy process.
Data Recovery: Data Recovery is done quickly in Excel. If data is written on paper, it isn't easy to recover and filter it. But it is easy to find and recover the data in Excel Spreadsheet.
Mathematical Calculations: Excel consists of a variety of formulas that are used to perform mathematical calculations. Based on the data and requirements, the formula is modified.
Data Privacy: Data security and privacy is ensured in Excel. The Personal Computer and laptop are secured with a password; hence data is protected.
All data in one place: In earlier days, the records and data were maintained in separate files, which were difficult to take and time-consuming process. In Excelthe data are stored in one Excel file, which is used to analyze the data whenever needed.
Organized and clear View: The data is stored as a table in MS Excel. Hence the data looks organized and clear to understand.
2. Explain different excel functions and formulas .
In Microsoft Excel, a formula is an expression that operates on values in a range of cells. These formulas return a result, even when it is an error. Excel formulas enable you to perform calculations such as addition, subtraction, multiplication, and division. In addition to these, you can find out averages and calculate percentages in excel for a range of cells, manipulate date and time values, and do a lot more.
Formulas in Excel: An Overview
- Choose a cell.
- To enter an equal sign, click the cell and type =.
- Enter the address of a cell in the selected cell or select a cell from the list.
- You need to enter an operator.
- Enter the address of the next cell in the selected cell.
- Press Enter.
There are plenty of Excel formulas and functions depending on what kind of operation you want to perform on the dataset. We will look into the formulas and functions on mathematical operations, character-text functions, data and time, sumif-countif, and few lookup functions.
1. SUM
The SUM() function, as the name suggests, gives the total of the selected range of cell values. It performs the mathematical operation which is addition. Here’s an example of it below:
=SUM(C2:C4)
2. AVERAGE
The AVERAGE() function focuses on calculating the average of the selected range of cell values. As seen from the below example, to find the avg of the total sales, you have to simply type in:
=AVERAGE(C2, C3, C4)
3. COUNT
The function COUNT() counts the total number of cells in a range that contains a number. It does not include the cell, which is blank, and the ones that hold data in any other format apart from numeric.
=COUNT(C1:C4)
4. SUBTOTAL
Moving ahead, let’s now understand how the subtotal function works. The SUBTOTAL() function returns the subtotal in a database. Depending on what you want, you can select either average, count, sum, min, max, min, and others. Let’s have a look at two such examples.
In the example above, we have performed the subtotal calculation on cells ranging from A2 to A4. As you can see, the function used is
=SUBTOTAL(1, A2: A4)
In the subtotal list “1” refers to average. Hence, the above function will give the average of A2: A4 and the answer to it is 11, which is stored in C5. Similarly,
“=SUBTOTAL(4, A2: A4)”
This selects the cell with the maximum value from A2 to A4, which is 12. Incorporating “4” in the function provides the maximum result.
5. MODULUS
The MOD() function works on returning the remainder when a particular number is divided by a divisor. Let’s now have a look at the examples below for better understanding.
In the first example, we have divided 10 by 3. The remainder is calculated using the function
=MOD(A2,3)
6. POWER
The function “Power()” returns the result of a number raised to a certain power. Let’s have a look at the examples shown below:
As you can see above, to find the power of 10 stored in A2 raised to 3, we have to type:
=POWER(A2,3)
7. CEILING
Next, we have the ceiling function. The CEILING() function rounds a number up to its nearest multiple of significance.
=CEILING(A2,1)
8. FLOOR
Contrary to the Ceiling function, the floor function rounds a number down to the nearest multiple of significance.
FLOOR(A2,1)
9. CONCATENATE
This function merges or joins several text strings into one text string. Given below are the different ways to perform this function.
In this example, we have operated with the syntax:
=CONCATENATE(A25, " ", B25)
10. LEN
The function LEN() returns the total number of characters in a string. So, it will count the overall characters, including spaces and special characters. Given below is an example of the Len function.
3. What are different artificial intelligence tools used in excel?
In Excel, an AI tool (Artificial Intelligence tool) refers to an add-in or extension that leverages artificial intelligence to enhance your spreadsheet experience. These tools can automate tasks, improve data analysis, and generally make you more productive
Best Free AI Tool in Excel
1. Power Query
A robust AI-powered data handling tool integral to Microsoft Excel and Power BI. It lets users quickly clean, convert, and combine data from various sources.
2. Sheet+
An AI-powered application that makes numbers in Google Sheets and Excel ten times easier to write. It enables AI to handle tedious formula writing tasks.
3. Ideas
Excel offers tailored suggestions to create professional documents, presentations, and spreadsheets. In Excel, Ideas swiftly identifies trends and patterns in data sets to help customers analyze data in a fraction of second.
4. New Data Types
Excel is leveling up with AI that recognizes real-world concepts like Stocks and Geography. This new capability transforms plain text into interactive entities packed with info.
5. Data From Picture
You can snap a photo of a printed data table using your Android device. Then, with Insert Data from Picture, convert it into an Excel spreadsheet instantly.
4. Explain excel interface tabs and its function
Microsoft Excel organizes its features into tabs located at the top of the window, collectively known as the Ribbon. Each tab groups related functions and tools. Here's an overview of the main tabs and their functions:
1. File Tab
Provides access to basic file management tasks.
Functions:
- New: Create a new workbook.
- Open: Open existing workbooks.
- Save/Save As: Save the current workbook.
- Print: Print the workbook or preview before printing.
- Share: Share the workbook via email or cloud.
2. Home Tab
Contains the most commonly used tools for formatting and data management.
Functions:
- Clipboard: Copy, cut, paste.
- Font: Change font type, size, and color.
- Alignment: Align text and adjust cell text orientation.
- Number: Format numbers (currency, percentage, etc.).
- Styles: Apply pre-designed cell styles and conditional formatting.
- Editing: Sort, filter, and find data.
3. Insert Tab
Used to insert objects into the workbook.
Functions:
- Tables: Insert a table or pivot table.
- Illustrations: Add pictures, shapes, or SmartArt.
- Charts: Insert various chart types (line, bar, pie, etc.).
- Text: Add text boxes, headers, and footers.
4. Page Layout Tab
Focuses on workbook appearance and print setup.
Functions:
- Themes: Change workbook themes and colors.
- Page Setup: Adjust margins, orientation, and size.
- Scale to Fit: Resize content for printing.
- Sheet Options: Show/hide gridlines or headings.
5. Formulas Tab
Contains tools for working with formulas and functions.
Functions:
- Function Library: Insert built-in functions like SUM, VLOOKUP, etc.
- Defined Names: Manage named ranges in the workbook.
- Formula Auditing: Trace dependencies and evaluate formulas.
6. Data Tab
Helps in managing and analyzing data.
Functions:
- Get & Transform Data: Import data from external sources.
- Sort & Filter: Organize and filter data.
- Data Tools: Remove duplicates, group data, or validate entries.
- Forecast: Perform data analysis like trend forecasting.
7. Review Tab
Used for reviewing and collaborating on workbooks.
Functions:
- Proofing: Check spelling and grammar.
- Comments: Add, edit, and delete comments.
- Protect: Protect sheets or workbooks with passwords.
8. View Tab
Controls the way the workbook is displayed.
Functions:
- Workbook Views: Switch between Normal, Page Layout, and Page Break views.
- Show: Display or hide gridlines, headings, and rulers.
- Zoom: Adjust the zoom level.
- Window: Arrange, freeze panes, or view multiple windows.
9. Help Tab
Provides assistance and tutorials for Excel.
Functions:
- Help: Search for features or troubleshooting steps.
- Training: Access training resources.
10. Developer Tab (Optional)
Used by advanced users for customizations.
Functions:
- Code: Work with macros and VBA scripts.
- Controls: Insert form controls.
- Add-ins: Manage Excel add-ins.
- Each tab is designed to make it easy to perform specific tasks efficiently.
5. Explain Formatting in excel table rows and layout.
- Select the cell(s) you want to modify.
- On the Home tab, click the drop-down arrow next to the Font Size command, then select the desired font size. In our example, we will choose 24 to make the text larger.
- The text will change to the selected font size.
- Select the cell(s) you want to modify.
- On the Home tab, click the drop-down arrow next to the Font command, then select the desired font. In our example, we'll choose Century Gothic.
- The text will change to the selected font.
- Select the cell(s) you want to modify.
- On the Home tab, click the drop-down arrow next to the Font Color command, then select the desired font color. In our example, we'll choose Green.
- The text will change to the selected font color.
- Select the cell(s) you want to modify.
- Click the Bold (B), Italic (I), or Underline (U) command on the Home tab. In our example, we'll make the selected cells bold.
- The selected style will be applied to the text.
- Select the cell(s) you want to modify.
- On the Home tab, click the drop-down arrow next to the Fill Color command, then select the fill color you want to use. In our example, we'll choose a dark gray.
- The selected fill color will appear in the selected cells. We've also changed the font color to white to make it more readable with this dark fill color.
- Select the cell(s) you want to modify.
- On the Home tab, click the drop-down arrow next to the Borders command, then select the border style you want to use. In our example, we'll choose to display All Borders.
- The selected border style will appear.
- In our example, we'll apply a new cell style to our existing title and header cells.
- Select the cell(s) you want to modify.
- Click the Cell Styles command on the Home tab, then choose the desired style from the drop-down menu.
- The selected cell style will appear.
- Rate = It is the interest rate/period
- Nper = Number of periods
- = Payment/period
- PV = Present Value
- = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)
FVSCHEDULE = (Principal, Schedule)
- Principal = Principal is the present value of a particular investment
- Schedule = A series of interest rate put together (in case of excel, we will use different boxes and select the range)
- Rate = It is the interest rate/period
- Nper = Number of periods
- = Payment/period
- FV = Future Value
- = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)
- Rate = Discount rate for a period
- Value 1, , … = Positive or negative cash flows
- Here, negative values would be considered as payments, and positive values would be treated as inflows.
- Rate = Discount rate for a period
- Values = Positive or negative cash flows (an array of values)
- Dates = Specific dates (an array of dates)
- Rate = It is the interest rate/period
- Nper = Number of periods
- PV = Present Value
- = An optional argument which is about the future value of a loan (if nothing is mentioned, FV is considered as “0”)
- = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)
- Rate = It is the interest rate/period
- Per = The period for which the principal is to be calculated
- Nper = Number of periods
- PV = Present Value
- An optional argument which is about the future value of a loan (if nothing is mentioned, FV is considered as “0”)
- = When the payment is made (if nothing is mentioned, it’s assumed that the payment has been made at the end of the period)
- Values = Positive or negative cash flows (an array of values) = An assumption of what you think IRR should be
- Values = Positive or negative cash flows (an array of values)
- Finance rate = Interest rate paid for the money used in cash flows
- Reinvestment rate = Interest rate paid for reinvestment of cash flows
- Values = Positive or negative cash flows (an array of values)
- Dates = Specific dates (an array of dates) = An assumption of what you think IRR should be
8. Explain how to calculate the depreciation manually and using excel.
Manually Calculating Depreciation
1. Straight-Line Method
Formula:
Depreciation Expense = (Cost of Asset - Salvage Value) / Useful Life (in years)
Example:
Cost of Asset: ₹50,000
Salvage Value: ₹5,000
Useful Life: 5 years
Depreciation Expense: (50,000 - 5,000) / 5 = ₹9,000 per year.
2. Declining Balance Method
Formula:
Depreciation Expense = Book Value at Start of Year × Depreciation Rate
Example:
Cost of Asset: ₹50,000
Depreciation Rate: 20%
Salvage Value: ₹5,000
Year 1: ₹50,000 × 20% = ₹10,000
Book Value after Year 1: ₹50,000 - ₹10,000 = ₹40,000.
3. Units of Production Method
Formula:
Depreciation Expense = ((Cost - Salvage Value) / Total Estimated Units) × Units Produced in the Period
Example:
Cost: ₹1,00,000
Salvage Value: ₹10,000
Estimated Production: 10,000 units
Units Produced in Year 1: 2,000
Depreciation per Unit: (1,00,000 - 10,000) / 10,000 = ₹9.
Depreciation for Year 1: ₹9 × 2,000 = ₹18,000.
4. Sum-of-the-Years-Digits Method
Formula:
Depreciation Expense = (Remaining Life / Sum of the Years) × (Cost - Salvage Value)
Example:
Cost: ₹60,000
Salvage Value: ₹10,000
Useful Life: 5 years
Sum of the Years = 5 + 4 + 3 + 2 + 1 = 15.
Year 1: (5 / 15) × (60,000 - 10,000) = ₹16,667.
Using Excel to Calculate Depreciation
1. Straight-Line Method in Excel
Formula in Excel:
=(Cost - Salvage_Value) / Useful_Life
Steps:
- Input the Cost, Salvage Value, and Useful Life into separate cells.
- Use the formula to calculate annual depreciation.
- Drag the formula down for subsequent years.
2. Declining Balance Method in Excel
Formula in Excel:
=DB(Cost, Salvage_Value, Useful_Life, Period)
3. Units of Production Method in Excel
Formula in Excel:
=((Cost - Salvage_Value) / Total_Units) × Units_Produced
4. Sum-of-the-Years-Digits in Excel
Formula:
(Remaining_Life / Sum_of_Years) × (Cost - Salvage_Value)
Summary of Excel Functions
| Method | Excel Formula Example |
|---|---|
| Straight-Line | =(Cost - Salvage) / Life |
| Declining Balance | =DB(Cost, Salvage, Life, Period) |
| Units of Production | =((Cost - Salvage) / Total_Units) × Units_Produced |
| Sum-of-the-Years-Digits | Calculated manually |
9. Write a short note on Straight line depreciation
With the straight line depreciation method, the value of an asset is reduced uniformly over each period until it reaches its salvage value. Straight line depreciation is the most commonly used and straightforward depreciation method for allocating the cost of a capital asset. It is calculated by simply dividing the cost of an asset, less its salvage value, by the useful life of the asset.
The straight line depreciation formula for an asset is as follows:
Where:
Cost of the asset is the purchase price of the asset
Salvage value is the value of the asset at the end of its useful life
Useful life of asset represents the number of periods/years in which the asset is expected to be used by the company
Additionally, the straight line depreciation rate can be calculated as follows:
Depreciation charge per year = (net book value – residual value) x depreciation factor
.png)
0 Comments