Spreadsheet

This page contains the NCERT class 12 Accountancy Computerised Accounting SystemChapter 2 Spreadsheet. You can find the exercise questions for Spreadsheet from the NCERT Class 12 Accountancy book on this page.
Buy Class 12 Accountancy Books NOW!
Multiple Choice Questions
Question 1
1. The best way to get started in Excel 2007 is to click the _____.
(a)
View toolbar.
(b)
Home tab.
(c)
Microsoft Office Button ✔
(d)
None of the above.
Answer 1
(c) Microsoft Office Button
This is the starting point for opening an old workbook, creating a new workbook, saving, and printing. It gives access to important file-related commands.
Question 2
2. Which key combination collapses the ribbon?
(a)
[Ctrl]+[F1] ✔
(b)
[Ctrl]+[F3]
(c)
[Ctrl]+[F5]
(d)
[Ctrl]+[F7]
Answer 2
(a) [Ctrl]+[F1]
This shortcut is used to collapse or expand the Ribbon, which helps in creating more working space on the screen.
Question 3
3. Which view shows margins and the rulers?
(a)
Normal
(b)
Page Layout ✔
(c)
Page Setup
(d)
Review
Answer 3
(b) Page Layout
Page Layout view shows the worksheet in a print-oriented form. It displays margins, rulers, and page boundaries, so it is helpful while preparing the worksheet for printing.
Question 4
4. As you type a number in a cell, what mode appears in the status bar?
(a)
Enter mode ✔
(b)
Ready mode
(c)
Edit mode
(d)
Record mode
Answer 4
(a) Enter mode
When data is being entered in a cell, Excel shows Enter mode in the status bar. This indicates that the user is currently typing a new entry.
Question 5
5. Where is the address of the active cell displayed?
(a)
Row heading
(b)
Status bar
(c)
Name Box ✔
(d)
Formula bar
Answer 5
(c) Name Box
The address of the active cell, such as A1 or G8, is displayed in the Name Box. It helps the user identify the exact cell location in the worksheet.
Question 6
6. Which command reverses the last action performed in the worksheet?
(a)
Cut
(b)
Undo ✔
(c)
Redo
(d)
Paste
Answer 6
(b) Undo
The Undo command is used to cancel or reverse the last action performed in the worksheet. It is very useful when a wrong step is done by mistake.
Question 7
7. When navigating in a workbook, which command is used to move to the beginning of the current row?
(a)
[Ctrl]+[Home]
(b)
[Page Up]
(c)
[Home] ✔
(d)
[Ctrl]+[Backspace]
Answer 7
(c) [Home]
The Home key moves the pointer to the beginning of the current row. It is a simple navigation key used for faster movement in a worksheet.
Question 8
8. Which key when pressed displays the access keys?
(a)
[Alt] ✔
(b)
[Ctrl]
(c)
[Shift]
(d)
[Esc]
Answer 8
(a) [Alt]
When the Alt key is pressed, Excel displays access keys on the Ribbon. These keys help the user work quickly through the keyboard without using the mouse.
Question 9
9. Which command allows you to reverse an Undo command?
(a)
Redo ✔
(b)
Repeat
(c)
Reset
(d)
Reverse
Answer 9
(a) Redo
Redo is used to restore an action that was removed using Undo. So, it works as the opposite of Undo for the most recent reversed action.
Question 10
10. Which function results can be displayed in AutoCalculate?
(a)
SUM and AVERAGE ✔
(b)
MAX and LOOK
(c)
LABEL and AVERAGE
(d)
MIN and BLANK
Answer 10
(a) SUM and AVERAGE
AutoCalculate can quickly display useful summary results like SUM and AVERAGE for selected cells. It helps in quick checking of numerical data without writing formulas in the worksheet.
Question 11
11. Which cell alignment is assigned to most values by default?
(a)
Right ✔
(b)
Left
(c)
Centre
(d)
Decimal
Answer 11
(a) Right
Most numeric values are right-aligned by default in Excel. This makes numbers easier to read and compare properly in rows and columns. (Here, the word “values” refers to numeric values.)
Question 12
12. Which function automatically totals a column or row of values?
(a)
TOTAL
(b)
ADD
(c)
SUM ✔
(d)
AVG
Answer 12
(c) SUM
The SUM function is used to add a row or column of values. It is one of the most common and basic spreadsheet functions.
Question 13
13. Which mathematical operator is represented by an asterisk (*)?
(a)
Exponentiation
(b)
Addition
(c)
Subtraction
(d)
Multiplication ✔
Answer 13
(d) Multiplication
In spreadsheet formulas, the asterisk symbol `*` is used for multiplication. It is a standard mathematical operator in Excel formulas.
Question 14
14. Which step completes an entry and moves the pointer to the cell to the right?
(a)
Pressing [Enter]
(b)
Pressing [Tab] ✔
(c)
Pressing [Shift]+[Tab]
(d)
Pressing [Shift]+[Enter]
Answer 14
(b) Pressing [Tab]
Pressing Tab completes the entry and moves the active cell to the right. This is useful when entering data across columns.
Question 15
15. How many blank worksheets are shown when a new workbook is created?
(a)
One
(b)
Two
(c)
Three ✔
(d)
Four
Answer 15
(c) Three
A new workbook shows three blank worksheets by default, usually named Sheet1, Sheet2 and Sheet3.
Answer The Followings Questions
Question 1
1. Define spreadsheet and its salient features?
Answer 1
A spreadsheet is a configuration of rows and columns used to record, calculate and compare numerical or financial data. It is also known as a worksheet. In a spreadsheet, values can be entered directly as basic values, or they can be derived with the help of arithmetic expressions and functions. It is a computer program that allows us to enter, process and analyse data in a systematic manner.
The salient features of a spreadsheet are:
•
It provides a large working area made up of rows, columns and cells.
•
Each cell has a unique cell address such as A1, B5 or G8.
•
It allows entry of numeric, alphanumeric and date values.
•
It supports formulas and functions for automatic calculation.
•
It updates derived values automatically whenever input values are changed.
•
It supports what-if analysis, which helps in studying the effect of changes in one or more input values.
•
It also provides features such as formatting, named ranges, conditional functions, lookup functions and printing options.
Thus, a spreadsheet is a very useful tool for storing data, performing calculations and presenting information in an organised form.
Question 2
2. Write any five advantages using electronic spreadsheet in place of manual spreadsheet.
Answer 2
Electronic spreadsheet has many advantages over manual spreadsheet.
1.
Calculations are done automatically with the help of formulas and functions. This saves time and reduces the chance of calculation mistakes.
2.
If any input value is changed, all related derived values are updated automatically. In a manual spreadsheet, all calculations have to be done again by hand.
3.
It helps in what-if analysis. This means we can change one or more values and immediately study the effect on the result. This is very useful in business calculations.
4.
Data can be formatted neatly by using different styles, alignment, merging, wrapping, tables and conditional formatting. This improves presentation and readability.
5.
Electronic spreadsheet makes data storage, editing, printing and retrieval much easier. It also allows the use of powerful features such as LOOKUP, IF, financial functions and named ranges.
So, electronic spreadsheet is faster, more accurate, more flexible and more useful than manual spreadsheet.
Question 3
3. Define workbook and worksheet. What is the difference between them?
Answer 3
A workbook in Excel is a file. It is a collection of one or more worksheets. When Excel is opened, a workbook is created in which sheets are available for work. By default, three sheets are available.
A worksheet is an individual sheet inside a workbook. It is made up of rows, columns and cells and is used for entering data, formulas and functions. At a time, only one worksheet can be active for carrying out operations.
Difference between Workbook and Worksheet
Basis of Difference
Workbook
Worksheet
Meaning
A workbook is an Excel file.
A worksheet is a single sheet inside the workbook.
Nature
It is a collection of worksheets.
It is a part of a workbook.
Use
It stores all the sheets related to a file.
It is used for entering and processing data.
Structure
It may contain one or more worksheets.
It contains rows, columns and cells.
Example
A saved Excel file like `Accounts.xlsx`
Sheet1, Sheet2 or Sheet3 inside that file
So, workbook is the complete file, while worksheet is only one page or sheet within that file.
Question 4
4. Explain active worksheet?
Answer 4
An active worksheet is the worksheet on which the user is currently working. At a time, only one worksheet can be active in a workbook, and all operations such as data entry, editing, formula writing and formatting are performed on that sheet only.
The active worksheet can be identified easily because its name appears in bold letters in the sheet tab area at the bottom left of the screen. If the user clicks another sheet tab, that sheet becomes the new active worksheet.
Therefore, the active worksheet is simply the currently selected worksheet available to the user for work.
Question 5
5. Using Fill: Series command in Excel write down the steps to fill data from 120,320…..2300 in Column A.
Answer 5
To fill data in Column A from 120, 320, 520 … up to 2300, the step value will be 200.
The steps are as follows:
1.
Enter 120 in the starting cell of Column A, such as A1.
2.
Select the cell A1.
3.
Go to the Home tab.
4.
In the Editing group, click Fill.
5.
Choose Series from the drop-down list.
6.
In the Series dialog box:
*
Select Columns
*
Select Linear
*
Enter Step value = 200
*
Enter Stop value = 2300
7.
Click OK.
Now Excel will fill the cells in Column A as 120, 320, 520, 720 and so on up to 2300. This method is useful when data has to be entered in a sequence without typing every value manually.
Question 6
6. Define Data Range, Name Range and How these are created and selected?
Answer 6
A Data Range means a group of cells in a worksheet. It is identified by the cell references of the upper-left cell and lower-right cell, separated by a colon. For example, A1:E2 is a data range. It includes all cells between these two points. Ranges are used in formulas, functions and commands.
A Name Range means giving a meaningful name to a cell or range of cells. This helps in writing formulas more easily and makes them easier to understand and remember. For example, instead of using `B1:F1`, we can give the name Numbers and use `=SUM(Numbers)`.
Steps to create a Name Range
1.
Select the cell or range of cells to be named.
2.
Click the Formulas tab.
3.
Click Define Name.
4.
A dialog box will open.
5.
Type the required name in the Name box.
6.
Check the cell reference in the Refers to box.
7.
Click OK.
Steps to select a Name Range
1.
Click the Formulas tab.
2.
Use Apply Name or the Name Box.
3.
Choose the already created range name.
4.
Excel will select that named range.
So, a data range is a group of cells, while a name range is a labelled range used for convenience and clarity.
Question 7
7. Write down the steps for the use of Custom Lists.
Answer 7
Custom Lists are used when we want Excel to fill repeated user-defined sequences such as department names, months, regions or product categories in a desired order.
The steps are as follows:
1.
Click the Microsoft Office Button and then click Excel Options.
2.
Choose the option related to editing or popular Excel settings where Custom Lists can be managed.
3.
Open the Custom Lists dialog box.
4.
In the List entries box, type the values in the required order, each on a separate line.
5.
Click Add.
6.
Click OK to save the custom list.
7.
Now type the first item of the custom list in a cell.
8.
Use the fill handle and drag it to continue the list automatically.
This feature is useful when the normal numeric or date series is not enough and the user wants a special sequence for repeated use.
Question 8
8. Explain the purpose of Format, Conditional Formatting and Auto Format.
Answer 8
Format is used to change the appearance of data in a worksheet. It helps in improving readability and presentation. With formatting, we can change font, size, colour, alignment, borders, number style, currency format, date format and many other display properties.
Conditional Formatting is used to apply formatting automatically when certain conditions are true. For example, values above a given amount can be highlighted in one colour and lower values in another. This helps in quick analysis and easy identification of important data. It is especially useful in large worksheets.
Auto Format is used to apply a ready-made formatting style to a range or table quickly. It saves time because the user does not have to apply each formatting feature separately. It gives a neat and professional look to the worksheet. Table styles such as Light, Medium and Dark can also be used for this purpose.
So, all three are used to make spreadsheet data clear, attractive and easy to understand.
Question 9
9. What is Wrap Text feature of Excel, Merging of Cells and what will be the cell address of merged cells?
Answer 9
Wrap Text is a feature that displays the contents of a cell on multiple lines within the same cell. It is useful when the text is long and cannot fit properly in the cell width. Instead of overflowing into the next cell, the text is wrapped and shown neatly inside the same cell.
Merging of Cells means joining two or more selected cells into one larger cell. This is usually done for headings and titles so that they look more clear and centered over related columns.
When cells are merged, the cell address of the merged cell becomes the address of the upper-left cell of the selected range. For example, if cells A1:C1 are merged, the merged cell address will be A1.
These features are useful for better worksheet design and presentation.
Question 10
10. Explain the purpose of using Print Preview?
Answer 10
Print Preview is used to see how the worksheet will appear when printed. It helps the user check the page layout before actually taking the printout.
Its main purpose is to check whether:
*
the data fits properly on the page,
*
margins are correct,
*
headers and footers appear properly,
*
page breaks are suitable,
*
alignment and spacing are proper,
*
no part of the worksheet is cut off.
This feature saves paper, ink and time because mistakes can be corrected before printing. So, Print Preview is an important feature for preparing a proper final printout.
Question 11
11. Explain difference between relative reference and absolute reference?
Answer 11
Difference between Relative Reference and Absolute Reference
Basis of Difference
Relative Reference
Absolute Reference
Meaning
It changes automatically when the formula is copied to another cell.
It remains fixed even when the formula is copied to another cell.
Default Nature
It is the default cell reference in Excel.
It is used only when a fixed reference is needed.
Form
Written without dollar signs, such as `C4`.
Written with dollar signs, such as `$C$4`.
Use
Used when the relationship should change according to the new location.
Used when the same cell reference must be used again and again.
Behaviour on Copying
Row and column both may change.
Row and column do not change.
Relative reference is used when a formula has to adjust according to position. For example, if `=A1+B1` is copied down, it becomes `=A2+B2`.
Absolute reference is used when one cell must stay fixed. For example, if tax rate is in cell `$G$3`, then every copied formula can continue to use that same fixed cell.
The spreadsheet also uses mixed reference, where either row or column remains fixed, such as `$C4` or `C$4`.
Question 12
12. Discuss IF function and nested IF functions giving example?
Answer 12
The IF function is a logical function. It returns one value if a condition is true and another value if the condition is false. Its syntax is:
`IF(logical_test, value_if_true, value_if_false)`
Here:
•
logical_test means the condition to be checked,
•
value_if_true means the result if the condition is satisfied,
•
value_if_false means the result if the condition is not satisfied.
Example:
`=IF(A1<20,”Yes”,”No”)`
This formula gives Yes if the value in A1 is less than 20, otherwise it gives No.
A nested IF function means using one IF function inside another IF function. It is used when more than one condition has to be tested. Excel first checks the first condition. If it is false, then it checks the next one, and so on.
Example:
`=IF(E2<96, 
    IF(E2<91,
        IF(E2<55,"Fail","C Grade"),
    "B Grade"),
"A Grade")`
This works as follows:
•
if marks are less than 55, result is Fail
•
if marks are 55 or more but less than 91, result is C Grade
•
if marks are 91 or more but less than 96, result is B Grade
•
if marks are 96 or above, result is A Grade
Thus, IF function is used for one condition, while nested IF is used for multiple conditions.
Question 13
13. Write down examples for any two financial functions you know with their proper syntax.
Answer 13
Two useful financial functions are PMT and PV.
(i) PMT Function
The PMT function is used to calculate the periodic payment for a loan based on constant payments and a constant rate of interest.
Syntax:
`PMT(rate, nper, pv, [fv], [type])`
Where:
•
rate = rate of interest per period
•
nper = total number of payments
•
pv = present value or loan amount
•
fv = future value
•
type = whether payment is at the beginning or end of the period
Example:
`=PMT(10%/12, 24, 50000)`
This can be used to calculate the monthly instalment for a loan of ₹ 50,000 for 24 months at 10% annual interest. PMT is used for loan repayment schedules.
(ii) PV Function
The PV function is used to find the present value of a future amount.
Syntax:
`PV(rate, nper, pmt, [fv], [type])`
Where:
*
rate = interest rate per period
*
nper = number of periods
*
pmt = periodic payment
*
fv = future value
*
type = timing of payment
Example:
`=PV(10%, 3, 0, 1000)`
This is used to find the present value of ₹ 1000 receivable after 3 years at 10% rate of interest. The later spreadsheet application exercises also use PV and PMT as important financial functions.
So, financial functions are very useful for loan, investment and repayment calculations in spreadsheets.
Question 14
14. What is the use of PMT function?
Answer 14
The PMT function is used to calculate the periodic payment amount of a loan or investment. It is very useful when a person wants to know how much amount has to be paid regularly, such as every month or every year, for repayment of a loan. The payment is calculated on the basis of a constant rate of interest and a fixed number of periods.
This function is commonly used in loan repayment schedule preparation. For example, if a person takes a loan from a bank, the PMT function helps in finding the equal monthly instalment. In this way, it becomes easy to plan repayment.
The general syntax is:
`=PMT(rate, nper, pv, [fv], [type])`
Where:
•
rate = rate of interest per period
•
nper = total number of payment periods
•
pv = present value or loan amount
•
fv = future value
•
type = payment at the beginning or end of the period
Example:
`=PMT(10%/12,24,50000)`
This can be used to calculate the monthly instalment for a loan of ₹ 50,000 for 24 months at 10% annual interest.
Question 15
15. How many ways Data Entry is possible in Spread Sheet?
Answer 15
Data entry in a spreadsheet is mainly possible in two ways.
1. Direct entry of value
In this method, the user enters the value directly into the cell by typing from the keyboard. The value may be:
•
numeric
•
alpha-numeric
•
date value
For example, entering 2500, Assets, or 12/07/2026 directly into a cell is direct data entry.
2. Entry through formula or derived value
In this method, the value is not typed directly. Instead, it is obtained with the help of a formula or function using other cell values. This is called a derived value.
For example, if:
•
Quantity is in one cell
•
Price is in another cell
then total value can be calculated by a formula such as:
`=Quantity * Price`
So, data entry in spreadsheet is possible either by entering the value directly or by using formulas/functions to derive the value.
Question 16
16. Define one-variable Table and Two-variable table. Explain these with examples.
Answer 16
A Data Table is used in spreadsheet for what-if analysis. It helps in studying the effect of changing one or more input values on the result. There are two main types: one-variable table and two-variable table.
One-variable Table
A one-variable table is used when the result depends on only one changing input value. In this type of table, one input value is changed repeatedly, while the other values remain fixed. The spreadsheet then shows how the result changes for different values of that one variable.
For example, suppose compound interest or instalment amount depends on rate of interest. If only the rate is changed and all other values remain fixed, then a one-variable table can be created to see the effect of different interest rates.
Another simple example is preparing a table to see how the value of one formula changes when values in cells B5:B10 are substituted into one input cell.
Two-variable Table
A two-variable table is used when the result depends on two changing input values. In this table, two different variables are changed at the same time, and the effect on the result is studied.
For example, a multiplication table can be prepared by using two input cells. One variable may be entered in rows and the other in columns. The result shown inside the table is obtained by multiplying the row and column values. The book gives a simple example of creating a 15 × 15 multiplication table by using two input cells, such as B1 and C1, and the formula `=B1*C1`.
Difference in simple words
•
In a one-variable table, only one input changes.
•
In a two-variable table, two inputs change together.
Thus, both tables are useful tools for analysis, comparison and decision-making in spreadsheet work.
Question 17
17. Define Pivot Table and explain usage?
Answer 17
A Pivot Table is a special spreadsheet tool used to summarise, organise and analyse large amounts of data in a compact and meaningful form. It helps the user present the same data in different ways without changing the original data.
It is called a Pivot Table because the arrangement of rows and columns can be changed or “pivoted” easily to view the data from different angles.
Usage of Pivot Table
A Pivot Table is used for the following purposes:
1.
Summarising large data quickly
It can total, count, average or otherwise summarise values without writing long formulas.
2.
Grouping data
It helps in grouping records category-wise, month-wise, product-wise, dealer-wise, employee-wise and so on.
3.
Comparison of values
It is useful for comparing sales, expenses, quantity, hours worked or other figures across categories.
4.
Flexible presentation
The user can drag row fields, column fields and value fields to prepare different types of reports from the same source data.
5.
Decision-making
It helps management and users to understand patterns, totals and trends in business data.
Example
If a company has sales data containing:
•
dealer name
•
product name
•
month
•
quantity sold
•
sales value
then a Pivot Table can prepare:
•
product-wise sales report
•
dealer-wise sales report
•
month-wise total sales
•
combined summaries such as dealer-wise and product-wise totals
So, Pivot Table is a very useful feature for data analysis and report preparation in spreadsheet.
Question 18
18. What are formulas and how they are created?
Answer 18
A formula means a mathematical calculation performed on a set of cells. It is used to calculate a result in a spreadsheet. A formula always starts with an equal to sign (=).
A formula may contain:
•
numbers
•
cell references
•
arithmetic operators
•
functions
For example:
`=A1+B1`
`=D1+E1/F1*G1`
`=SUM(B1:F1)`
When a formula is entered in a cell, the spreadsheet calculates the result and displays it in that cell. If the values of the related cells are changed, the result of the formula also changes automatically. This makes the spreadsheet very useful for quick and accurate calculation.
How formulas are created
The steps for creating a formula are:
1.
Select the cell where the result is required.
2.
Type the equal to sign (=).
3.
Enter the cell references, numbers, operators or functions required.
4.
Press Enter.
5.
The result will be displayed in the selected cell.
Example
If values are in cells A1 and B1, then to add them:
`=A1+B1`
If marks are in cells C1 to C5, then total can be calculated by:
`=SUM(C1:C5)`
Thus, formulas are used to perform calculations in spreadsheet and they are created by entering an expression beginning with `=`.
Question 19
19. Write down the differences between formula and function?
Answer 19
Basis of Difference
Formula
Function
Meaning
A formula is a user-created mathematical expression used for calculation in a spreadsheet.
A function is a built-in predefined formula available in spreadsheet software.
Creation
It is created by the user according to need.
It is already provided by Excel.
Complexity
It may be simple or complex depending on the expression written by the user.
It is designed to perform a particular task in a standard way.
Starting Sign
It starts with an equal to sign `=`.
It also starts with an equal to sign `=`, followed by the function name.
Components
It may contain numbers, cell references, operators and functions.
It contains a function name and arguments inside brackets.
Example
`=A1+B1*C1`
`=SUM(A1:A10)` or `=AVERAGE(B1:B5)`
Nature
It is a general expression.
It is a special keyword for a specific operation.
In simple words, a formula is any expression written to calculate a result, while a function is a ready-made formula provided by the spreadsheet software. Every function is used inside a formula, but every formula is not necessarily a function.
For example:
•
`=A1+B1` is a formula
•
`=SUM(A1:A5)` is a function-based formula
Skill Review
Question A
A. You had started your own online business to sale and find out the sales for the first week is as follows:
Monday
₹ 120.45
Tuesday
₹ 187.43
Wednesday
₹ 106.87
Thursday
₹ 143.69
Friday
₹ 117.52
Saturday
₹ 87.93
Sunday
₹ 92.12
Use a function to work out how much you earned, on average, each day.
Answer A
To find how much was earned on average each day, the AVERAGE() function should be used. In a spreadsheet, functions are built-in formulas used to process data, and AVERAGE() gives the mean value of a selected range.
Suggested worksheet format
Day
Sales (₹)
Monday
120.45
Tuesday
187.43
Wednesday
106.87
Thursday
143.69
Friday
117.52
Saturday
87.93
Sunday
92.12
If these sales values are entered in cells B2:B8, then the formula for average sales will be:
`=AVERAGE(B2:B8)`
Calculation
Total sales for the week
=
120.45 + 187.43+ 106.87+ 143.69+ 117.52+ 87.93+ 92.12
=
856.01
Average sales per day
=
856.01 ÷ 7
=
122.29 approximately
Final Answer
So, the average earning per day = ₹ 122.29.
This can be calculated directly in Excel by using the formula:
`=AVERAGE(B2:B8)`
Question B
B. Use a Days360 function to work out how many days are left before your next birthday. Instead of typing out the current date in say cell A2, you can use this inbuilt function:
= Now()
The Now function doesn’t need anything between the round brackets. Once you have today’s date, you can enter your birthday in say cell B2.
Answer B
To find how many days are left before the next birthday, the spreadsheet can use the NOW() function for the current date and time, and then use DAYS360() to calculate the number of days between two dates on a 360-day year basis. NOW() returns the current date and time and does not require anything inside the brackets.
Steps
1.
In cell A2, type:
`=NOW()`
2.
In cell B2, enter the next birthday date.
Example: `15-10-2026`
3.
In cell C2, type the formula:
`=DAYS360(A2,B2)`
This formula will return the number of days left before the next birthday according to the 360-day method.
Example
Suppose:
*
A2 contains the current date from `=NOW()`
*
B2 contains the next birthday date
Then:
`=DAYS360(A2,B2)`
will give the number of days left.
Important note
If the birthday for the current year has already passed, then the date entered in B2 should be the birthday of the next year. Otherwise, the result may be incorrect or negative. So, the main idea is to use:
*
NOW() for today’s date
*
DAYS360() for calculating the number of days remaining.
Depending on when you’re seeing this answer, the birthdate should be as per that time. For instance if you’re seeing this in 2030 or any other later year after 2026, use the birthdate for that year.
Question C
C. Create an Activity Report (Weekly) for a Sales Representative working in a reputed home appliances manufacturing company. Details recorded should contain Date of Visit, Day of Visit, Name of Shop/Dealer Visited, Address, Phone Number, Name of Product (Dealing), Type of Response (by the Dealer), Demand of Product and Duration Spent (in hrs)..
(a)
Fill data in Date of Visit, Day of Visit using Fill Series.
(b)
Name the worksheet created above as Weekly Visit Report.
(c)
Cerate Product-wise, Dealer-wise Monthly Report which should include Total Hours Spent.
(d)
Count total number of dealers visited and dealers who gave positive response.
Answer C
This activity is based on preparing a practical spreadsheet report. The spreadsheet should contain all the required columns and should use features like Fill Series, worksheet naming, formulas, and summary functions. Spreadsheet tools also support sorting, grouping, functions, and Pivot Table reporting, which make such activity reports easier to prepare.
Step 1: Create the weekly activity report table
The worksheet can be prepared in the following format:
Date of Visit
Day of Visit
Name of Shop/Dealer Visited
Address
Phone Number
Name of Product
Type of Response
Demand of Product
Duration Spent (in hrs)
01-07-2026
Monday
Sharma Electronics
Main Road, Delhi
9876543210
Refrigerator
Positive
High
2
02-07-2026
Tuesday
Gupta Home Needs
Karol Bagh, Delhi
9876501234
Washing Machine
Neutral
Medium
1.5
03-07-2026
Wednesday
Modern Appliances
Rohini, Delhi
9811122233
Microwave Oven
Positive
High
2
04-07-2026
Thursday
Bright Sales
Pitampura, Delhi
9898989898
Air Conditioner
Negative
Low
1
05-07-2026
Friday
Khanna Traders
Janakpuri, Delhi
9822233344
Mixer Grinder
Positive
Medium
1.5
06-07-2026
Saturday
City Electronics
Laxmi Nagar, Delhi
9812345678
Water Purifier
Positive
High
2
07-07-2026
Sunday
Om Agencies
Dwarka, Delhi
9800011111
Induction Cooktop
Neutral
Medium
1
This format includes all the details asked in the question.
(a) Fill data in Date of Visit, Day of Visit using Fill Series
For Date of Visit, enter the first date in the starting cell, then use Fill Series to continue the dates automatically downward. For Day of Visit, after entering the first day, the fill handle or series option can be used so that Excel fills Monday, Tuesday, Wednesday and so on in sequence. The use of Fill Series for sequences in a worksheet will be of help in this case.
(b) Name the worksheet created above as Weekly Visit Report
After preparing the worksheet, the default sheet name should be changed to Weekly Visit Report. A workbook may contain multiple worksheets, and the sheet names can be changed by selecting the sheet tab and renaming it.
(c) Create Product-wise, Dealer-wise Monthly Report which should include Total Hours Spent
For a monthly report, the weekly visit data can be summarised either by formulas or by using a Pivot Table. A Pivot Table is useful for arranging and summarising data in a flexible manner. It can show product-wise and dealer-wise totals very clearly.
Product-wise summary example
Product
Total Hours Spent
Refrigerator
2
Washing Machine
1.5
Microwave Oven
2
Air Conditioner
1
Mixer Grinder
1.5
Water Purifier
2
Induction Cooktop
1
Dealer-wise summary example
Dealer
Total Hours Spent
Sharma Electronics
2
Gupta Home Needs
1.5
Modern Appliances
2
Bright Sales
1
Khanna Traders
1.5
City Electronics
2
Om Agencies
1
If more weeks are added in the same month, the Pivot Table will automatically help in generating a monthly report with total hours spent dealer-wise and product-wise.
(d) Count total number of dealers visited and dealers who gave positive response
To count the total number of dealers visited, the number of filled dealer names can be counted using COUNTA().
If dealer names are entered in cells C2:C8, the formula can be:
`=COUNTA(C2:C8)`
This will give the total number of dealers visited. Since there are 7 entries in the sample data, the result will be 7. Functions like COUNT and related functions are part of spreadsheet processing and are used for summarising data.
To count dealers who gave Positive response, COUNTIF() can be used.
If response values are entered in cells G2:G8, the formula can be:
`=COUNTIF(G2:G8,”Positive”)`
In the sample data, positive responses are from:
•
Sharma Electronics
•
Modern Appliances
•
Khanna Traders
•
City Electronics
So, the result will be 4.
Final submission note
Thus, the weekly report should contain all dealer visit details in one worksheet named Weekly Visit Report, dates and days should be filled using Fill Series, and monthly summaries should be prepared product-wise and dealer-wise with Total Hours Spent. The total dealers visited and total positive responses can be calculated using spreadsheet functions. This makes the activity report systematic, accurate and easy to analyse.
Question D
D. Create a worksheet to record sales of home appliances sold by M/s Home Maker Ltd. in the following format:
Date of Sales
Name of Customers
Name of Products
Make
Quantity
Sales Amount
 
 
 
 
 
 
 
The product lists includes Television sets, Refrigerators, Micro wave ovens, Water Coolers, Air Coolers, Geezers and Air conditioners of different Makes (and models). The cost of price of television is ranging from ₹ 10,000 to ₹ 56,000; refrigerator is ₹ 13,000 to ₹ 45,000, micro wave ovens, water coolers, geezers and air coolers are from ₹ 8,000 to ₹ 25,000 and Air Conditioners are from ₹ 18,000 to ₹ 55,000. The shopkeeper sales these products adding 17.25% more on cost price. He provides a discount of 4.35% on total amount if any customer purchases two products on the same date. Enter 30 records of different dates (for a month) and different customers accordingly. Calculate the following:
(a)
Product wise weekly sales and discount.
(b)
Calculate the profit of shopkeeper.
(c)
Product wise total sales of the month and discount offered.
Answer D
Final Submission Answer
This worksheet should be prepared in spreadsheet form so that the sales of home appliances can be recorded properly and all calculations can be done automatically with formulas. The question clearly states two important conditions:
1.
Selling Price = Cost Price + 17.25% of Cost Price
2.
Discount = 4.35% on total amount when quantity purchased is 2 on the same date
So, the sheet should calculate:
•
selling price per unit,
•
gross amount,
•
discount,
•
net sales amount,
•
and profit.
1. Formula logic to be used
Assume:
•
Quantity is in column `E`
•
Cost Price per Unit is in column `F`
•
Selling Price per Unit is in column `G`
•
Gross Amount is in column `H`
•
Discount is in column `I`
•
Net Sales Amount is in column `J`
•
Profit is in column `K`
Then the formulas are:
•
Selling Price per Unit
`=F2*1.1725`
•
Gross Amount
`=E2*G2`
•
Discount
`=IF(E2=2,H2*4.35%,0)`
•
Net Sales Amount
`=H2-I2`
•
Profit
`=J2-(E2*F2)`
Here, the discount is correctly applied in the rows where quantity = 2, as requested.
2. Sample data table with 30 customer purchase records
S. No.
Date of Sales
Name of Customer
Name of Product
Make
Qty
Cost Price/Unit
Selling Price/Unit
Gross Amount
Discount
Net Sales Amount
Profit
1
01-07-2026
Amit
Television
Sony
1
30000
35175.00
35175.00
0.00
35175.00
5175.00
2
01-07-2026
Bhavna
Refrigerator
LG
1
22000
25795.00
25795.00
0.00
25795.00
3795.00
3
02-07-2026
Chirag
Microwave Oven
IFB
2
12000
14070.00
28140.00
1224.09
26915.91
2915.91
4
02-07-2026
Divya
Air Cooler
Symphony
1
15000
17587.50
17587.50
0.00
17587.50
2587.50
5
03-07-2026
Eshan
Water Cooler
Blue Star
1
18000
21105.00
21105.00
0.00
21105.00
3105.00
6
03-07-2026
Farah
Geezer
Bajaj
2
10000
11725.00
23450.00
1020.08
22429.92
2429.92
7
04-07-2026
Gaurav
Air Conditioner
Voltas
1
40000
46900.00
46900.00
0.00
46900.00
6900.00
8
04-07-2026
Heena
Television
Samsung
1
28000
32830.00
32830.00
0.00
32830.00
4830.00
9
05-07-2026
Ishaan
Refrigerator
Whirlpool
2
25000
29312.50
58625.00
2550.19
56074.81
6074.81
10
05-07-2026
Juhi
Microwave Oven
LG
1
14000
16415.00
16415.00
0.00
16415.00
2415.00
11
06-07-2026
Karan
Air Cooler
Orient
1
16000
18760.00
18760.00
0.00
18760.00
2760.00
12
06-07-2026
Lavanya
Geezer
Havells
1
11000
12897.50
12897.50
0.00
12897.50
1897.50
13
07-07-2026
Manav
Water Cooler
Voltas
2
20000
23450.00
46900.00
2040.15
44859.85
4859.85
14
07-07-2026
Neha
Television
LG
1
32000
37520.00
37520.00
0.00
37520.00
5520.00
15
08-07-2026
Omkar
Air Conditioner
Daikin
1
50000
58625.00
58625.00
0.00
58625.00
8625.00
16
08-07-2026
Pooja
Refrigerator
Samsung
1
30000
35175.00
35175.00
0.00
35175.00
5175.00
17
09-07-2026
Qadir
Microwave Oven
Panasonic
2
13000
15242.50
30485.00
1326.10
29158.90
3158.90
18
09-07-2026
Riya
Air Cooler
Crompton
1
14000
16415.00
16415.00
0.00
16415.00
2415.00
19
10-07-2026
Sahil
Geezer
Racold
1
12000
14070.00
14070.00
0.00
14070.00
2070.00
20
10-07-2026
Tanvi
Water Cooler
Usha
1
17000
19932.50
19932.50
0.00
19932.50
2932.50
21
11-07-2026
Uday
Television
Sony
2
35000
41037.50
82075.00
3570.26
78504.74
8504.74
22
11-07-2026
Vaishnavi
Air Conditioner
LG
1
45000
52762.50
52762.50
0.00
52762.50
7762.50
23
12-07-2026
Waseem
Refrigerator
Bosch
1
38000
44555.00
44555.00
0.00
44555.00
6555.00
24
12-07-2026
Xenia
Microwave Oven
IFB
1
15000
17587.50
17587.50
0.00
17587.50
2587.50
25
13-07-2026
Yash
Air Cooler
Symphony
2
13000
15242.50
30485.00
1326.10
29158.90
3158.90
26
13-07-2026
Zara
Geezer
AO Smith
1
12500
14656.25
14656.25
0.00
14656.25
2156.25
27
14-07-2026
Aarav
Water Cooler
Blue Star
1
21000
24622.50
24622.50
0.00
24622.50
3622.50
28
14-07-2026
Bina
Television
TCL
1
26000
30485.00
30485.00
0.00
30485.00
4485.00
29
15-07-2026
Chetan
Air Conditioner
Hitachi
2
42000
49245.00
98490.00
4284.32
94205.69
10205.69
30
15-07-2026
Disha
Refrigerator
Haier
1
24000
28140.00
28140.00
0.00
28140.00
4140.00
Note: In rows 3, 6, 9, 13, 17, 21, 25 and 29, the customer has purchased two products on the same date (Qty = 2), so the 4.35% discount has been applied correctly on the gross amount.
3. Product-wise weekly sales and discount
For this sample, the month can be grouped week-wise as:
•
Week 1: 1st to 7th
•
Week 2: 8th to 14th
•
Week 3: 15th onwards
Weekly summary
Week
Product
Total Sales Amount
Total Discount
Week 1
Air Conditioner
46900.00
0.00
Week 1
Air Cooler
36347.50
0.00
Week 1
Geezer
35327.42
1020.08
Week 1
Microwave Oven
43330.91
1224.09
Week 1
Refrigerator
81869.81
2550.19
Week 1
Television
105525.00
0.00
Week 1
Water Cooler
65964.85
2040.15
Week 2
Air Conditioner
111387.50
0.00
Week 2
Air Cooler
45573.90
1326.10
Week 2
Geezer
28726.25
0.00
Week 2
Microwave Oven
46746.40
1326.10
Week 2
Refrigerator
79730.00
0.00
Week 2
Television
108989.74
3570.26
Week 2
Water Cooler
44555.00
0.00
Week 3
Air Conditioner
94205.69
4284.32
Week 3
Refrigerator
28140.00
0.00
Useful formulas
If:
•
Date is in column `B`
•
Product is in column `D`
•
Net Sales Amount is in column `J`
•
Discount is in column `I`
then product-wise weekly total can be prepared using SUMIFS().
Example for Week 1 Refrigerator Sales:
`=SUMIFS(J:J,D:D,”Refrigerator”,B:B,”>=01-07-2026″,B:B,”<=07-07-2026″)`
Example for Week 1 Refrigerator Discount:
`=SUMIFS(I:I,D:D,”Refrigerator”,B:B,”>=01-07-2026″,B:B,”<=07-07-2026″)`
A Pivot Table can also be used for faster summary preparation.
4. Profit of the shopkeeper
Profit for each row is:
`=Net Sales Amount – (Quantity × Cost Price)`
So, total monthly profit is:
`=SUM(K2:K31)`
Total Profit
Total Profit of the shopkeeper = Rs. 1,32,819.98
This profit is after considering the 4.35% discount in the rows where quantity is 2.
5. Product-wise total sales of the month and discount offered
Product
Total Monthly Sales
Total Discount Offered
Air Conditioner
252493.19
4284.32
Air Cooler
81921.40
1326.10
Geezer
64053.67
1020.08
Microwave Oven
90077.31
2550.19
Refrigerator
189739.81
2550.19
Television
214514.74
3570.26
Water Cooler
110519.85
2040.15
Useful formulas
Example for Television total sales:
`=SUMIF(D:D,”Television”,J:J)`
Example for Television discount offered:
`=SUMIF(D:D,”Television”,I:I)`
6. Conclusion
Thus, the worksheet has been prepared with 30 customer purchase records. The selling price is calculated by adding 17.25% to cost price, and the 4.35% discount is correctly applied in the rows where the customer purchased two products on the same date (Qty = 2). The spreadsheet also shows:
•
product-wise weekly sales and discount,
•
total profit of the shopkeeper,
•
and product-wise monthly sales and discount offered.
Question E
E. Create a worksheet to keep track of revenue collected and expenses done in conducting tour programs at different tourist places during 2004 to 2008. Format the numeric data in currency format, prepare year wise columns for revenue and expenses for each tourist place and calculate the difference. The calculated difference may be negative, the format of negative balance may be red coloured. Use conditional formatting for higher and lower values of revenue and expenses. Align entire text in centre. The font of tourist place is Arial with 14 point while the font of year is Times Roman with 14 points.
(Amount in lacs)
Tourist Place
2004 Rev
2004 Exp
2005 Rev
2005 Exp
2006 Rev
2006 Exp
2007 Rev
2007 Exp
2008 Rev
2008 Exp
Manali
123
55
234
123
345
333
333
365
365
453
Kashmir
234
123
123
55
365
453
345
333
333
365
Shilong
345
333
333
365
123
55
234
123
456
233
Kerala
333
365
365
453
234
123
123
55
345
333
Answer E
This worksheet should be prepared to compare revenue, expenses and difference for each tourist place over the years 2004 to 2008. The question also asks for proper formatting, including currency format, red colour for negative balance, conditional formatting, centre alignment, and specified fonts. The tourist-place table with year-wise revenue and expense values is given in the problem.
Suggested worksheet structure
Tourist Place
2004 Rev
2004 Exp
2004 Diff
2005 Rev
2005 Exp
2005 Diff
2006 Rev
2006 Exp
2006 Diff
2007 Rev
2007 Exp
2007 Diff
2008 Rev
2008 Exp
2008 Diff
Manali
₹ 123.00
₹ 55.00
₹ 68.00
₹ 234.00
₹ 123.00
₹ 111.00
₹ 345.00
₹ 333.00
₹ 12.00
₹ 333.00
₹ 365.00
-₹ 32.00
₹ 365.00
₹ 453.00
-₹ 88.00
Kashmir
₹ 234.00
₹ 123.00
₹ 111.00
₹ 123.00
₹ 55.00
₹ 68.00
₹ 365.00
₹ 453.00
-₹ 88.00
₹ 345.00
₹ 333.00
₹ 12.00
₹ 333.00
₹ 365.00
-₹ 32.00
Shilong
₹ 345.00
₹ 333.00
₹ 12.00
₹ 333.00
₹ 365.00
-₹ 32.00
₹ 123.00
₹ 55.00
₹ 68.00
₹ 234.00
₹ 123.00
₹ 111.00
₹ 456.00
₹ 233.00
₹ 223.00
Kerala
₹ 333.00
₹ 365.00
-₹ 32.00
₹ 365.00
₹ 453.00
-₹ 88.00
₹ 234.00
₹ 123.00
₹ 111.00
₹ 123.00
₹ 55.00
₹ 68.00
₹ 345.00
₹ 333.00
₹ 12.00
The values above are based on the table shown in the question.
Formula for difference
For each year:
Difference = Revenue – Expenses
For example, if:
•
Revenue is in B2
•
Expense is in C2
Then difference in D2 will be:
`=B2-C2`
The same pattern can be copied for all years and all tourist places.
Required formatting steps
1. Currency format for numeric data
All revenue, expense and difference values should be formatted in currency format. The spreadsheet formatting section explains use of currency format and also shows that negative values can be displayed in red.
2. Negative difference in red colour
For all difference columns, apply number format so that negative values appear in red colour. This helps in quickly identifying loss or negative balance.
3. Conditional formatting for higher and lower values
Apply conditional formatting separately on revenue and expense columns:
•
higher values can be highlighted with one style
•
lower values can be highlighted with another style
This makes comparison easier and improves visual analysis. Conditional formatting is used in spreadsheet to highlight values based on conditions.
4. Centre alignment
The entire worksheet text should be centre aligned so that the table looks neat and balanced.
5. Font settings
•
Tourist Place names should be in Arial, 14 point
•
Year headings should be in Times Roman, 14 point
Example of analysis
From the table:
•
Manali has positive difference in 2004, 2005 and 2006, but negative difference in 2007 and 2008.
•
Shilong shows the highest positive difference in 2008.
•
Kerala shows negative balance in 2004 and 2005, but positive difference in later years.
This kind of worksheet is helpful for comparing the financial outcome of tour programs at different tourist places over multiple years.
Final submission note
Thus, the worksheet should contain tourist-place-wise and year-wise revenue and expense columns, along with a difference column for each year. Proper currency formatting, red coloured negative values, conditional formatting, centre alignment, and required font settings should be applied. This makes the worksheet clear, attractive and suitable for financial comparison.