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,...
-
How do detailed action plans contribute to individual ownership of a marketing plan?
-
What are the advantages and disadvantages of the corporate form of business organization?
-
Show that \[ \begin{aligned} & P_{H_{1}}\left(\frac{\bar{X}-\mu_{0}}{\frac{S}{\sqrt{n}}} \geq t_{n-1,1-\alpha / 2} ight)+P_{H_{1}}\left(\frac{\bar{X}-\mu_{0}}{\frac{S}{\sqrt{n}}}
-
A radioactive material produces 1280 decays per minute at one time, and 4.6h later produces 320 decays per minute. What is its half-life?
-
Calculate the momentum of a 265 kg motorcycle traveling at 25 m/s. Two clay balls collide and stick. Ball 1 has a mass of 10 kg and an initial velocity of 10 m/s in the positive x direction. Ball 2...
-
Sebastian Trains Co (ST) is a UK based rail operating company which holds three UK rail franchises. The franchises allow ST to run trains on the rail network in a particular geographical area within...
-
A petty cash fund was established with a $600 balance. It currently has cash of $150 and petty cash tickets as shown below: Travel expense $100 Office supplies expense 310 Equipment rental expense 40...
-
Think about any activity that you carry out during the week (e.g. travel to college, eating meals, washing clothes). How would you measure the volume of that activity in a week? How might the cost of...
-
In Problems 23-34, find the value of each annuity at the end of the indicated number of years. Assume that the interest is compounded with the same frequency as the deposits. Amount of Deposit m 28....
-
Find the monthly payment for the loans in Problems 33-44. A \(\$ 258,000\) condominium bought with a \(30 \%\) down payment and the balance financed for 30 years at 3.05%
-
Explain the meaning of activity and output.
-
Use the advertisement shown in Figure 11.12 as a basis for answering Problems 14-20. Assume the current interest rate is \(1.7 \%\). Figure 11. 12 Suppose you take the \(\$ 300,000\) cash payment and...
-
execution. For the MIPS code below, what will be the contents of resister $t0 after addi $s0, $zero, OxA2 addi $s1, $zero, Ox3C sltu $to, $s1, $s0
-
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...
-
The current price of a stock share that pays no dividend is \( 50\). The price follows a GBM with drift \(12 \%\) and volatility \(35 \%\); the continuously compounded risk-free rate is \(5 \%\)....
-
An immediate consequence of Eq. (13.26), is that (modulo a discount factor) it gives, for free, the BSM price of a digital (or binary) option, i.e., an option paying \(\$ 1\) if . Using the indicator...
-
The approximation of Eq. (13.31) is sometimes suggested as a possible way to approximate V@R of option portfolios. For instance, with the data of Example 13.10, we may apply the following first-order...
Study smarter with the SolutionInn App