ACC2102 - Principles of Managerial Accounting Excel Analytics Project: Ivy Corporation Background Ivy Corporation manufactures a variety of indoor and outdoor furniture. It has several manufacturing facilities located in six different states or provinces: Ontario and British Columbia in Canada; and Kansas, North Carolina, Ohio, and Texas in the United States. Each manufacturing facility makes its own unique products. The company uses just-in-time inventory management, so it has no significant inventory. Ivy has five sales representatives who can sell products from any of its manufacturing facilities. Data All transactions in Ivy's job cost system for the most recent month have been exported to an Excel file that can be downloaded here. (This link is also available on Moodle.) In this transaction file, revenue amounts are shown as positive numbers while all costs are shown as negative numbers. Job costs in lvy's system are tracked using four different general ledger numbers: G/L #3100 - Revenue G/L #4101 - Direct materials G/L #4301 - Direct labor G/L #4501 - Manufacturing overhead Analysis As managerial accountant for Ivy Corporation, you have been tasked with analyzing the transaction detail to report to various members of management. The following individuals have requested information from you. Your task is to create pivot tables in separate worksheets to calculate appropriate responses to each of their requests. Rename each of the pivot table worksheets accordingly. Format dollar amounts in all pivot tables with the Accounting format, zero decimal places. 1. Chief Financial Officer: "I am ready to analyze financial results for this month. I need to know our total revenue. Then, please provide separate subtotals for total direct materials used, total direct labor used, and total manufacturing overhead applied. Finally, what is the total gross profit?" 2. Production Manager: "I am looking for information on performance at our various manufacturing facilities. Could you help me with this? What is the total revenue, total direct materials, total direct labor, total manufacturing overhead, and total gross profit for each country? For each state or province? Which manufacturing facility has the most gross profit? Which has the least? Which country's manufacturing facilities generate the most gross profit for the company?" Adapted from Case MA-4300, Accounting is Analytics: https://accountingisanalytics.com/case-ma-4300-ivy-corporation-excel/3. Vice President of Operations: "I need some data on our profitability on each job this month. Can you tell me the total revenue generated by each job? Then, what are the totals of direct materials, direct labor, and manufacturing overhead for each job? What is the gross profit generated by each job? Which job generated the most gross profit? Which job generated the least gross profit?" 4. Sales Team Director: "It's time to conduct monthly reviews of our sales team members' performance. Could you please provide some data telling me the total revenue generated by each sales rep? Which sales rep generated the most revenue? Who generated the least? How much of each sales rep's revenue is generated from facilities in Canada? From facilities in the United States? How much of each sales rep's revenue is generated by each of the facilities within each country?** ^Hint: Sort the pivot table by gross profit. *Hint: Use a filter in the pivot table. Check Figures 1. CFO Pivot Table: Cell 87 should be $(429,680) 2. Production Mgr Pivot Table: G/L #4501 - Manufacturing overhead should be $(74,461) for the Ontario facility 3. VP of Ops Pivot Table: Cell F936 should be $1,293 4. Sales Team Dir Pivot Table: Sophia Payne's Ontario revenues should equal $151,580 Communication Just as important as generating accurate data is communicating it clearly. Once you have gathered the requested information from the pivot tables, prepare a slide deck (PowerPoint, Google Slides, etc.) summarizing the answers to these questions in a visual manner. Your slide deck should have a title slide separating each of the four sections (each section should be addressed to the respective member of management. Be creative in your presentation: use tables, graphs, infographics, etc. to visually present the information requested. You should incorporate at least three different types of visuals throughout your slides. All of their questions should be addressed. Submission Your final submission should include an Excel file with five tabs: an original data tab and one tab for each of the four pivot tables. Also submit your slide deck as an attachment or as a link. If submitting a link, please provide access to both Professor Olsen (ceolsen@ unwsp.edu) and the course TA, Caleb Merwin (camerwin@students. unwsp.edu). Adopted from Case MA-4300, Accounting is Analytics: https://accountingisanalytics.com/case-ma-4300-ivy-corporation-excel/Grading Criteria This project is worth 60 points toward the Projects category of your grade. Expectations for full credit: Points possible: Accuracy of Data - Pivot table data is accurate, results are correct. Accurate data has also been transferred to the slides. /24 Communication of Findings - Slide deck clearly and accurately presents findings. A variety of visuals are used to communicate the data. Types of visuals used are thoughtful and intentional. /24 Professionalism - Submissions are clean and complete. All instructions have been followed. /12 /60 Resources (All of these links are also available on Moodle.) Calculating job cost using job costing (2:30) Calculating gross profit on a job (0:58) Creating a pivot table (3:00) Renaming worksheets (0:53) Formatting pivot table data - Windows (1:17) Formatting pivot table data - Mac (1:12] Sorting a pivot table (0:54) Filtering a pivot table - Windows (1:47) Filtering a pivot table - Mac (1:51) Tutorial of project start-to-finish (16:25) Mini data set for tutorial video Adopted from Case MA-4300, Accounting is Analytics: https://accountingisanalytics.com/case-ma-4300-ivy-corporation-excel/