Data Analytics and Accounting Flashcards
1
Q
What is structured data?
A
What you mean to collect
Ex: customer sales
2
Q
What is unstructured data?
A
Driven from internet-enabled actions and connected devices
Not intentionally collected?
3
Q
How many % of data today is unstructured?
A
Almost 90%
4
Q
What revolution are we currently experiencing?
A
A big data revolution, alongside the digital revolution
5
Q
How much of the world’s data has been produced recently?
A
The vast majority of data has been created in the last few years
6
Q
What everyday activities generate data?
A
Checking email, driving a car, checking out at a grocery store, and many other actions.
7
Q
How is data transforming society?
A
It shapes what we know, how we act, and how we make decisions.
8
Q
What challenge do organizations face with data?
A
Extracting meaning from data and making decisions based on findings.
9
Q
Why is data analysis important for a company?
A
It reduces decision uncertainty by uncovering unknown opportunities or threats.
10
Q
How can data analysis contribute to a competitive advantage?
A
By identifying insights that help a company differentiate itself and make strategic decisions.
11
Q
What must data analyses focus on to be effective?
A
Clearly defined knowledge and performance goals that align with organizational objectives.
12
Q
Why is understanding customer needs important?
A
It helps businesses anticipate expectations and improve customer satisfaction.
13
Q
What is customer segmentation?
A
The process of analyzing customer behavior to create descriptive profiles.
14
Q
How can businesses use customer profiles?
A
To develop personalized marketing strategies that target specific segments.
15
Q
How precise can customer targeting be?
A
Data-driven marketing can be so specific that a segment can be just one customer.
16
Q
What types of data help with customer segmentation?
A
Purchase history, demographics, preferences, and online behavior.
17
Q
Why is it important to analyze product sales?
A
To identify which products are selling well and adjust inventory or marketing strategies.
18
Q
How can data help determine product profitability?
A
By analyzing profit margins to focus on high-margin products.
19
Q
How can businesses use data to identify product relationships?
A
By finding cross-selling opportunities, such as customers who buy one product being likely to buy another.
20
Q
What is data mining in product analysis?
A
The use of algorithms to find patterns, like product recommendations in online stores.
21
Q
How can data improve production and inventory control?
A
By tracking demand trends to optimize stock levels and reduce waste.
22
Q
How can having more customer data than competitors be an advantage?
A
It allows for better-targeted marketing and personalized strategies, improving customer retention.
23
Q
What insights can be gained from analyzing competitors’ social media use?
A
Understanding their engagement strategies, promotions, and customer interactions to refine your own approach.
24
Q
Why is search engine ranking important in competition?
A
Higher rankings lead to more visibility, increased traffic, and potentially higher sales.
25
How can new competitors affect sales projections?
They can reduce market share, change pricing dynamics, and shift customer preferences.
26
How can businesses use competitor analysis for strategic planning?
By identifying gaps in the market, adjusting pricing, and improving offerings to stay competitive.
27
How do market trends impact an industry?
They influence consumer demand, pricing strategies, and business innovation.
28
Why is it important to track the global economy?
Economic conditions affect consumer spending, investment decisions, and supply chain costs.
29
How does the economy impact consumer purchases?
In a strong economy, consumers spend more; in a weak economy, they cut back on non-essential goods.
30
What factors determine where new market segments will emerge?
Population growth, income levels, technological advancements, and cultural shifts.
31
How can businesses adapt to changing economic conditions?
By adjusting pricing, optimizing costs, diversifying revenue streams, and exploring new markets.
32
Why is it important to combine multiple data sources?
It provides a more complete picture for better decision-making.
33
What are some examples of different types of data sources?
Satellite imagery, social media, RFID tags, medical records, e-commerce transactions, and surveillance data.
34
Why is real-time data analysis valuable?
It enables quick reactions to trends, risks, and opportunities.
35
How can data help the healthcare industry?
To improve the ability to classify and treat cancer and other diseases and the associated effectiveness of medications, and to conduct survival analysis, mine images for information
36
How can data help the airline industry?
To assist pilots in aviation and increase revenue and customer satisfaction by discovering passengers who are likely to miss their flight
37
How can data help the TV and Media industry?
To analyze audiences and make predictions about which shows will be popular
38
How can data help the bank cards industry?
To detect fraud and customer usage (How likely is it that Bob Smith is now purchasing large items in Istanbul?) and to predict customer behavior when interest rates rise
39
How can data help the call center industry?
To apply inferred customer social relationships
40
How can data help the loaning industry?
To adjust credit scores as transactions are occurring and account for risk fluctuations
41
What is data mining?
The process of searching large datasets for patterns using computational techniques.
42
Why do organizations use data mining?
To identify trends, improve decision-making, and optimize operations.
43
What is numeric and text data mining?
Analyzing structured numbers and unstructured text for insights.
44
How does impact measurement work?
It assesses the effectiveness of business strategies and interventions.
45
What are some of the ways data mining can make critical business decisions?
Numeric and text data mining
Impact measurement
Store assortment and stock-keeping unit optimization
Marketing optimization
Marketing mix modeling
Web analytics
Sales force sizing and optimization
Price and promotion modeling
Credit risk analysis
Fraud analytics
46
What unexpected pattern did the midwestern grocery store discover through data mining?
The store found that men were coming in on Thursdays to buy diapers and often bought beer at the same time.
47
How did the store use the discovery about beer and diapers?
The store placed beer closer to the diaper display and made strategic advertising decisions. They also sold both products at full price on Thursdays without discounts.
48
What type of analysis helps identify patterns like the one between beer and diapers?
Data mining helps uncover patterns and associations between products and customer behaviors.
49
What are the types of reliable findings that data analysis can uncover?
Data analysis can uncover associations, sequences, classifications, anomalies and fraud, grouping/clusters/segments, and forecasts.
50
How does the grocery store example demonstrate the concept of "associations" in data analysis?
The store discovered an association between the purchase of diapers and beer, which led to strategic decisions about product placement and pricing.
51
What role do forecasts play in data mining and analysis
Forecasts predict future trends and behaviors, helping businesses make informed decisions, such as demand for products on specific days.
52
What is the primary goal of data mining?
The primary goal of data mining is to extract knowledge from data by identifying patterns and establishing relationships to solve problems.
53
How do data mining tools help businesses?
Data mining tools allow businesses to predict future trends by analyzing large datasets to uncover hidden patterns and relationships.
54
What are the two main types of data mining techniques?
The two main types of data mining techniques are predictive and descriptive.
55
What is the purpose of predictive data mining?
Predictive data mining is used to forecast future outcomes by extrapolating information based on present data, such as analyzing past customer transactions to predict future behaviour.
56
How does descriptive data mining differ from predictive data mining?
Descriptive data mining is used to uncover hidden patterns and information that are already present in the data, such as finding new clusters or associations that weren’t initially obvious.
57
Can you give an example of descriptive data mining?
An example of descriptive data mining is discovering previously unknown associations between products or identifying customer clusters in a database.
58
What challenges and opportunities does the rapidly expanding world of data present?
The expanding world of data, particularly unstructured data, presents organizations and society with opportunities for innovation but also challenges in handling and processing the data effectively.
59
How have technological innovations contributed to the big data revolution?
Technological innovations have driven advances in computational data processing, enhancing data analytical capabilities and making it easier to extract insights from large datasets.
60
Despite advances in technology, what role does human decision-making still play?
Human decision-making remains central, even in technologically advanced societies, as humans guide and interpret insights provided by data analytics.
61
What is the primary focus of developed data analytics?
Developed data analytics focuses on using objective data insights to support and guide human decision-making, such as evaluating competing business ideas or marketing campaigns.
62
What kind of data is typically used in developed data analytics?
Developed data analytics often uses states and events-recording data, such as sales records, to compare different courses of action or identify key factors influencing outcomes.
63
What is the goal of emergent analytics?
Emergent analytics aims to use data to drive automated or autonomous processes, focusing on real-time responses and control of systems, such as in self-driving vehicles.
64
What types of data are used in emergent analytics?
Emergent analytics uses continuously streaming data from sensors, surveillance systems, tracking devices, and social media, with a focus on accuracy, completeness, and timeliness.
65
How is accuracy, completeness, and timeliness valued in emergent analytics?
In emergent analytics, the value lies in the real-time nature of data, where timely and accurate data flow is crucial for immediate response and process control.
66
How does big data impact accounting?
Big data influences accounting by improving budgeting, planning, and forecasting, helping businesses determine strategy, reduce operational costs, detect fraud, and shape financial statement preparation and auditing.
67
Why are accountants well positioned to lead the application of big data in organizations?
Accountants are already immersed in IT, financial systems, and reporting, and serve as intermediaries between management, operations, and external stakeholders, giving them the expertise needed to leverage big data effectively.
68
What types of data do accountants need to derive information from?
Accountants need to derive information from both structured data (e.g., transactional data) and unstructured data (e.g., emails, telephone calls, Wi-Fi sensors, GPS data, images, and video).
69
How is big data used in financial accounting?
In financial accounting, big data is integrated into accounting information systems, helping to monitor inventory in real-time, assess valuations, and automate daily pricing and valuation workflows.
70
How can big data improve inventory management in financial accounting?
By monitoring inventory in real time, big data allows accountants to implement more accurate inventory counts and assessments of inventory valuation.
71
How is big data used in auditing to improve accuracy and efficiency?
Big data is used in auditing to implement database-to-database verification with independent trading partners and replace manual audit confirmations with automatic ones.
72
How does the SEC use big data in auditing?
The SEC uses big data to monitor around one billion records a day from the 13 national equity exchanges, time-stamped to the microsecond, to detect securities law violations and audit failures.
73
What is the advantage of automatic audit confirmation over manual confirmation?
Automatic audit confirmation improves efficiency and accuracy by eliminating the need for manual processes, ensuring quicker and more reliable confirmation of audit details.
74
How does big data help the SEC in detecting audit failures?
By analyzing a vast amount of time-stamped records from national equity exchanges, the SEC can detect potential securities law violations and audit failures more effectively and in real time.
75
How are data analytics tools changing the role of managerial accountants?
Data analytics tools are moving the role of managerial accountants towards predictive functions, allowing them to incorporate nontraditional data sources, like news and social media, to make more informed decisions.
76
How can analyzing customer profitability and shopping habits benefit companies?
Analyzing customer profitability, shopping habits, and shipping locations helps companies identify purchase patterns, trends, and irregularities in real time, allowing for better decision-making.
77
How does the Internet of Things (IoT) help businesses improve operations?
The IoT allows businesses to gather better sales data, minimize downtime, reduce operational costs, and target customers more effectively by collecting and exchanging data from connected devices.
78
How can retailers and hospitality companies optimize labor costs using big data?
By analyzing weather and historical/seasonal sales data, retailers and hospitality companies can predict optimal staffing levels and adjust labor costs accordingly.
79
How can hotel owners use customer reviews to improve business decisions?
Hotel owners can track ranking changes on platforms like TripAdvisor to anticipate changes in room demand and adjust their strategies accordingly.
80
How did economist Orley Ashenfelter use big data to predict wine quality?
Orley Ashenfelter used big data to predict the quality of wine by analyzing factors like rainfall during winter and harvest seasons, as well as average growing season temperatures, to forecast wine quality.
81
What is "dark data" and how can monitoring it benefit a company?
Dark data refers to information a company already collects but doesn’t use, such as emails and phone calls. Monitoring dark data can help detect violations before they occur, improving risk management.
82
How can big data help identify motivational measures in a company?
Big data can measure morale through the tone of emails and phone conversations, productivity by counting emails sent by managers, and customer satisfaction by analyzing body language captured on video.
83
How can big data supplement HR processes in talent acquisition?
Big data can reduce talent acquisition costs by using social media data to identify factors like potential employee relocation due to a spouse’s job, helping companies target the right candidates more efficiently.
84
How can big data help companies identify fraud in travel and expense data?
By supplementing travel and expense data with big data, companies can identify unusual patterns and activities that may indicate potential fraud.
85
How can artificial intelligence (AI) be used in revenue assessment?
AI can compare ongoing revenues with projections, identifying discrepancies and suggesting corrective actions, such as how Amazon's Alexa can brief executives on demand forecasts.
86
What are the potential negative consequences of using big data in decision-making?
While big data can provide valuable insights, it can also lead to unintended, negative consequences when used unfairly or irresponsibly, especially when predicting human behavior, opinions, or goals.
87
How can data models create the future instead of just predicting it?
Data models can create the future when they influence people's behavior, opinions, or goals, leading individuals or groups to act in ways that align with the predictions, rather than simply reflecting existing reality.
88
How is measuring tangible outputs, like water quality, different from predicting human behavior using big data?
Measuring tangible outputs like water quality involves analyzing clear inputs (e.g., rain amounts, chemicals) and refining models with real data. In contrast, predicting human behavior is more complex and can lead to inaccurate models that influence people's actions negatively.
89
Why are models focused on human behavior more prone to error compared to those predicting physical outcomes?
Models predicting human behavior can be inaccurate because human actions are influenced by a wide range of unpredictable and complex factors, making it more difficult to account for all relevant inputs. This can lead to models that create, rather than predict, outcomes.
90
What does the saying "garbage in, garbage out" mean in the context of big data?
It means that if invalid or poor-quality data is used as input into a model, the resulting outputs will be useless or misleading.
91
Why is it crucial to understand the data input into a model?
Failure to understand the data input into a model leads to a misunderstanding of the results, potentially causing incorrect or harmful conclusions.
92
How are machines different from humans in terms of adapting to data?
Machines can only process the data they are given and cannot adapt or learn on their own. Unlike humans, automated systems stay stagnant unless they are regularly updated.
93
What problem could arise if a big data model used outdated or biased data, such as one from the 1960s for college admissions?
If a model used data from the 1960s, it would likely be biased, potentially excluding women from the analysis, since the data primarily involved men, reinforcing outdated and unjust conclusions.
94
Why is it important to interrogate the data collection process when building models?
It's essential to interrogate the data collection process to ensure the data is accurate, representative, and relevant, which is crucial for building valid and reliable models.
95
What role does skepticism play in data science?
Being skeptical about the data and the models used ensures that the inputs and processes are rigorously understood and defended, which is essential for the integrity of data science.
96
What is a major problem with models used for job applicant personality tests?
A major problem is that these models do not learn from their previous decisions. Once candidates are eliminated, the model forgets them and justifies their elimination, even though some may have gone on to have successful careers.
97
Why is it problematic that the model does not track eliminated candidates over time?
It’s problematic because the model doesn’t evaluate the potential success of eliminated candidates in the future, which means it misses opportunities for identifying candidates who could excel despite being initially overlooked.
98
How does the lack of self-correction affect the model?
Without self-correction, the model becomes outdated and ineffective, as it fails to adjust based on new information, leading to inaccurate decisions that can harm candidates' opportunities.
99
How can constant feedback improve the effectiveness of a model?
Constant feedback helps refine and adjust models, ensuring they remain relevant and accurate over time. This continuous improvement allows the model to adapt to new data and provide better, more fair outcomes.
100
Why is it important for models to be dynamic and adaptable?
It’s important because static models can lead to unjust or inaccurate decisions, potentially affecting individuals' lives negatively. Dynamic models, on the other hand, can evolve and improve over time, providing more accurate and fair results.
101
What was the initial goal of US News and World Report when they published college rankings?
The goal was to boost issue sales by publishing a ranking of colleges based on a basic model with factors they believed indicated prestige.
102
Why did the college rankings create a self-reinforcing feedback loop?
If a college performed poorly in the rankings, its reputation suffered, which led to worse conditions, causing top students and professors to avoid it, thus making it harder to improve its ranking.
103
How did colleges respond to their rankings in an attempt to improve their position?
Colleges dedicated significant resources and effort to improving performance in the limited areas defined by the rankings, often at the expense of other important factors.
104
What negative consequences resulted from colleges focusing on improving their rankings?
The focus on boosting rankings led to several negative outcomes, including higher tuition costs, as resources were channeled into improving specific ranking factors rather than broader educational goals.
105
How did the rankings affect colleges in a way that was not initially intended?
Instead of improving colleges in a balanced way, the rankings created pressure to prioritize certain factors, which led to negative changes, like rising tuition costs and a narrow focus on specific metrics, rather than overall educational quality.
106
How is data analysis applied in crime prevention, and what issues can arise?
Data analysis is used to predict where and when crimes might occur by analyzing historical patterns. However, using nuisance crimes like loitering or panhandling in the models can lead to unfair predictions and reinforce bias in policing, particularly in impoverished neighborhoods.
107
What negative feedback loop can occur in predictive policing models?
The use of biased crime data, such as from impoverished neighborhoods, draws police into these areas, leading to more arrests and more data. This cycle reinforces itself, leading to over-policing in certain areas and contributing to higher incarceration rates without effectively reducing crime.
108
How can predictive policing models unfairly target certain communities?
By including crimes that are more likely to occur in impoverished areas, like loitering or small drug offenses, predictive models may unfairly target these neighborhoods, exacerbating existing inequalities in law enforcement practices.
109
What is the ethical issue surrounding the NSA's wiretapping practices, according to critics?
The criticism centers on the balance between security and privacy. The NSA defends wiretapping as a means to detect more crime, but the ethical concern is whether sacrificing privacy and individual rights for increased data collection is justified.
110
In data modeling, what is the ethical question that should be asked regarding fairness and efficiency?
The ethical question is whether we should sacrifice some efficiency in a model in order to ensure fairness. This is especially relevant when data collection or predictive models disproportionately impact certain groups, leading to potential harm or bias.
111
What was the main problem with the teacher evaluation system based on early statistical models?
The main problem was that the models used to identify "bad" teachers were based on flawed data and statistical errors, leading to the wrongful firing of teachers who were well-liked and effective.
112
What was the error in the report "A Nation at Risk" that led to incorrect conclusions about teacher performance?
The error was that the report linked declining SAT scores to poor teaching, but it overlooked the fact that the increase in the number of students taking the test, including more poor students and minorities, was a major factor in the drop in average scores.
113
What is Simpson’s paradox, and how did it apply to the teacher evaluation data?
Simpson’s paradox occurs when a trend appears in the overall data, but disappears or reverses when the data is broken down into subgroups. In this case, overall SAT scores dropped, but when broken down by income groups, scores were rising for each group.
114
How did the initial teacher evaluation models fail to account for changes in student demographics?
The models failed to account for the fact that a larger and more diverse group of students were now taking the SATs, which affected the average scores but did not reflect a decline in teaching quality.
115
What was the impact of continually testing and monitoring students in the teacher evaluation process?
The constant testing and monitoring led to many complaints from both teachers and students, contributing to a negative environment and potentially inaccurate evaluations based on flawed data.
116
What is Market Basket Analysis, and how is it used in behavioral prediction?
Market Basket Analysis is a data analytic tool used to predict customer behavior by grouping people with similar purchasing patterns, assuming that individuals with similar preferences will tend to buy similar items.
117
What problem can arise when correlation is confused with causation in data models?
Confusing correlation with causation can lead to inaccurate predictions and decisions. For example, poor people might be more likely to have bad credit or live in high-crime neighborhoods, but these are correlated, not causally linked. This misinterpretation can result in unfair assumptions, such as higher insurance rates based on location rather than individual behavior.
118
How can poor people be unfairly affected by data models, as seen with auto insurance rates and subprime loans?
Poor people are often categorized by data models based on their living environment, leading to higher insurance rates and targeted marketing for subprime loans. This can worsen their financial situation, further damaging their credit and limiting their opportunities, such as access to jobs.
119
What is the risk of self-fulfilling data modeling spirals in predictive models?
Self-fulfilling data modeling spirals occur when biased or flawed models reinforce negative outcomes. For example, targeting poor individuals for subprime loans can lead to worsening credit, which then justifies the original high-risk categorization, creating a cycle of disadvantage.
120
How can self-fulfilling spirals in data modeling impact individuals' lives?
These spirals can lock individuals into cycles of poverty and limited opportunities, as flawed data models perpetuate unfair decisions and restrictions, such as higher insurance rates, poor loan offers, and limited access to employment opportunities.
121
What is a potential problem with wellness programs that measure proxy variables, such as blood pressure and steps walked, to incentivize health?
The problem with wellness programs measuring proxy variables is that they may not accurately reflect an individual's overall health or wellness. These models could unfairly penalize employees who may be healthy but have a medical condition not captured by the proxy variables, leading to unjust consequences, such as higher insurance premiums or exclusion from rewards.
122
How can social media platforms like Facebook, Amazon, and Google influence society through data control?
These platforms can influence society by using hidden algorithms to target individuals with tailored content and ads. This control over data shapes how people learn, what they buy, what they see, and even how they vote, without full transparency or awareness of how personal data is being used.
123
What are the ethical concerns surrounding private data companies that collect and sell personal information?
The main ethical concerns include invasion of privacy, lack of consent, and the potential for manipulation. Companies like Cambridge Analytica used personal data without individuals’ knowledge to influence political outcomes, raising concerns about how data can be exploited for profit and control.
124
What went wrong with Michigan's unemployment fraud detection model between 2013 and 2015?
The model used by Michigan’s unemployment agency had a high false positive rate, accusing over 20,000 people of fraud, with some facing fines up to $100,000. The lack of accountability in the automated system caused significant harm to individuals' lives, exposing the risks of relying on imperfect data models for important decisions.
125
How can business models that calculate employee scheduling affect workers?
These models, while cost-effective for businesses, can leave employees uncertain about their schedules, making it difficult to plan for childcare, second jobs, or education. The unpredictability can harm employees’ work-life balance and personal stability.
126
How can models that assess employee productivity miss important variables?
Productivity models may overlook crucial soft skills, such as teamwork, creativity, or leadership, which are essential in many work environments. A model that only measures quantitative performance may inaccurately label an employee as “weak” when they are, in fact, contributing valuable skills to the organization.
127
What are some key lessons from this lesson regarding the use of data analytics models?
Key lessons include the importance of incorporating positive feedback loops in models, allowing them to learn and improve over time. Additionally, caution should be exercised when using models to predict human behavior, as they can have significant consequences on people's lives.
128
What are some of the principles outlined in the CODE OF ETHICS for data analysts?
The code emphasizes humility in recognizing that models don't always represent the real world perfectly, caution in relying too heavily on mathematical elegance without regard for real-world application, and transparency in making assumptions and oversights explicit. It also highlights the understanding that data analytics can have profound societal and economic impacts.
129
Why is it important for data analysts to explain the assumptions and limitations of their models?
It is important to ensure that users of the models understand the potential weaknesses and limitations. This transparency prevents overconfidence in the results and helps avoid misinterpretations or harmful decisions based on flawed or incomplete data.
130
What potential consequences can arise from using models to predict human behavior?
Predicting human behavior with models can lead to unintended and significant consequences, such as reinforcing biases, creating unfair outcomes, or making life-altering decisions for individuals based on inaccurate or incomplete data. This highlights the need for caution and responsibility in data analytics.
131
What is a workbook in Excel?
An Excel file is commonly referred to as a workbook. A workbook can consist of one or more worksheets.
132
What is a worksheet in Excel?
A worksheet in Excel is a collection of rows and columns (cells) where data is placed. An Excel workbook can have several worksheets if necessary. Worksheets can be quickly added by clicking on the plus icon at the bottom of the workbook. You can right click on any worksheet and access a context menu that provides you with easy access to common actions, such as deleting and renaming the worksheet.
133
What is a cell in Excel?
A cell is a specific location inside a worksheet in which you can add data, formulas, or other information. Each cell has a specific address referenced by a letter for the column and a number for the row. For instance, data found in cell D4 would be in Column D, Row 4.
134
What is a relative reference in Excel?
Relative references are used to specify a specific cell address like D4. When using relative references, and copying formulas from one cell to another, Excel will update the reference accordingly. For instance, if you copy a formula containing the reference D4 to the next row below the reference, Excel will update the reference to D5. It will also update the column D should you decide to paste the formula into a new column.
135
What is an absolute reference in Excel?
Absolute references are designed to prevent Excel from changing column and row numbers when using references to cells for formulas and calculations. To create a cell reference that prevents the column from changing, you place $ in front of the column letter, such as $D4. When this is done and the formula is copied into a new column, the letter D will not change; however if copying to a new row, the number 4 will still update. To prevent this, you would place $ before the column and before the row number. Using the cell reference as $D$4 would ensure the same cell is used over and over in your calculations. This is great when centralizing variables in your worksheets that you may want to change to perform different scenarios. Also, note that you can create an absolute reference that only keeps the row from changing like this D$4.
136
What is cell range in Excel?
At times, you might need to reference a series of values located between cells. This typically comes in handy when working with formulas and defining data to be used in a chart. An example of a range is D1:D30. Using this range would include all the data contained in cells D1 through D30.
137
What is filter in Excel?
Excel provides some nice features, when working with large amounts of data, that allow the end user to quickly perform some data analysis. The filter capability is found on the Data tab of the Ribbon bar. If you click on the row of your worksheet that contains your header, then click on the Filter feature, you will notice each column now has a drop-down option. This provides you the ability to slice and dice your data to find different scenarios very quickly in your spreadsheet.
138
What is formatting in Excel?
Any professional document created for business purposes should always consider the look and usability of the information that it contains. When it comes to formatting in Excel, you can spend countless hours designing a document that contains graphics, colors, and a variety of display capabilities to make the data pop. Some best practices include using borders, creating column headers, using page setup to set the print area, and using labels that describe the data to the users of the information. In Excel, some simple formatting can be performed quickly from the Home tab on the Ribbon bar. Another alternative is by right clicking on a cell or range of cells and choose Format Cells from the Context menu.
139
What are formulas in Excel?
To fully understand the true power and benefit of Excel in data analysis it will be essential for you to become familiar with using formulas in your worksheets. Formulas in Excel can also be referenced as functions that are either built into the application or can be custom built by advanced Excel users. Since Excel comes with a large collection of built-in functions, it is not likely that you will ever need to create your own functions.
To use formulas, you access them by going to the Formulas menu on the Ribbon bar. Excel has made it easier in more recent versions by categorizing functions according to their purpose. There is also a quick access to commonly used functions, such as AutoSum, and recently used functions. To insert a function in a cell, click on the cell, type Then in the "=" sign, and then enter the function.
140
What are charts in Excel?
When using data to tell a story, Excel provides you with the ability to create charts of many different styles and purposes. You have the option to choose from basic pie, bar and line charts, as well as pivot, scatter and surface charts for more advanced needs. When deciding what type of chart to use, you must first consider the data that you have, what you are looking to represent, and what insights you are looking to share from the data.
Adding a chart in Excel is done by first selecting the data that you would like to use in your chart. Highlight all of the rows and columns that you would like to be included. After selecting your data, then go to the Insert menu and select the type of chart that you are looking to add to your worksheet. Excel will then add the chart with default colors and labels for you. Select the chart and right click on it to access several options.
141
What is the risk of using formulas in Excel?
Formula errors are caused by formula discrepancies. For instance, you might have a formula set up to automatically calculate a figure based on other information, but the figure might not look accurate. So, in a rush to create a graph or print the data, you might insert a corrected value and forget to change it back later. Now you have a static cell in place of the formula, so this number will not automatically calculate based on other information you insert later.
142
What is the risk of using constants in Excel?
Risk is involved when using constants in formulas. For instance, you might have a weekly calculation that changes to a monthly calculation and not all the relative formulas are modified. The best remedy for this is to keep all the constants used in one location in the spreadsheet.
143
What is the risk of using hidden feature in Excel?
You can hide rows and columns in Excel, but they are still part of the spreadsheet, even when hidden. The information is still being included in the calculations, but you might not remember it is there.
144
What is the risk of using references in Excel?
It is possible to create formulas that contain references to another worksheet in your spreadsheet or another spreadsheet altogether. If these are missing, no longer available, or not under your control, Excel may not be able to update the calculated values or your information could be off.
145
What is the relationship between assets and liabilitites?
A financially healthy company tries to maintain a consistent balance between assets and liabilities. By keeping a certain balance, the company is appealing to lenders or equity investors and keeps financing costs down. A sudden change from historical norms means something has changed. This could be due to changes in management strategy or potentially fraudulent actions. A sudden increase in the ratio could mean that liabilities such as long-term debt have been hidden in off-balance sheet entities. If the value of liabilities rises, it could reveal that the company is borrowing heavily to finance operations.
146
What is the relationship between sales and COGS?
What a company sells has to be purchased, manufactured, or both, which entails a cash outlay for materials, labor, etc. Therefore, for each sale, there must be a cost associated with it. If sales increase, then the cost of goods sold generally increases proportionally.
147
What is the relationship between sales and A/R?
When a company makes a sale to a customer, there is often a lag time between the time of the sale and the collection of revenue. Therefore, the relationship between the sales and the accounts receivable is directly proportional. If sales increase, then accounts receivable should increase at approximately the same rate.
148
What is the relationship between sales and inventory?
A company holds inventory in preparation for meeting future sales. Therefore, inventory usually reflects the growth in sales. If sales increase, then inventory should increase to meet the demands of sales. Inventory that grows at a faster pace than sales might indicate obsolete, slow-moving merchandise or overstated inventory.
149
What are profit margins?
Gross, operating, and net profit margins, shown on the income statement, take into account direct and indirect costs related to producing or acquiring the products or providing services. Over time, profit margins should stay consistent. If the company must reduce the product or service price, it will have to find ways to cut expenses. Ongoing pressure on profit margins indicates pressure on management.
150
What are the 2 return on equity ratios?
Return on Assets (ROA) is defined as Net Income / Average Total Assets. This answers the following question: For each dollar of assets, how much net income does the company generate?
Financial Leverage is defined as Average Total Assets / Avg. Shareholders Equity. This answers the following question: For every dollar of shareholder investment, how many assets are they able to buy?
151
What are the 2 return on asset ratios?
Return on Sales, or ROS, is defined as Net Income / Sales and answers the following question: How much profit does the company earn on each dollar of sales?
Asset Turnover is a measure of efficiency. It is defined as Sales / Average Total Assets. This answers the question: For every dollar of assets, how much sales can it generate?"
152
What are the calculations for leverage ratios?
Financial Leverage is defined as Average Total Assets / Average Shareholders Equity.
The DuPont innovation was to notice that ROE (the two drivers of which are ROA and Leverage) is equal to Net Income / Sales x Sales / Assets x Assets / Equity. In other words, ROE equals Profitability x Efficiency x Leverage. This breakdown lets us look at whether a company's performance in their ROE is driven by their Profitability, their Efficiency, or their Leverage.
153
What is the P/E ratio?
The price-earnings ratio (P/E ratio) is the ratio of the company stock price to its earnings per share. People will use this as a quick way to try to figure out what the value of a company's stock price should be. If you look at the average P/E ratio within the company's industry or at the company's historical P/E ratio and multiply that by earnings, it gives you what the stock price should be. Then you can compare it to the actual stock price and get a sense of whether the company is over or undervalued.
154
What is profit margin?
The price-earnings ratio (P/E ratio) is the ratio of the company stock price to its earnings per share. People will use this as a quick way to try to figure out what the value of a company's stock price should be. If you look at the average P/E ratio within the company's industry or at the company's historical P/E ratio and multiply that by earnings, it gives you what the stock price should be. Then you can compare it to the actual stock price and get a sense of whether the company is over or undervalued.
155
What is asset turnover?
These ratios indicate how many times per year the company cycles through a given account.
Inventory turnover (cost of goods sold / average inventory) shows how many times a company has sold and replaced inventory.
Accounts receivable turnover (sales divided by average accounts receivable) says how many times during the year average accounts receivable was collected. It is used to assess the ability of a company to issue credit to its customers and collect funds from them on time.
Fixed asset turnover sales / average net PPE (property, plant, and equipment) tells us the sales generated for the investment in property, plant, and equipment.
Accounts payable turnover (purchases / average accounts payable) is used to see the number of times the company pays its suppliers. Purchases can be calculated by ending inventory + cost of goods sold- beginning inventory.
156
What is contribution margin?
Unlike the other financial accounting ratios discussed, contribution margin is a managerial accounting concept and is the difference between revenue and variable costs. Although the information to compute this ratio is not available outside of the organization, typically, managerial accountants can use it to evaluate profitability across product lines, geographical regions, etc. It also allows for computing breakeven analysis and the detection of common employee frauds, such as diverting sales revenues or inventory theft. Employee frauds are usually concealed in variable costs and are not seen by looking at the gross margin which considers fixed costs, as well. Contribution margin, in contrast to gross margin, should remain consistent.
157
What are short term ratios?
Unlike the other financial accounting ratios discussed, contribution margin is a managerial accounting concept and is the difference between revenue and variable costs. Although the information to compute this ratio is not available outside of the organization, typically, managerial accountants can use it to evaluate profitability across product lines, geographical regions, etc. It also allows for computing breakeven analysis and the detection of common employee frauds, such as diverting sales revenues or inventory theft. Employee frauds are usually concealed in variable costs and are not seen by looking at the gross margin which considers fixed costs, as well. Contribution margin, in contrast to gross margin, should remain consistent.
158
What are the long term ratios?
These ratios help answer questions involving company finance growth: Does it use a lot of debt financing, or raise equity from investors? They also provide measures of bankruptcy risk and borrowing capacity.
Debt to equity (total liabilities / total stockholders' equity)
Debt to assets (total liabilities / total assets)
You can also look at just the long term debt portion or just tangible assets.
159
Ratios are only useful when they're ______?
Comparative
160
Where can you find comparators for financial information/ratios?
In addition to paying for a service, there are several free ways to find comparison firms. Every company has a standard classification code for what industry they're in, such as SIC or NAICS Codes. You can search these to find out what the codes are and find other companies with the same code.
You can also look at security analysis and data services reports which will often mention the company's competitors.
You can find a large company's financial statement data on its website. There's usually an investor relations page that will have all the company's financial statements. You can also look at the SEC Edgar website where they have all of the financial filings for U.S. companies and for any non-U.S. companies that are listed in U.S. markets.
Another source you could use is one of the Internet finance sites like Google Finance, Yahoo Finance, Morningstar, or Reuters. They will all usually give you a list of related companies or industry averages.
