ProductsLogo
LogoStudy Documents
LogoAI Grader
LogoAI Answer
LogoAI Code Checker
LogoPlagiarism Checker
LogoAI Paraphraser
LogoAI Quiz
LogoAI Detector
PricingBlogAbout Us
logo

Exploring MS Excel Capabilities

Verified

Added on  2020/06/05

|15
|3296
|110
AI Summary
This assignment delves into the functionalities of Microsoft Excel, covering essential aspects such as formulas (relative and absolute cell references), charting options (area, column, line, pie, scatter plots), and data entry best practices. It emphasizes the tool's significance in analyzing datasets, performing calculations, and presenting summarized results visually.

Contribute Materials

Your contribution can guide someone’s learning journey. Share your documents today.
Document Page
DEVELOP AND USE
COMPLEX SPREADSHEET

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
Table of Contents
INTRODUCTION...........................................................................................................................2
TASK 1............................................................................................................................................2
TASK 2............................................................................................................................................6
Advanced functions of spreadsheet software applications..........................................................6
Impact of formatting and designing on presentation and readability of data...............................7
Organizational requirements for ergonomics, work period and breaks and conservations
techniques.....................................................................................................................................7
WRITTEN/VERBAL QUESTIONS...............................................................................................9
1. Steps to develop a spreadsheet.................................................................................................9
2. Requirements to develop a linked spreadsheet........................................................................9
3. Steps involved in automating and standardizing spreadsheet..................................................9
4. Basic process to use spreadsheet............................................................................................10
5. Steps to present numerical data in graphical form.................................................................10
6. Relative and absolute cell reference.......................................................................................11
7. Data entry...............................................................................................................................11
8. Common type of charts and graphs........................................................................................11
CONCLUSION..............................................................................................................................11
REFERENCES..............................................................................................................................13
Document Page
INTRODUCTION
In real world, managers need to carry out various calculations to examine relevant
information and data set for the purpose of rationale decisions. The current report aims at
identifying the practical use of MS Excel in the corporate world. The assignment will critically
investigate the use of different features of MS Excel to assess that how it can be use for
analytical purpose and data visualization. Despite this, ergonomics requirements in workplace
settings that every entity must follow will be discuss. In addition, various ways to save excessive
energy consumption will be present.
TASK 1
Spreadsheet development: A spreadsheet has been design to examine the information
presented in the income statement of ABC Ltd. For such purpose, various formulas had used and
a column graph had designed to clearly present summarized details.
Formula used in Excel creation:
Net sales = Sales – Sales return and allowance
= F6 – F7
Cost of Goods sold = Opening stock + Purchase + Fright inward – closing inventory
= F11 + F14 – F15
Gross profit (Loss) = Net sales – Cost of goods sold
= F8 - F16
Total Expenses = Sum (F21:F35)
Net Operating Income = Gross profit – Total expenses
F18 – F36
Earnings before tax = Net operating income + Interest received – Interest paid
= F38 + F42 – F4
Corporation tax rate = Earnings before tax * 27.5%
Document Page
Net profit = Earnings before tax – Tax
= F45 – F46
Macro feature used for computing net profit using the same formula applied for the net
sales.
Income Statement
For the Year Ended 31st March 2017
Particulars Amount
Revenue
Gross Sales $ 34,000.00
Less: sales return and
allowance $ 2,600.00
Net Sales $ 31,400.00
Cost of Goods Sold
Beginning Inventory $ 12,000.00
Add: Purchase $ 8,000.00
Add: Fright inward $ 200.00
$ 20,200.00
Less: Ending inventory $ 2,000.00
Cost of Goods Sold $ 18,200.00
Gross Profit (Loss) $ 13,200.00
Expenses:
Advertising $ 900.00
Bank Charges $ 200.00
Commissions $ 150.00
Delivery Expenses $ 50.00
Depreciation $ 1,200.00
Insurance $ 1,000.00
Maintenance $ 180.00
Miscellaneous $ 250.00
Office Expenses $ 550.00
Postage $ 200.00
Rent $ 2,000.00
Repairs $ 800.00
Telephone $ 200.00
Utilities $ 600.00
Wages $ 2,500.00
Total Expenses $ 10,780.00
Net Operating Income $ 2,420.00

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
Other Income:
Interest Income $ 400.00
$ 400.00
Less: Interest expense $ 200.00
$ 200.00
Earnings before tax $ 2,620.00
Less: Corporate Tax @ 27.5% $ 720.50
Net profit $ 1,899.50
Common Size Income statement had created using absolute and relative cell references in
which, every item is present as a percentage of total sales. Hence, in order to keep revenue cell
constant, dollar ($) sign has been put like that $F$6.
Common Size Income Statement
Particulars % on sales
Revenue
Gross Sales 100.00%
Less: sales return and allowance 7.65%
Net Sales 92.35%
Cost of Goods Sold
Beginning Inventory 35.29%
Add: Purchase 23.53%
Add: Fright inward 0.59%
59.41%
Less: Ending inventory 5.88%
Cost of Goods Sold 53.53%
Gross Profit (Loss) 38.82%
Expenses:
Advertising 2.65%
Bank Charges 0.59%
Commissions 0.44%
Delivery Expenses 0.15%
Depreciation 3.53%
Insurance 2.94%
Maintenance 0.53%
Miscellaneous 0.74%
Office Expenses 1.62%
Postage 0.59%
Rent 5.88%
Repairs 2.35%
Telephone 0.59%
Utilities 1.76%
Document Page
Wages 7.35%
Total Expenses 31.71%
Net Operating Income 7.12%
Other Income:
Interest Income 1.18%
1.18%
Less: Interest expense 0.59%
0.59%
Earnings before tax 7.71%
Less: Corporate Tax @ 27.5% 2.12%
Net profit 5.59%
Profitability ratios: The table imported from the text file while outcome of the ratio is
determined by entering necessary formula as follows:
Gross profit ratio: 'Income Statement'!F18/'Income Statement'!$F$6 *100
Operating profit ratio: 'Income Statement'!F38/'Income Statement'!$F$6
Net profit ratio: 'Income Statement'!F47/'Income Statement'!$F$6
Profitability Ratios
Gross profit ratio Gross profit/Sales*100 38.82%
Operating profit ratio Operating profit/Sales*100 7.12%
Net profit ratio Net profit/Sales*100 5.59%
Document Page
Gross profit
ratio Operating profit
ratio Net profit ratio
0.00%
5.00%
10.00%
15.00%
20.00%
25.00%
30.00%
35.00%
40.00%
45.00%
38.82%
7.12% 5.59%
Profitability Ratios
Profitability Ratios
Figure 1 Profitability Ratios
From the above column graph, it can be seen that ABC Ltd generates a gross profit of
38.82%, which shows good profit because of mark up on total cost. However, operating profit is
just 7.12% that indicates high level of operating expenditures and net operating profit is
determined to 5.59%.
TASK 2
Advanced functions of spreadsheet software applications
Spreadsheet is greatly use by the mangers in performing statistical, financial, logical and
mathematical computation. This software helps analyst to gain a deeper understanding of the
mentioned data set and helps in performing complex calculations. It helps in performing
statistical calculations like descriptive statistics that enable analyst to identify basic
characteristics of the given data. Besides this, certain calculations that takes too much time and
complex to perform manually can be solve through using MS excel within a second such as
regression, correlation, T-test and many others. Moreover, in the business world, sound
presentation is highly important, Excel is of great use to make different kind of charts, and
graphs like pie chart, bar chart, line graph, scatter plot and others. Many times, when data is not
arrange in an accurate manner, then, analysts need to arrange it and manually arranging the large
set of data takes too much time (Tanavalee, Luksanapruksa and Singhatanadgige, 2016).

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
However, in Excel filter option is available to arrange the data in desired manner. Currently,
advanced filter option is available which provides flexibility to the firm in extracting the
necessary data set using multiple criteria.
In addition, to discover specific information, conditional formatting option is provided
that allow users to highlight certain cells in different color according to the requirements. It
improves understanding and readability of the data set through better visualization. Apart from
this, pivot chart is a special feature that allows analyst to visualize complex data set containing
both the text entries and values and assist users to discover trends and patterns. Furthermore,
advanced formatting option helps to improve the visualization of the charts. Data validation is
one of the advanced features, which helps to create list of entries restricting certain values in the
cells. Analyst can also use tools like Goal Seek, Data Tables and Scenarios to determine possible
results.
Now-a-days, modern spreadsheet developed built-in functions to perform common set of
statistical and financial calculations like net present value, standard deviation and others.
Impact of formatting and designing on presentation and readability of data
Each company has their own presentation standards that are required to be follow
including company’s logo, font size and style, header and footer, paper size, margin, numbering
formats and others. It is necessary to design the spreadsheet considering the need of intended
audience. A poorly designed spreadsheet makes it complex for the user to enter the data set or
vice-versa. Every user must follow a planned approach while designing the workbook to avoid
any pitfalls that can consumes a lot of time.
A well-designed spreadsheet allows easier navigation, eliminate repetitive inputs errors
and provide required details to the reader quickly. Design and layout setting requirement
includes borders, numbering list, captions, page number, font size, spacing and others. Automatic
designing in Excel helps to assure consistency with the other business documents. Moreover,
automatic setting of certain features including date, auto format, auto text, headers & footers,
heading helps in proper formatting and designing that helps reader to easily understand the
spreadsheet reports (Becker and et.al., 2010). In addition, charting, labeling and formatting helps
to improve appropriateness that assist users to produce huge data set with minimum effort and
Document Page
make spreadsheet readable. After working, files must be store at safe place in the required format
within designated timelines for financial statement preparation, date agreed with the client or
supervisor and others. For effective presentation, different kind of graphs like pie, column, bar,
scatter, line and others can be use. However, for the data storage purpose, firms must set a
standard system for naming all the documents ad folders as per the files stored within (Vayssiere,
Saint Paul and Benatallah, 2014). All the folders must be arranged logically which enable users
to easily navigate to the required source.
Organizational requirements for ergonomics, work period and breaks and conservations
techniques
Ergonomics is the study of workplace management so that people can perform their work
efficiently. An excellent ergonomic design helps preventing repetitive strain injuries and other
health concerns, which can lead to long-term disability (Young and et.al., 2015). With reference
to the workplace, every business unit needs to follow following requirements, that are detailed
below:
Position of computer screen must be place 18-28 inch away from the eye that permits
worker to focus on the screen.
Seat tilt must adjust in such a manner that everyone feels comfortable while working.
Knees must bent in an angle above 90 degree to prevent leg muscle pain. Besides this,
backrest must be place to provide support to the person for sitting upright and prevent
back pain.
If necessary, then footrest must provide to help worker to bend their knees in an angle of
90 degree or more (Pheasant and Haslegrave, 2016).
Keyboard must arrange properly to allow forearms to horizontal and wrist straight. A
well designed should be provides to all to not cause any undue pressure.
Monitor screen should be place at side to the source of light to avoid reflection.
Posture also matters at the workplace and a relaxed and natural posture assure flexible
movement (Carayon, 2016).
In order to avoid standing in the same position for long working hours, timely breaks
needs to provide to the workers. In this regards, employees must take a brief rest break in every
30 -60 minutes, in which, they can stand up from the seat and move around at the workplace.
Document Page
Looking on the monitor screen for extended time period can arise issues, therefore, in every 15
minutes, workers must look away. Performing complex tasks can result in Occupational Overuse
Syndrome and to prevent such occurrence, it seems necessary to interchange repetitive tasks and
other activities.
At the workplace, it seems necessary to apply necessary energy conservation techniques
to minimize the cost of overhead and maximize business profitability. The cost of electricity can
be reducing by turning off all the unoccupied lights when not in use. For the same, workers can
be aware by placing a notice board on the workplace to turn off all the lights. It developed a habit
among all and minimizes high amount of electricity bills. Besides this, workers must shut down
all the system at the workday’s end and during break hours, it must keep in sleep mode to save
energy. Energy-efficient lights can be use like CFL (Compact Florescent bulbs) that reduce
energy consumption (Pheasant and Haslegrave, 2016). Staff members need to avoid use of desk
lamps and utilize CFL and other energy-efficient lights. Timers can be use to make sure that all
the lights have been turned off at the end of the day.
In despite of this, at the workplace, it is usually seen that employees wasted a lot of paper
due to printing errors and others. Such wastage of paper can be preventing through using print
preview feature to find out errors before final printing. Moreover, using double side printing, use
of paper can be reduce.
WRITTEN/VERBAL QUESTIONS
1. Steps to develop a spreadsheet
Determine organizational standards for data entry, storing, output, reporting and
presentations
Data entry: Import files to automatically enter data or manually enter the data
Save Files: All the files must be save at safe place like to assure confidentiality. The file
must be saving at such a place where only the authorized party can access it.
Generating output after exercising required calculations
Final presentation in line with the business requirement such as margin, size, numbering,
header & footer and others

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
2. Requirements to develop a linked spreadsheet
Linking a spreadsheet means extracting data from other worksheet or workbook.
Method One = Select necessary cells to link them and copy & then paste special it to
required place.
Method Two= Enter required cells
Method Three: link formula by entering = worksheet name with an exclamation mark
and cell reference (Wood and et.al., 2014)
3. Steps involved in automating and standardizing spreadsheet
Excel has ability to automate tasks and customize logic within macro, which is a perfect
procedure to automate a process, saves time, repetitive tasks, and create standardized documents.
Macro recorder is the easy way to replicate the same action many times follows below
mentioned process:
Go to view tab > Macro > Record Macro
Entering a name in “Record Macro” dialogue box
Assigning short-cut key
From the store macro’s drop-down list, enter the required option, this workbook, new
workbook or personal macro workbook.
Entering description of the macro in the box
Press Ok
Perform the actions
Stop recording
4. Basic process to use spreadsheet
Entering Data after checking its accuracy and validity in line with organizational
requirements
Import file: File Tab > Open > select file > Ok
Perform necessary calculations
Menu > Save as > Browse location > Type name > Ok
Before printing > Print Preview > Ok
Final print
Document Page
5. Steps to present numerical data in graphical form
When data exists in a continuous cell range, than, select all cells and click on the required
chart that will present entire range.
If data does not exist in a continuous range then hide features uses to hide rows or
columns that are not usable for chart presentation.
After selection of the required data set, go to insert tab, select required chart in the
“Charts” menu. Afterwards, right click on mouse gives many option, “select data” can be use to
set horizontal and legends axis labels. Format Plot Area can be use to fill color, border style, &
color, format of chart (3D) and others to improve data presentation (Tanavalee, Luksanapruksa
and Singhatanadgige, 2016).
6. Relative and absolute cell reference
Relative cell reference: By default, MS Excel has a relative cell reference, in which, if a
formula-containing cell copied from one place to another, then, according to the relative position
of columns and rows, value will be change. It is use to repeat the calculation across various
columns or rows.
Absolute cell reference: By contrast, absolute cells reference does not change when they
copied from one cell to another and keep row and/or column value constant. It can be design
through adding dollar sign like $A$2.
7. Data entry
Data entry, as name itself, is the process of entering the given data set or input to the
worksheet. The process may involve some amendments and little bit editing to arrange data in a
logical manner to perform necessary calculations. Data can be enter either automatically or by a
clerk (Wood and et.al., 2014). While entering dataset into excel, accuracy must be check.
Moreover, necessary instructions must follow regarding its content & format along with spell
check and proofreading.
8. Common type of charts and graphs
There are number of charts that are often prepared to visualize data in an effective
manner that are mention below:
Area chart
Document Page
Column and bar diagram
Histogram
Line chart
Pie chart
Scatter plot
CONCLUSION
From the findings, it becomes clear that MS Excel is extremely important that assist
analyst to examine large amount of data set using various statistical, financial and mathematical
techniques. Moreover, it helps them to present the summarized results clearly using various
charts. However, later part found that every entity must design well-maintained workplace
settings to provide a comfortable environment to the people.

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
REFERENCES
Books and Journals
Becker, A.J. and et.al., 2010. Design of spreadsheet functions for working with tables of data.
U.S. Patent 7,698,287.
Carayon, P. ed., 2016. Handbook of human factors and ergonomics in health care and patient
safety. CRC Press.
Pheasant, S. and Haslegrave, C.M., 2016. Bodyspace: Anthropometry, ergonomics and the
design of work. CRC Press.
Salvendy, G., 2012. Handbook of human factors and ergonomics. John Wiley & Sons.
Tanavalee, C., Luksanapruksa, P. and Singhatanadgige, W., 2016. Limitations of Using
Microsoft Excel Version 2016 (MS Excel 2016) for Statistical Analysis for Medical
Research. Clinical spine surgery. 29(5). pp.203-204.
Vayssiere, J.J.P., Saint-Paul, R.F.B. and Benatallah, B., SAP SE, 2014. Adapting a spreadsheet
for use with a complex object. U.S. Patent 8,656,271.
Wood, D. and et.al., 2014. Linked Data. Manning Publications Co..
Young, M.S. and et.al., 2015. State of science: mental workload in
ergonomics. Ergonomics. 58(1). pp.1-17.
Online
Lin, E., 2017. 7 Popular Business uses for Microsoft Excel. [Online]. Available through: <
http://www.classycareergirl.com/2016/05/business-uses-for-microsoft-excel/>.
Document Page
1 out of 15
[object Object]

Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

[object Object]