Business Dashboard Spreadsheet Report: Analysis and Data Presentation

This report focuses on the creation and use of a dashboard spreadsheet for Australian Electronics Pty Ltd. It details the purpose of the spreadsheet, which is to organize data for better understanding and presentation, including both tabular and graphical formats. The report explains the layout, data input methods, and the calculation of key financial ratios such as inventory turnover, sales growth, inventory to sales, and gross profit ratio, providing insights into the company's financial health and performance. Furthermore, it includes sections on preparing instruction sheets, data input sheets, and data processing sheets, demonstrating how to use Excel for data analysis, presenting sales trends, and assessing profitability. The report concludes by emphasizing the usefulness of Excel in presenting large datasets in a structured manner and assessing the financial performance of the company.

Dashboard spreadsheet data excel

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................1
1. Preparing an instruction sheet..................................................................................................1
Purpose of spreadsheet................................................................................................................1
Description of layout...................................................................................................................1
Place where data is entered..........................................................................................................1
Ratios...........................................................................................................................................1
Description of other information.................................................................................................2
2. Preparing data input sheet........................................................................................................3
3. Constructing the data processing sheets..................................................................................3
CONCLUSION................................................................................................................................3

INTRODUCTION
Dashboard may be served as a management tool that provides information about business
in a clear and structured way. The present report is based on Australian Electronics Pty Ltd that
offers technical products to the customers. In this, report will furnish information about the
purpose of spreadsheet and its use in the creation of dashboard.
1. Preparing an instruction sheet
Purpose of spreadsheet
The main purpose behind using spreadsheet is that it organizes data into row as well as
column and thereby makes it highly understandable. By using spreadsheets, research analyst or
manager of the firms including Australian Electronics Pty ltd can presents data attractively in a
graphical format. In addition to this, spreadsheet provides various options such as sort, filter,
conditional formatting etc. that can be used to discover the suitable information and present the
same in a prominent way.
Description of layout
Layout of dashboard includes both tabular and graphical presentation. Hence, with the
help of excel tables have been created through using data related to inventory and sales. In
addition to this, with the aim to develop better understanding graphs have also been included
with the help of excel.
Place where data is entered
In the excel sheet, data is entered with the motive to simply the same and present it in a
structured way. Besides this, to apply the formulas such as sum, average etc. on sales and stock
figure excel sheet has been used. Thus, analyst needs to enter data in excel for deriving suitable
information.
Ratios
As per the given case scenario four ratios pertaining to Australian Electronics Pty Ltd. are
calculated such as inventory turnover, inventory to sales, sales growth etc. The rationale behind

the inclusion of such four ratios is that they furnish information about the extent to which firm is
performing well. All the ratios related to company’s financial health and performance is
calculated by using excel. Hence, by inserting appropriate formula pertaining to the firm’s
efficiency calculated.
Inventory turnover ratio: The ratio is derived through dividing the cost of goods sold
with the average stock available at the end to the firm. It gives a clear picture about how
efficiently and effectively firm is utilizing their inventory so as to generate sales revenue quickly.
Sales Growth: It reflects percentage change in the total sales value of Limit Switch,
Circuit Breaker and Transmission Line that helps to identify that whether sales got increase or
decrease over the period. It helps Australian Electronics Pty Ltd mangers to make rationalized
and smarter plans and decisions so as to maximize their sales revenue.
Inventory to sales: The ratio enable firm managers to find out that whether stock is
undervalued or overstocked. The ratio is computed by dividing inventory in hand with the sales
value. The rationale behind using the ratio is that holding too much stock resultant high stocking
cost whereas in contrast to this, in the case of under stocking, firm will not be able to satisfy their
customer demand
Gross profit ratio: In excel, GP ratio has been calculated by dividing gross profit from
sales revenue. The main reasons behind including this ratio are that it helps business organization
in ascertaining the level of profit margin. By calculating this, firm can assess the margin that was
generated by it during the period of 6 months.
Description of other information
In this, information about sales trends have also been included which in turn clearly
reflects the trend pertaining to same. In dashboard table of sales trend has been inserted that
presents whether sales of limit switch, transmission line and circuit breaker of Australian Pty
increased or decreased. Further, graph related to sales data also has been included to clearly
reflect the sales trend.

2. Preparing data input sheet
Enclosed in excel.
3. Constructing the data processing sheets
Enclosed in excel.
CONCLUSION
From the above report, it has been included that excel presents large data set in a
structured way. Besides this, it can be inferred that to assess the financial performance of
Australian Electronics Pty Ltd sales trend, profitability and inventory ratios are calculated.