Font Heading 1 Heading 2 Title Subtitle Subtle Emph Paragraph Styles Dictate Pane Styles Voice SUNY Westchester Community College accounting format, zero decimal places (Windows | Mac). Change the column and row labels to Managerial Accounting better reflect the data. (General Ledger Accounts, Gross Profit, Total.) Make each worksheet a. What is the revenue generated by each sales rep? How much revenue did Franklin Ivy Corporation manufactures a variety of indoor and outdoor furniture. It has several tab a different color. Smithers generate? manufacturing facilities located in six different states or provinces: Ontario and British 1 Pivot table #1 (Check figure: Cell B7 should be $ (429,680)) b. Which sales rep generates the most revenue? Which generates the least? Columbia in Canada, and Kansas, North Carolina, Ohio, and Texas in the United States. Each Create a pivot table to answer the following questions. Type your answers into the questions in C. How much of each sales rep's revenue is generated from facilities in Canada? From 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 Connect. (Not all questions are in Connect but your pivot table should answer all of them.) facilities in the United States? Hint: Use a filter in the pivot table (Windows | Mac). a. What Is the total revenue? How much revenue did Hallie Vang generate in Canada? products from any of its manufacturing facilities. d. How much of each sales rep's revenue is generated by each of the facilities within All transactions in lvy's job cost system for the most recent month have been exported to an b. What is the total direct material used? each country? Hint: Use a filter in the pivot table (Windows | Mac). Excel file that can be downloaded at http://tiny.cc/ivydata. In this transaction file, revenue What is the total direct labor used? How much of Sophie Payne's revenue was generated in Kansas? amounts are shown as positive numbers while all costs shown as negative numbers. The transactions file contains the following fields: d. What is the total manufacturing overhead. applied? Job e. What Is the total gross profit? Customer # 2. Pivot table #2 (Check figure: G/L 84501 - Mig. overhead should be S (74,461) for the Customer name Ontario facility) Sales rep Facility What is the total revenue, total direct material, total direct labor, total manufacturing Country overhead, and total gross profit? General ledger account a. for each country? Amount b. for each state or province? Job costs in lvy's system are tracked using four different general ledger numbers: G/L #3100- Revenue; G/L #4101-Direct material; G/L #4301-Direct labor; and G/L #4501- C. Which manufacturing facility has the most gross profit? Which has the least? Manufacturing overhead. Note that in this case, gross profit is simplified because there are no Inventories! d. Which country's manufacturing facilities generate the most gross profit? . This case is adapted from one by Dr. Wendy Tietz 3. Pivot table #3 (Check figure: Cell F936 should be $ 1,293) General instructions a. What is the revenue generated by each job? How much revenue did job 3515 generate? Before you start: b. What are the totals of direct material, direct labor, and manufacturing overhead At home, before starting this in lab, watch the tutorial video and practice using the tutorial data costs for each job? How much direct materials did job 3535 use? set, which is similar but smaller than the actual data set: C. What is the gross profit generated by each job? Answer the questions in d. Here is the link to the tutorial (link) and to the tutorial data set (link). d. Which Job generated the most gross profit? Which generated the least gross profit? Hint: Sort the pivot table by gross profit (link). In class: Create pivot tables (link) in separate worksheets to calculate answers for each of the following 4. Pivot table #4 (Check figure: Sophia Payne's Ontario revenues should equal $ 151,580) requirements. Rename each of the pivot table worksheets (link) from the default names to "Pivot table 1," "Pivot table 2," etc. Format dollar amounts in all pivot tables with the 75% Focus E +
