accounting practice test / quiz and need the explanation and answer to help me learn.
Accounting quiz on Mar 19; data set released at 10am. Quiz starts at 11am EST and closes at 11:30am EST on March 19, hence the tutor must be active on March 19 (This Sunday, from 10am to 11:30am). There will be only 6 multiple choice questions, that you must answer using the data set provided. Also, I will provide you the mock quiz (practice quiz) prior that way you can get familiar on what to expect for the actual quiz. I have provided, Supporting materials that help you to prepare for this quiz (It is mandatory, you go over them). Also the tutor must be good at using excel sheet. You must go over the video below.
Requirements: 6 multiple choice questions
ADMS2500 3.00 Introduction to Financial Accounting
Cabinet Accessory Company: Sales, Cost, and Gross Profit Analysis
Learning objectives
Find and correct errors in a data set (clean the data)
Add columns containing formulas to a data set
Create a pivot table in Excel
Create a pivot chart in Excel
Analyze sales data using a pivot table and a pivot chart
Determine the net impact on retained earnings using data from a pivot table
Create a calculated field in a pivot table
Sort a pivot table by stated criteria
Filter a pivot table
Interpret findings from a pivot table
Data set background
This dataset represents sales and cost data for a fictitious company, Cabinet Accessory Company (CAC), covering the four-year period from 2015 to 2018. CAC sells cabinet hardware including knobs and pulls. In the project, the student is asked to calculate the net impact on retained earnings each year from the sales and cost data. The project also focuses on the calculation of gross profit from a set of sales data and how that sales and cost data can be viewed from the perspective of the region, product line, etc.
Data dictionary
customer number: Description
region: There are eight regions: Central, East coast, Midwest, Northeast, Northwest, South, West, and International.
date_of_sale: Description
item: Description
brand: There are three brands: Elements, Jeffrey Alexander and Riders
collection: Description (there are several collections, all have sold more than 1 unit)
description: Description
list_price: Description
cost: Description
quantity_sold: Description
Prior to the analytics quiz date, all students will have access to:
this document with instructions
the video tutorial
the video tutorial sample dataset
the video tutorial slides and script
the dataset for a mock quiz and the mock quiz
The complete dataset will be released on Sunday March 19th at 10am, and analytic quiz will start at 11am and closes at 11:30am. Students will answer a eClass quiz with 6 questions each pertaining to variations of requirements 2 to 6:
Requirement 1 – Create three columns in the Data worksheet that calculate sales revenue, cost, and gross profit for each sales record (each row.) Use the columns already named in the Data worksheet “sales revenue,” “total cost,” and “gross profit,” respectively. Format numbers in the three columns as Accounting with two decimal places.
Requirement 2: Create a pivot table that shows sales revenue by region for each of the four years and quarters. Correct any errors in the original dataset in the worksheet that are revealed in this initial pivot table. Once you have corrected the errors in the original data set, refresh the pivot table to reflect the corrected data. Insert a pivot chart that shows the sales revenue trend by region over the four-year period. In the pivot chart, the x-axis should be years and the y-axis should be sales revenue. What trends do you see in the pivot table and pivot chart?
Requirement 3 – Create a pivot table that shows sales revenue, total cost, and gross profit for each year. What would have been the net impact on retained earnings in each of these years from the transactions listed in the data set? Ignore any tax effects.
Requirement 4 – Create a pivot table to answer questions like “What was the most profitable brand in each year, as measured by gross profit?” Link in the same pivot table gross profit percentage and sales revenue; you will need to add a calculated field to the pivot table to calculate the gross profit percentage.
Requirement 5 – Create a pivot table to answer questions like: “Within each brand, what was the most profitable collection in 2018, as measured by the gross profit percentage? The least most profitable collection for each brand?” Use the field “years” to filter the data to include just the year of 2018. You will need to use the calculated field added in requirement 4 to calculate the gross profit percentage. Within each brand, sort the collections by gross profit percentage, from the largest to the smallest. Interpret your findings.
Requirement 6 – Create a pivot table to answer questions like “Which region was the most profitable in 2018, as measured by the gross profit or its percentage?” Include gross profit (dollars) in the pivot table. Use a filter to include only sales from 2018 in this pivot table. Sort the regions by gross profit dollar or percentage, from largest to smallest.
Data Analytics Tutorial: Sales, Cost, and Gross Profit Analysis Using Excel Pivot Tables and ChartsCabinet Accessories Company (CAC) datasetWelcome to this data analytics tutorial that covers sales, cost, and gross profit analysis using pivot tables and charts in Excel. 1
Cabinet Accessories Company (CAC)•CAC is a fictitious company that sells cabinet hardware including knobs and pulls•Data set contains sales and cost data for 2014 –2018•In this tutorial, we are using a small, 36‐record data set•For the actual activity, you will be using the full data set so answers will be different but the process will be similarIn this activity, we are using a sales and cost data set for a fictitious company, Cabinet Accessories Company (CAC.) The sales and cost data covers 2014 –2018. For this tutorial only, we are using a small, 36‐record data set. For the actual activity, you will be using the full data set so your answers for the activity requirements will be different –but the process will be similar.2
Pivot tables and pivot charts•Using Office 365 Excel in Windows in this tutorial•Other versions of Excel may be slightly different•May be many ways of accomplishing the same thing –just presenting one way hereFor this tutorial on pivot tables and pivot charts, we will be demonstrating using Office 365 Excel for Windows. Other versions of Excel may be slightly different. Also note that there may be many ways of accomplishing the same thing –we are just presenting one way here. Make sure your version of Office 365 is updated; you may not see things the same way if you have not updated recently.3
Start by opening Excel workbookStart this activity by opening the Excel workbook containing the data set.4
General instructionsFor each of the requirements (except Requirement 1), create a new pivot table in a new worksheet. Name each new worksheet as “Req 2,” “Req 3,” etc. If instructed, format the dollar amounts in each pivot table or pivot chart using the accounting format with two decimal places. In general, for each of the requirements in this activity (except for Requirement 1), create a new pivot table in a new worksheet. Name each new worksheet as “Req 2,” “Req 3,” etc. If instructed, format the dollar amounts in each pivot table or pivot chart using the accounting format with two decimal places. 5
Requirement 1Createthree columns in the Data worksheet that calculate sales revenue, cost, and gross profit for each sales recordRequirement 1 asks “Create three columns in the Data worksheet that calculate sales revenue, cost, and gross profit for each sales record.”6
Req 1: Create 3 columns#1: Enter the formula for sales revenue, which is =h2*j2 (point to the cells rather than typing them in)For the first step in the first requirement, to go Cell K2 in the Data worksheet, which is the cell under the column heading sales revenue. Enter the formula for sales revenue, which is =h2*j2. Point to the cells rather than typing them in.7
Req 1: Create 3 columns#2: Enter the formula for total cost, which is =i2*j2For the second step, click in Cell L2, which is right below the column heading total cost. Enter the formula for total cost, which is =i2*j2. Again, point to the cells rather than typing in the names.8
Req 1: Create 3 columns#3: Enter the formula for gross profit, which is sales revenue minus total cost or =K2−L2For the third column, click in cell M2, which is the cell right below the column heading of gross profit. Enter the formula for gross profit, which is sales revenue minus total cost or=K2−L2 (again, point to the cells rather than typing them in –there is a lot less potential for error that way.)9
Req 1: Create 3 columns#4: To copy the three formulas down to the rest of the rows, select the three cells and then double‐click the small box in the lower right‐hand corner of cell M2In the fourth step, copy the three formulas down to the rest of the rows by selecting the three cells and then double‐clicking the small box in the lower right‐hand corner of cell M2.10
Req 1: Create 3 columns#5: Format the three columns by selecting them and then clicking on Format, Accounting format with 2 decimal places.For Step 5, format the three columns by selecting the columns and then clicking on Format, Accounting format with 2 decimal places.11
Req 1: Create 3 columnsThat’s it, you have added three formatted columns to the Data worksheetThat’s it, you have added three formatted columns to the Data worksheet.12
Requirement 2Create a pivot table that shows sales revenue by region for each year. Correct any errors in the data set. Insert a pivot chart to show sales trends.Requirement 2 states “Create a pivot table that shows sales revenue by region for each year. Correct any errors in the data set. Insert a pivot chart to show sales trends.” 13
Req 2: Sales by region and error correction#1: Click anywhere in the data in the Data worksheetThe first step is to click anywhere in the data in the Data worksheet.14
Req 2: Sales by region and error correction#2: On the ribbon, click Insert and then Pivot TableNext, on the ribbon, click Insert and then Pivot Table.15
Req 2: Sales by region and error correction#3: Right‐click the worksheet name to rename it as “Req 2”Before we go any further, right‐click the worksheet name tab and rename it “Req 2.” That will help to keep track of the different pivot tables.16
Req 2: Sales by region and error correctionBy the way, if this panel ever disappears, you can bring it back by clicking anywhere in the pivot table you have createdBy the way, if the PivotTable Fields panel ever disappears, you can bring it back by clicking anywhere in the pivot table you have created.17
Req 2: Sales by region and error correction#4: Drag “Region” in the PivotTable Fields panel down to the Rows box, “Sales revenue” down to the Values box, and “date_of_sale” down to the Columns boxThe next step is to drag “Region” in the PivotTable Fields panel down to the Rows box, “Sales revenue” down to the Values box, and “date_of_sale” down to the Columns box.18
Req 2: Sales by region and error correction#5: Examine the pivot table now. Look for errors. Here we see that “Central” has been entered as “Centrals” at least once in the dataset.#5: Examine the pivot table now. Look for errors. Here we see that “Central” has been entered as “Centrals” at least once in the dataset. When you work on the large assigned dataset, there may be different error(s), but the same visual inspection technique will work to find any errors.19
Req 2: Sales by region and error correction#6: Now switch back to the Data worksheet and click on Find & Select in the Home ribbon. Select Find and Replace. Type in the error term you found in the pivot table and click Find Next. Now switch back to the Data worksheet and click on Find & Select in the Home ribbon. Select Find and Replace. Type in the error term(s) you found in the pivot table and click Find Next. Replace with the corrected spelling. Do this process for each error you find in the pivot table. Here we had just one, Centrals instead of the correct Central.20
Req 2: Sales by region and error correction#7: Return to the Req 2 worksheet. Click in the data in the pivot table. Right‐click and select Refresh. For Step 7, return to the Req 2 worksheet. Click in the data in the pivot table. Right‐click and select Refresh. This process should update the pivot table so the errors you corrected are no longer in the pivot table.21
Req 2: Sales by region and error correction#8: Select the pivot table data and right‐click to select Value Field SettingsNext, we will format the data in the pivot table. Select the pivot table data and right‐click to select Value Field Settings.22
Req 2: Sales by region and error correction#9: Select Number Format and then format the pivot table cells as Accounting with 2 decimal placesSelect Number Format and then format the pivot table cells as Accounting with 2 decimal places.23
Req 2: Sales by region and error correction#10: Select the pivot table data. On the Insert ribbon, click on PivotChartNow we are going to insert a PivotChart in this same worksheet. Select the pivot table data. On the Insert ribbon, click on PivotChart.24
Req 2: Sales by region and error correction#11: Next, select the Line type of chartNext, select the Line type of chart and then click on OK.25
Req 2: Sales by region and error correctionThe pivot chart now appears in the worksheetThe pivot chart now appears in the worksheet. However, we need to switch the data rows and columns.26
Req 2: Sales by region and error correction#12: Right‐click the chart and click on Select DataTo switch the rows and columns, right‐click the chart and click on Select Data.27
Req 2: Sales by region and error correction#13: Click on the Switch Row/Column button at the top of the boxNext, click on the Switch Row/Column button at the top of the box.28
Req 2: Sales by region and error correction#14: Now click OK to finalize the switch of the rows and columnsNow click OK to finalize the switch of the rows and columns.29
Req 2: Sales by region and error correctionThe pivot table rows and columns now have been switched, as well as the pivot chart dataThe pivot table rows and columns now have been switched, as well as the pivot chart data.30
Requirement 3Create a pivot table that shows sales revenue, costs, and gross profit for each year. What is the impact on retained earnings each year?Requirement 3 reads “Create a pivot table that shows sales revenue, costs, and gross profit for each year. What is the impact on retained earnings each year?”31
Req 3: Sales, costs and gross profit by year#1: Click anywhere in the data in the Data worksheetThe first step is to click anywhere in the data in the Data worksheet.32
Req 3: Sales, costs and gross profit by year#2: Click on the Insert tab and then click on Pivot TableClick on the Insert tab and then click on Pivot Table.33
Req 3: Sales, costs and gross profit by year#3: Accept the defaults and click on OKNext, accept the defaults and click on OK.34
Req 3: Sales, costs and gross profit by year#4: Right‐click the worksheet name to rename it as “Req 3”Before we go any further, right‐click the worksheet name tab and rename it “Req 3.” That will just help to keep track of the various pivot tables.35
Req 3: Sales, costs and gross profit by year#5: Drag “Years” in the PivotTable Fields panel down to the Rows box. Drag“sales revenue”, “total cost”, and “gross profit” down to the Values boxIn the next step, drag “Years” in the PivotTable Fields panel down to the Rows box. Drag “sales revenue”, “total cost”, and “gross profit” down to the Values box.36
Req 3: Sales, costs and gross profit by year#6: Select the pivot table data. Right‐click and select Value Field SettingsNext, select the pivot table data. Right‐click and select Value Field Settings.37
Req 3: Sales, costs and gross profit by year#7: Click on Number FormatNext, click on Number Format.38
Req 3: Sales, costs and gross profit by year#8: Select Accounting format with 2 decimal placesSelect Accounting format with 2 decimal places.39
Req 3: Sales, costs and gross profit by yearThis pivot table that shows sales revenue, total cost, and gross profit by year is finishedThis pivot table that shows sales revenue, total cost, and gross profit by year is now finished. The requirements for the data project ask to calculate the net impact on retained earnings in each of the years from the transactions. Remember that gross profit will increase retained earnings.40
Requirement 4Create a pivot table that shows the most profitable brand in each year, as measured by gross profit.Requirement 4 reads “Create a pivot table that shows the most profitable brand in each year, as measured by gross profit.”41
Req 4: Most profitable by gross profit#1: Click anywhere in the data in the Data worksheetThe first step is to click anywhere in the data in the Data worksheet.42
Req 4: Most profitable by gross profit#2: Click on the Insert tab and then click on Pivot TableClick on the Insert tab and then click on Pivot Table.43
Req 4: Most profitable by gross profit#3: Accept the defaults and click on OKAccept the defaults and click on OK.44
Req 4: Most profitable by gross profit#4: Right‐click the worksheet name to rename it as “Req 4”Before we go any further, right‐click the worksheet name tab and rename it “Req 4.” That will help to keep track of the pivot tables.45
Req 4: Most profitable by gross profit#5: Drag “Years” in the PivotTable Fields panel down to the Columns box. Drag “brand” and “collection” down to the Rows box. Drag“gross profit” down to the Values box.In the next step, drag “Years” in the PivotTable Fields panel down to the Columns box. Drag “brand” and “collection” down to the Rows box. Drag “gross profit” down to the Values box.46
Req 4: Most profitable by gross profit#6: Select the pivot table data, right‐click, click Value Field Settings, Number Format, and format as Accounting with 2 decimal placesNext, select the pivot table data, right‐click, click Value Field Settings, Number Format, and format as Accounting with 2 decimal places.47
Req 4: Most profitable by gross profitThe pivot table is now done –it shows the most profitable brand each year.The pivot table is now done –it shows the most profitable brand each year.48
Requirement 5Create a pivot table to answer the question “Within each brand, what was the most profitable brand in each year, as measured by gross profit?”Requirement 5 reads ““Within each brand, what was the most profitable collection in 2018, as measured by the gross profit percentage? The least most profitable collection for each brand?” Use the field “years” to filter the data to include just the year of 2018. You will need to add a calculated field to the pivot table to calculate the gross profit percentage. Within each brand, sort the collections by gross profit percentage, from the largest to the smallest. Interpret your findings.”49
Req 5: Most profitable brand by gross profit#1: Click anywhere in the data in the Data worksheetThe first step is to click anywhere in the data in the Data worksheet.50
Req 5: Most profitable brand by gross profit#2: Click on Insert and then Pivot TableNext, click on Insert and then Pivot Table.51
Req 5: Most profitable brand by gross profit#3: Accept the defaults for the pivot table and click OKNext, accept the defaults for the pivot table and click OK.52
Req 5: Most profitable brand by gross profit#4: Right‐click the worksheet name to rename it as “Req 5”Before we go any further, right‐click the worksheet name tab and rename it “Req 5.” Numbering the worksheets helps to keep track of the pivot tables.53
Req 5: Most profitable brand by gross profit#5: Drag “Years” in the PivotTable Fields panel down to the Filters box. Drag“brand” and “collection” down to the Rows box. Finally, drag gross profit down to the Values box.Next, drag “Years” in the PivotTable Fields panel down to the Filters box. Drag “brand” and “collection” down to the Rows box. Finally, drag gross profit down to the Values box.54
Req 5: Most profitable brand by gross profit#6: In the Analyze ribbon, click on Fields, Items, & Sets to add a CalculatedFieldNext, in the Analyze ribbon, click on Fields, Items, & Sets to add a Calculated Field.55
Req 5: Most profitable brand by gross profit#7: Insert Calculated field using the name of “grossprofitpct” and the formula of = ‘gross profit’/ ‘sales revenue’For the next step, insert Calculated field using the name of “grossprofitpct” and the formula of = ‘gross profit’/ ‘sales revenue’. Point to the fields and click insert rather than typing them in.56
Req 5: Most profitable brand by gross profit#8: Select the data in the grossprofitpctcolumn and right‐click. Select Value Field Settings, Number Format, and format as a percentage.Next, select the data in the grossprofitpctcolumn and right‐click. Select Value Field Settings, Number Format, and format as a percentage.57
Req 5: Most profitable brand by gross profit#9: In the Years filter box, select 2018 as the year to display only 2018 dataIn the next step, in the Years filter box, select 2018 as the year to display only 2018 data.58
Req 5: Most profitable brand by gross profit#10: Sort the pivot table by collections, from largest to smallestClick on a cell in the pivot table at the collection level. Here we will click on Cell C5. Next, right‐click and select Sort, and then Sort Largest to Smallest.59
Req 5: Most profitable brand by gross profitPivot table is finishedThe pivot table is finished. We can see the most profitable and least profitable brands as measured by gross profit dollars.60
Requirement 6Create a pivot table to answer the question “Which region was the most profitable in 2018, as measured by the gross profit percentage?”Requirement 6 reads “Create a pivot table to answer the question “Which region was the most profitable in 2018, as measured by the gross profit percentage?” Use a filter to include only sales from 2018 in this pivot table. Again, you will need to add a calculated field to the pivot table to calculate the gross profit percentage. Sort the regions by gross profit percentage, from largest to smallest.”61
Req 6: 2018 most profitable by gross profit %#1: Click anywhere in the data in the Data worksheetThe first step is to click anywhere in the data in the Data worksheet.62
Req 6: 2018 most profitable by gross profit %#2: On the Insert ribbon, click on Pivot Table and accept the defaults to insert a new pivot table in the workbookOn the Insert ribbon, click on Pivot Table and accept the defaults to insert a new pivot table in the workbook.63
Req 6: 2018 most profitable by gross profit %#3: Right‐click the worksheet name to rename it as “Req 6”Before we go any further, right‐click the worksheet name tab and rename it “Req 6.” That will help to keep track of the pivot tables.64
Req 6: 2018 most profitable by gross profit %#4: Drag “Years” in the PivotTable Fields panel down to the Filters box. Drag“region” down to the Rows box. Finally, drag “gross profit” and “grossprofitpct” down to the Values boxNext, drag “Years” in the PivotTable Fields panel down to the Filters box. Drag “region” down to the Rows box. Finally, drag “gross profit” and “grossprofitpct” down to the Values box.65
#5: Select the data in the Sum of grossprofitpctcolumn, right‐click, and select Value Field SettingsReq 6: 2018 most profitable by gross profit %Select the data in the Sum of grossprofitpctcolumn, right‐click, and select Value Field Settings.66
#6: Select the Number Format boxReq 6: 2018 most profitable by gross profit %Next, in the Value Field Settings, select the Number Format box.67
#7: Format the data as percentage with 2 decimal places and click OKReq 6: 2018 most profitable by gross profit %Format the data as percentage with 2 decimal places and click OK.68
#8: In the filter box in the upper right corner of the worksheet, select 2018 as the yearReq 6: 2018 most profitable by gross profit %In the filter box in the upper right corner of the worksheet, select 2018 as the year.69
#9: Sort from largest to smallest even though here it is already in that orderReq 6: 2018 most profitable by gross profit %Sort the data from largest to smallest even though here in this small data set, it is already in that order. In the large data set, sorting from largest to smallest will make a difference.70
Req 6: 2018 most profitable by gross profit %That’s it –we can see the most profitable region in 2018 as measured by gross profit percentage.That’s it –we can see the most profitable region in 2018 as measured by gross profit percentage.71
hor Contact InfoPrepared by:Wendy M. Tietz, PhD, CPA, CMA, CSCACopyright © 2018 Wendy M. Tietz, LLCThat concludes this data analytics tutorial covering sales, cost, and gross profit analysis using pivot tables and charts in Excel. Thanks for watching!72
We are a professional custom writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework.
Yes. We have posted over our previous orders to display our experience. Since we have done this question before, we can also do it for you. To make sure we do it perfectly, please fill our Order Form. Filling the order form correctly will assist our team in referencing, specifications and future communication.
1. Click on the “Place order tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
2. Fill in your paper’s requirements in the "PAPER INFORMATION" section and click “PRICE CALCULATION” at the bottom to calculate your order price.
3. Fill in your paper’s academic level, deadline and the required number of pages from the drop-down menus.
4. Click “FINAL STEP” to enter your registration details and get an account with us for record keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
5. From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.
Need this assignment or any other paper?
Click here and claim 25% off
Discount code SAVE25