Pivot Tables allow you to create reports on a new spreadsheet from data stored on a...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Pivot Tables allow you to create reports on a new spreadsheet from data stored on a separate, often large spreadsheet. Employers tell us that knowing how to create and manipulate Pivot Tables is an essential skill for their businesses. For this exercise you do not need to type in the data. It is provided to you. Open the "Excel Student Spreadsheets BUS 1011." Look to the bottom of the spreadsheet and click the tab titled "Pivat Table Data." There you will find a spreadsheet that has two additional Columns: "2 Products," which lists the two key products sold by the stores-Apple's iPhone and the Samsung Galaxy, and "4 Quarters," which lists sales (revenues) for each of the four quarters in a year. Manipulating this data will allow you to answer the following questions: 1. Which store-the one in Queens, Brooklyn or the Bronx-was the most profitable? Which one was the least profitable? 2. Which quarter has the most sales (revenue) overall? 3. Which product-the Apple iPhone or the Samsung Galaxy-was more profitable overall? Calibri 20 A- Av A A Paste B. IU abe X, x A A 2+ To answer these questions: • Click on any cell (say A3) • Click on "Insert," then click "PivotTable" on the Create PivotTable Choose the data that you want to analyze extreme left. The following "Create PivotTable" box O Select a table or range should appear. Iable/Range: vor Table Data' SASSES2 • Simply click "OK," choosing the defaults. • A new worksheet will be created. Rename the tab "PT Analysis." • On the right-hand side of the spreadsheet, a box titled "PivotTable Field List/Builder" appears. e an external data source Connecton name Choose where you want the PvotTable report to be placed O New Worksheet Disting Worksheet • For Macs: Click "Summarize with Pivot Table of "Data." "Create Pivot Table" appears. Location: OK Cancel Page 12 ot 20 4177 Words English (US) 個 即 A, * - A - A O 国 , Pasta abe X X Styles PivotTable Field List Let's start by answering the question: Which store-the one in Queens, Brooklyn or the Bronx--was the most profitable? Choose fields to add to reporti Profit = Revenue Expenses • Click on/check the box for "Profits." • Then, click on/check the box for "3 Stores." MStores • Note that "Sum of Profit =" should appear under Values, 2 Products • "3 Stores" should appear under Row Labels. • A Pivot Table should appear in the upper left-hand corner. 4Quarters • Drag-and-drop flelds/variables to manipulate the Pivot Table and arrive at the rest of the answers. Now you can answer the questions: Drag felds between areas belowt • Which store is the most profitable overall? Which store is the least V Report Fiter O Column Labels profitable? • Which quarter has the highest sales revenue overall? • Which product was more profitable overall? Row Labels E values As an added step, try clicking on the "2 Products" box in the PivotTable 3Stores Sum of Profit Field List. This is, you will now have a total of four boxes selected- Profit, 3 Stores, 4 Quarters and 2 Products. Defer Layout Update Update Note: you DON'T need to create separate Pivot Tables for each question, simply manipulate the one you created and answer the questions. Page 13 of 20 4177 Words English (USI 130% B. Pivot Tables allow you to create reports on a new spreadsheet from data stored on a separate, often large spreadsheet. Employers tell us that knowing how to create and manipulate Pivot Tables is an essential skill for their businesses. For this exercise you do not need to type in the data. It is provided to you. Open the "Excel Student Spreadsheets BUS 1011." Look to the bottom of the spreadsheet and click the tab titled "Pivat Table Data." There you will find a spreadsheet that has two additional Columns: "2 Products," which lists the two key products sold by the stores-Apple's iPhone and the Samsung Galaxy, and "4 Quarters," which lists sales (revenues) for each of the four quarters in a year. Manipulating this data will allow you to answer the following questions: 1. Which store-the one in Queens, Brooklyn or the Bronx-was the most profitable? Which one was the least profitable? 2. Which quarter has the most sales (revenue) overall? 3. Which product-the Apple iPhone or the Samsung Galaxy-was more profitable overall? Calibri 20 A- Av A A Paste B. IU abe X, x A A 2+ To answer these questions: • Click on any cell (say A3) • Click on "Insert," then click "PivotTable" on the Create PivotTable Choose the data that you want to analyze extreme left. The following "Create PivotTable" box O Select a table or range should appear. Iable/Range: vor Table Data' SASSES2 • Simply click "OK," choosing the defaults. • A new worksheet will be created. Rename the tab "PT Analysis." • On the right-hand side of the spreadsheet, a box titled "PivotTable Field List/Builder" appears. e an external data source Connecton name Choose where you want the PvotTable report to be placed O New Worksheet Disting Worksheet • For Macs: Click "Summarize with Pivot Table of "Data." "Create Pivot Table" appears. Location: OK Cancel Page 12 ot 20 4177 Words English (US) 個 即 A, * - A - A O 国 , Pasta abe X X Styles PivotTable Field List Let's start by answering the question: Which store-the one in Queens, Brooklyn or the Bronx--was the most profitable? Choose fields to add to reporti Profit = Revenue Expenses • Click on/check the box for "Profits." • Then, click on/check the box for "3 Stores." MStores • Note that "Sum of Profit =" should appear under Values, 2 Products • "3 Stores" should appear under Row Labels. • A Pivot Table should appear in the upper left-hand corner. 4Quarters • Drag-and-drop flelds/variables to manipulate the Pivot Table and arrive at the rest of the answers. Now you can answer the questions: Drag felds between areas belowt • Which store is the most profitable overall? Which store is the least V Report Fiter O Column Labels profitable? • Which quarter has the highest sales revenue overall? • Which product was more profitable overall? Row Labels E values As an added step, try clicking on the "2 Products" box in the PivotTable 3Stores Sum of Profit Field List. This is, you will now have a total of four boxes selected- Profit, 3 Stores, 4 Quarters and 2 Products. Defer Layout Update Update Note: you DON'T need to create separate Pivot Tables for each question, simply manipulate the one you created and answer the questions. Page 13 of 20 4177 Words English (USI 130% B.
Expert Answer:
Answer rating: 100% (QA)
Answer 1 Answer 2 Answer 3 Row Labels Sum of Profit Bronx 4600 Brook... View the full answer
Related Book For
Fundamentals of Investing
ISBN: 978-0133075359
12th edition
Authors: Scott B. Smart, Lawrence J. Gitman, Michael D. Joehnk
Posted Date:
Students also viewed these databases questions
-
Let G stand for government spending, T for taxes, I for private investment, and S for private saving. Complete the following equation for the current account deficit: Current Account Deficit .) + (...
-
Create two frequency tables based on two separate questions from your survey. Create a bar graph and a pie chart based on the data in the frequency tables. Determine the class intervals and create...
-
We are given the following information for UWI Land. At the start of the period, there are 334,250 employed workers, 15,750 unemployed and 150,000 not in the labour force. During the period 500...
-
Suppose the comparative balance sheets of Sage Hill Inc.. are presented here. SAGE HILL INC. Condensed Balance Sheet May 31 ($ in millions) 2019 2018 Assets Current Assets $9,680 $8,760 Property,...
-
Determine whether f is even, odd, or neither. If you have a graphing calculator, use it to check your answer visually. F(x) = x /x2 + 1
-
In the toy water-rocket of Problem 64, the pressurized air can store internal energy. Assume you have a rocket that expels water at a speed of \(15.0 \mathrm{~m} / \mathrm{s}\) and the opening...
-
According to Equation 14.4, if expectations about the future exchange rate suddenly fall, all other things constant, what should happen to E? E = exp(E++1) i-+1
-
Since the club opened, a major concern has been the pool facilities. Although the existing pool is adequate, Mindy, Oscar, and Lori all desire to make LifePath a cutting-edge facility. Until the end...
-
17. Mass of Jupiter Io is one of the moons of Jupiter. It has a semi- major axis of 0.042 100 m and an orbital period of 1.769 days. Use these data to estimate the mass of Jupiter.
-
A polling company reported that 27% of 2302 surveyed adults said that they play baseball. Complete parts (a) through (d) below. a. What is the exact value that is 27% of 2302? The exact value is...
-
What is meant by universal quantifier and existential quantifier? Give an example
-
In January 2022 Karl sells a one-quarter interest in a chattel for 2,500. On the date of this sale, the remaining three-quarters interest is valued at 8,500. The chattel had cost Karl 3,850 in...
-
Mohammed owns two properties - a flat in Central London and a country cottage in Sussex. In general he lives in his London flat during the week and spends the weekends in his Sussex cottage. Which of...
-
A company's income statement for the year to 31 March 2022 is as follows: 1. The company acquired 240,000 of 10% debentures (for non-trade purposes) on 1 January 2022. Interest is receivable...
-
In October 2021 Keith sells an antique cabinet for 7,200. He incurs incidental costs of disposal amounting to 200. The cabinet cost Keith 2,300 in July 2013. Compute the chargeable gain.
-
Luke Steffie owns a balloon store and must fill balloons with helium and assemble them into certain configurations today for six major parties. His six customer jobs all need to use the same helium...
-
Simulate and Animate a Foucault Pendulum at the North Pole A Foucault pendulum demonstrates the rotation of the Earth. The pendulum has a long cord and swings in a Newtonian frame since it is mounted...
-
On average there are four traffic accidents in a city during one hour of rush-hour traffic. Use the Poisson distribution to calculate the probability that in one such hour there arc (a) No accidents...
-
The SP-6 index (a fictitious index) is used by many investors to monitor the general behavior of the stock market. It has a base value set equal to 100 at January 1, 1975. In the accompanying table,...
-
What strategy would you expect an aggressive bond investor (someone whos looking for capital gains) to employ?
-
Describe how, if at all, conservative and aggressive investors might use each of the following types of transactions as part of their investment programs. Contrast these two types of investors in...
-
(a) A car is speeding up in the negative \(x\) direction. In what direction do \(\vec{a}\) and \(\vec{v}\) point? (b) To which of the four graphs in Figures 3 . 2 and 3 . 3 does the situation...
-
The \(x\) component of the velocity of a car changes from \(-10 \mathrm{~m} / \mathrm{s}\) to \(-2.0 \mathrm{~m} / \mathrm{s}\) in \(10 \mathrm{~s}\). (a) Is the car traveling in the positive or...
-
A classmate leaves a message on your voice mail betting that you cannot throw a stone high enough so it lands on the roof of a 20 -m-high building. As you stare out of your window pondering whether...
Study smarter with the SolutionInn App