In this exercise, you will compile summary data based on an inventory listing for Educational Endeavors....
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
In this exercise, you will compile summary data based on an inventory listing for Educational Endeavors. Then you will determine the inventory balance fora product category using three methods. Last, you will create a PivotTable and a PivotChart. 1. Open EA4-A1-Inventory from your Chapter 04 folder and save it as: EA4-A1-Inventory-[YourName] 2. Use a function to display the total number of product categories in cell B2. 3. Populate the cells as described for the Construction Paper product category: In this cell: Use a function to display the: Cell J5 Number of years for which an inventory balance is displayed Cell K5 Average inventory balance for the five years shown Cell L5 Maximum inventory balance for the five years shown Cell M5 Minimum inventory balance for the five years shown Hint: Make sure the formula in cell J5 will update properly if any currently blank cells are later filled in. 4. Copy the formulas in the range J5:M5 and paste them such that the calculations will be performed for all product categories. 5. Enter formulas in the range E63:163 to add the inventory balances for each year. Use Inventory Costing Methods 6. Switch to the Construction Paper tab. 7. Use the CONCATENATE function to enter LIFO Inventory Balance in cell A17. 8. Enter a formula in cell B17 to calculate the ending inventory balance for construction paper under the LIFO method. 9. In cell A18, use the CONCATENATE function to enter FIFO Inventory Balance. 10. Enter a formula in cell B18 to calculate the ending inventory balance for construction paper under the FIFO method. 11. In cell A19, use the CONCATENATE function to enter WA Inventory Balance. 12. Enter a formula in cell B19 to calculate the ending inventory balance for construction paper under the weighted average method. 13. Enter a formula in the appropriate location on the Inventory Listing tab to display the fifth-year LIFO inventory balance for construction paper. 14. Apply the 3 Symbols (Uncircled) icon set and Red - White - Green color scale to the range K5:K62. Insert a PivotTable and a PivotChart 15. Create a PivotTable on the Inventory Listing tab based on the inventory balances displayed for all five years. 16. Rename the new worksheet tab containing the PivotTable. 17. Display the Year 5 Inventory Balances within the PivotTable with: • Country of Manufacture in the Rows area • Product Category in the Columns area · A filter that excludes the Active product department 18. Add a slicer that limits the data to product categories that earned at least a 3 of 5 online rating. 19. Create a 3-D Clustered Column PivotChart and display the Online Rating category in the legend. 20. Position the PivotChart and slicer in a logical manner. 21. Save and close the file. In this exercise, you will compile summary data based on an inventory listing for Educational Endeavors. Then you will determine the inventory balance fora product category using three methods. Last, you will create a PivotTable and a PivotChart. 1. Open EA4-A1-Inventory from your Chapter 04 folder and save it as: EA4-A1-Inventory-[YourName] 2. Use a function to display the total number of product categories in cell B2. 3. Populate the cells as described for the Construction Paper product category: In this cell: Use a function to display the: Cell J5 Number of years for which an inventory balance is displayed Cell K5 Average inventory balance for the five years shown Cell L5 Maximum inventory balance for the five years shown Cell M5 Minimum inventory balance for the five years shown Hint: Make sure the formula in cell J5 will update properly if any currently blank cells are later filled in. 4. Copy the formulas in the range J5:M5 and paste them such that the calculations will be performed for all product categories. 5. Enter formulas in the range E63:163 to add the inventory balances for each year. Use Inventory Costing Methods 6. Switch to the Construction Paper tab. 7. Use the CONCATENATE function to enter LIFO Inventory Balance in cell A17. 8. Enter a formula in cell B17 to calculate the ending inventory balance for construction paper under the LIFO method. 9. In cell A18, use the CONCATENATE function to enter FIFO Inventory Balance. 10. Enter a formula in cell B18 to calculate the ending inventory balance for construction paper under the FIFO method. 11. In cell A19, use the CONCATENATE function to enter WA Inventory Balance. 12. Enter a formula in cell B19 to calculate the ending inventory balance for construction paper under the weighted average method. 13. Enter a formula in the appropriate location on the Inventory Listing tab to display the fifth-year LIFO inventory balance for construction paper. 14. Apply the 3 Symbols (Uncircled) icon set and Red - White - Green color scale to the range K5:K62. Insert a PivotTable and a PivotChart 15. Create a PivotTable on the Inventory Listing tab based on the inventory balances displayed for all five years. 16. Rename the new worksheet tab containing the PivotTable. 17. Display the Year 5 Inventory Balances within the PivotTable with: • Country of Manufacture in the Rows area • Product Category in the Columns area · A filter that excludes the Active product department 18. Add a slicer that limits the data to product categories that earned at least a 3 of 5 online rating. 19. Create a 3-D Clustered Column PivotChart and display the Online Rating category in the legend. 20. Position the PivotChart and slicer in a logical manner. 21. Save and close the file.
Expert Answer:
Answer rating: 100% (QA)
Questions 1 5 Save Workbook as EA4A1InventoryYourNa... View the full answer
Related Book For
Business Analytics Methods Models and Decisions
ISBN: 978-0321997821
2nd edition
Authors: James R. Evans
Posted Date:
Students also viewed these finance questions
-
Create a PivotTable for the data in the Excel file Weddings to analyze the wedding cost by type of payor and value rating. What conclusions do you reach?
-
Create and edit tables for Coast Jewlers. a. Create a new table for Coast Jewelers that includes fields labeled Buyer ID, Buyer Name, and Buyer Phone. The first field (Buyer ID) is an AutoNumber data...
-
Why should one-time write-offs of fixed capital be used in absorption cost systems?
-
Explain why X-rays can be used to measure atomic distances in crystals but visible light cannot be used for this purpose?
-
A 23-kg suitcase is pulled with constant speed by a handle at an angle of 25 above the horizontal. If the normal force exerted on the suitcase is 180 N, what is the force F applied to the handle?
-
In Exercises 1 to 4, it may be helpful to draw a figure such as Figure 5.5. Figure 5.5. Using the normal curve table, determine the area of the standard normal distribution that is less than the...
-
1. Cash inflows from operating activities come from a. Payment for raw materials. b. Collection of sales revenues. c. Gains on the sale of operating equipment. d. Issuing capital stock. e. Issuing...
-
Discuss the concept of sense making and its significance in facilitating sense giving during periods of organizational ambiguity and transformation?
-
Design a Verilog code for a 3x3 array multiplier. Use structural Verilog description with full adders, half adders, and other gates. Submit your code, a testbench, and test results with a waveform....
-
[The following information applies to the questions displayed below] Now that operations for outdoor clinics and TEAM events are running smoothly, Suzie thinks of another area for business expansion....
-
what is important of properly managing your finances, especially when you are already earning your own income?
-
Consider a recombinant binomial tree. There are (j/n) paths to reach node ???? ???? in n total steps and j up-steps. There are no interest rates. An asset is worth zero today and goes up or down by 1...
-
Imitate it as a circular linked list of Single Double direction list and display all the players name in the list. Also Find who is the person in the list? Enter the name and height of the Players.
-
Bob Katz is interested in the following stock: current dividend is $2.50 . projected three year growth rate of 10% . growth rate after year 3 is expected to fall and remain constant at 6% Bob's...
-
Suppose that Alphabet's stock closed at $1,300 today. Six months from now Alphabet's stock price will be either $1,400 or $1,200. Using the binomial model, estimate the value of a European call...
-
Non-event risks Question 12 options: Have minimal impact Have two main types: variability risk and ambiguity risk Are typically risks that do not trigger events that may in turn cause future risks...
-
Archangel Corporation prepared the following variance report. Instructions Fill in the appropriate amounts or letters for the question marks in the report. ARCHANGEL CORPORATION Variance...
-
For the Valencia Products constraints and identify the feasible region. Then identify each of the corner points and show how increasing the objective function value identifies the optimal solution....
-
The Excel file Salary Data provides information on current salary, beginning salary, previous experience (in months) when hired, and total years of education for a sample of 100 employees in a firm....
-
A marketing study found that the mean spending in 15 categories of consumer items for 297 respondents in the 18 34 age group was $ 91.86 with a standard deviation of $ 50.90. For 536 respondents in...
-
Consider the financial system of a country like Japan for example and discuss which ingredients we may have to include in a model that can address the reason for the observed recurrent financial...
-
Discuss how one decides whether a model is too simple or unnecessarily complicated.
-
Imagine that the EU flagship Human Brain Project has succeeded in producing a highperformance computer that can simulate the entire network of 8.61010 neurones and their 1015 connections. (a) Will it...
Study smarter with the SolutionInn App