Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

In this week's Excel assignment you are the CFO of International Machines & Software Inc. and reviewing your 2021 year-end results. Use a pivot table

image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
In this week's Excel assignment you are the CFO of International Machines & Software Inc. and reviewing your 2021 year-end results. Use a pivot table to summarize the cost center data and complete the net operating income statement. Afterward, calculate the residual income and return on investment for each division so you can make recommendations to the CEO and Board of Directors. The COO has requested to know which software engineer incurred the most costs during the year too. Use a pivot table to provide her with that information. As always, complete the assignment using proper formatting in all places per the Excel Rubric. Please reach out if you have questions or need clarification on the assignment. Thank you! D G 5 6 International Machines & Software inc. Investment Center Mondbility Report Ending 12/21/2021 7 B 9 10 Software $ 1,256,7095 Hardware 909,099 $ Services 889,098 Total 3,134,906 Net Sales Revenue Cost of Sales Gross Profit $ $ 1,256,709 989.099 849,098 3,184,906 Operating bansa $ Operating income $ 1,256,709 989,099 189,098 $ 22 13 14 25 16 17 18 19 20 21 22 3,134,906 Aer Total Ass $ 1,340,990 1.118,902 $ 997,802 3,457,694 Minimum Operating Income Required $ 268,1985 220.7805 199,5605 591,539 Residual income 14 05 Return on investment In this ciment, create a pivot table with the cost center data provided. Use the pivot table to accurately categorise the cost of sales and operating expenses by division (..hardware sowe. Once you have the pivot table correct, copy and paste the values in Call 19 and complete the table to arrive at net operating income Complete the required calculations once operating income la properly calculated. Nest, rearrange pivot table by engineer to show costs by engineer. This will provide the answer to the third question below. Submit and priment with pivottabletab included 10 11 12 3 4 15 Which Divind the lowest RO Which in the Highest RI? Which Software Engineer Had the Most Costs 2 7 2 Profit Center Data Cost Center Data + B D E F G 5 6 International Machines & Software Inc.'s Cost Center Data for the Period Ending 12/31/2021 7 8 Account Account Description Lead Engineer Transaction Date Division Region 9 6000-0001 Operating Expenses Stacey Sware 1/1/21 Software US 10 5000-0001 Cost of Sales Hazel Harden 1/8/21 Hardware US 11 6000-0002 Operating Expenses Hazel Harden 1/15/21 Hardware US 12 6000-0001 Operating Expenses Hazel Harden 1/24/21 Hardware US 13 6000-0001 Operating Expenses Hazel Harden 1/30/21 Hardware International 14 5000-0001 Icost of Sales Hazel Harden 1/31/21 Hardware 15 6000-0002 Operating Expenses Sandra Server 2/3/21 Services 16 5000-0002 Cost of Sales Stacey Sware 2/8/21 Software 17 6000-0001 Operating Expenses Steve Soft 2/11/21 Software International 18 6000-0001 Operating Expenses Sandra Server 2/25/21 Services 19 6000-0002 Operating Expenses Hazel Harden 2/25/21 Hardware 20 6000-0001 Operating Expenses Hazel Harden 2/28/21 Hardware 21 6000-0001 Operating Expenses Sandra Servee 3/3/21 Services 22 16000-0001 Operating Expenses Hazel Harden 3/4/21 Hardware International 23 5000-0002 Cost of Sales Sandra Server 3/4/21 Services International 24 6000-0001 Operating Expenses Hazel Harden 3/13/21 Hardware International 25 6000-0001 Operating Expenses Hazel Harden 3/22221 Hardware 26 6000-0001 Operating Expenses Hazel Harden 3/23/21 Hardware International 27 6000-0002 Operating Expenses Sandra Servee 3/27/21 Services 28 S000-0002 Cost of Sales Sam Softstone 3/28/21 Software 29 5000-0001 Cost of Sales Sam Softstone 3/30/21 Software 30 5000-0002 Cost of Sales Hazel Harden 3/31/21 Hardware 31 6000-0002 Operating Expenses Stacey Sware 4/4/21 Software 32 6000-0001 Operating Expenses Hazel Harden 4/22/21 Hardware International 33 6000-0002 Operating Expenses Hazel Harden 4/23/21 Hardware international 34 5000-0001 Cost of Sales Stacey Sware 4124221 Software 35 5000-0001 Cost of Sales Sandra Servee 4/27/21 Services International 36 6000-0001 Operating Expenses Steve Soft 5/21 Software International 37 16000-0001 Operating Expenses Steve Sofft 5/4/21 Software International 38 5000-0002 Cost of Sales Sandra Servee 5/4/21 Services Profit Center Data Cost Center Data + ............. Amount 32,030.56 24,638.05 15,821.69 40,162.46 5,731.53 49,111.86 16,323.69 44,795.28 36,784.35 36,646.14 39,163.66 24,271.59 31,029.71 31,813,50 23,367.99 40,605.96 47,786,12 3,784.12 12,138.82 45,849.98 19,940.09 12,754.12 20,591.59 12.116.43 12,970.98 40,597,68 36,935.10 7,847.46 45,656,46 3,485.99 A B D G H | 39 40 41 42 43 44 45 46 47 48 49 SO 51 52 53 54 55 56 57 58 59 60 61 62 63 54 65 66 67 68 69 70 71 22 6000-0002 Operating Expenses 5000-0002 Cost of Sales 5000-0002 Cost of Sales 6000-0002 Operating Expenses 6000-0002 Operating Expenses 6000-0001 Operating Expenses 6000-0001 Operating Expenses 5000-0001 Cost of Sales 6000-0002 Operating Expenses 6000-0002 Operating Expenses 6000-0002 Operating Expenses S000-0001 Cost of Sales 5000-0001 Cost of Sales 6000-0001 Operating Expenses 6000-0001 Operating Expenses 6000-0001 Operat Expenses 5000-0001 Cost of Sales 5000-0001 Cost of Sales 6000-0001 Operating Expenses 6000-0001 Operating Expenses 5000-0001 Cost of Sales 5000-0001 Cost of Sales 6000-0001 Operating Expenses 6000-0001 Operating Expenses 6000-0001 Operating Expenses 5000-0001 Cost of Sales 5000-0002 Cost of Sales 6000-0001 Operating Expenses 6000-0001 Operating Expenses 5000-0002 Cost of Sales 6000-0001 Operating Expenses 5000-0002 Cost of Sales 5000-0001 Cost of Sales 000-00 CASAM Profit Center Data Steve Soft Sam Softstone Sam Softstone Hazel Harden Hazel Harden Hazel Harden Sandra Serve Sam Softstone Sandra Serve Stacey Sware Hazel Harden Steve Soft Sandra Servee Sandra Servee Hazel Harden Sandra Servee Hazel Harden Hazel Harden Hazel Harden Stacey Sware Stacey Sware Stacey Sware Sam Softstone Stacey Sware Sandra Servee Hazel Harden Sam Softstone Stacey Sware Stacey Sware Sandra Servee Hazel Harden Sam Softstone Hazel Harden Har Harian Cost Center Data 5/23/21 Software 5/25/21 Software 6/2/21 Software 6/13/21 Hardware 6/1721 Hardware 6/17/21 Hardware 6/21/21 Services 7/5/21 Software 7/14/21 Services 7/18/21 Software 7/24/21 Hardware 7/26/21 Software 7/30/21 Services 7/31/21 Services 8/1/21 Hardware 8/3/21 Services 8/4/21 Hardware 8/5/21 Hardware 8/9/21 Hardware 8/12/21 Software 8/13/21 Software 8/19/21 Software 8/24/21 Software 8/26/21 Software 8/30/21 Services 9/10/21 Hardware 9/20/21 Software 9/22/21 Software 9/25/21 software 9/27/21 Services 10/8/21 Hardware 10/10/21 Software 10/11/21 Hardware 10/1401 Hardware US US US International International International International US US US International International US US International US International US International International US International International US US US US US International US International US International International 25,137.30 41,774.25 8,245.85 13,962.12 29,852.35 35,495.36 40,231.37 25,650.36 15,621.78 40,180.55 1,059.10 2,642.62 33,177.25 44,354.73 13.480.45 23,301.90 46,837.22 50,252.44 36,613,15 5,283.78 30,642.71 1,692.48 10,196.94 2,893,40 16,096.89 5,034.42 8,748.31 46,115,83 13,787.28 24,469.89 25,567.91 4,381.20 12,745.95 26,272 49 + C D G International International A B 715000-0001 72 5000-0002 73 5000-0001 74 6000-0001 75 6000-0002 76 5000-0002 77 6000-0001 78 6000-0001 79 6000-0001 80 6000-0001 81 6000-0002 82 6000-0001 83 S000-0001 84 5000-0001 85 6000-0001 86 6000-0001 82 S000-0002 88 5000-0001 89 6000-0001 90 5000-0002 91 5000-0001 92 5000-0001 93 6000-0001 94 5000-0002 95 6000-0002 96 92 98 99 100 101 102 103 Cost of Sales Cost of Sales Cost of Sales Operating Expenses Operating Expenses Cost of Sales Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Cost of Sales Cost of Sales Operating Expenses Operating Expenses Cost of Sales Cost of Sales Operating Expenses Cost of Sales Cost of Sales Cost of Sales Operating Expenses Cost of Sales Operating Expenses Hare Harden Hazel Harden Steve Sofft Sandra Servee Sandra Servee Hazel Harden Sandra Serve Stacey Sware Hazel Harden Steve Soft Hazel Harder Stacey Sware Sandra Serve Sandra Service Harl Harden Sam Softstone Harl Harden Hazel Harden Sandra Server Harel Harder Sam Softstone Sandra Servee Steve Soft Sandra Servee Hazel Harden E F 10/11/21 Hardware 10/14/21 Hardware 10/17/21 Software 10/20/21 Services 10/22/21 Services 10/27/21 Hardware 10/28/21 Services 10/28/21 Software 10/31/21 Hardware 11/3/21 Software 11/3/21 Hardware 110/21 Software 11/21 Services 11/15/21 Services 11/15/21 Hardware 11/16/21 Software 11/18/21 Hardware 11/22/21 Hardware 12/4/21 Services 12/9/21 Hardware 12/14/21 Software 12/20/21 Services 12/23/21 Software 12/27/21 Services 12/29/21 Hardware US US International US US US US US US International US US International International International International US International US International US H 12,745.95 36,772.49 2,881.37 10,445.20 21,699.00 41,038.15 45,696.76 44,951.20 29,607.28 23,635.34 28,936.06 21,510.04 6,931.58 27,920.9 21,839.57 32,403.04 7,538.43 26,441.82 20,58542 17,724.10 42,141.07 41,274.83 36,823.89 22,673.65 30,747.84 In this week's Excel assignment you are the CFO of International Machines & Software Inc. and reviewing your 2021 year-end results. Use a pivot table to summarize the cost center data and complete the net operating income statement. Afterward, calculate the residual income and return on investment for each division so you can make recommendations to the CEO and Board of Directors. The COO has requested to know which software engineer incurred the most costs during the year too. Use a pivot table to provide her with that information. As always, complete the assignment using proper formatting in all places per the Excel Rubric. Please reach out if you have questions or need clarification on the assignment. Thank you! D G 5 6 International Machines & Software inc. Investment Center Mondbility Report Ending 12/21/2021 7 B 9 10 Software $ 1,256,7095 Hardware 909,099 $ Services 889,098 Total 3,134,906 Net Sales Revenue Cost of Sales Gross Profit $ $ 1,256,709 989.099 849,098 3,184,906 Operating bansa $ Operating income $ 1,256,709 989,099 189,098 $ 22 13 14 25 16 17 18 19 20 21 22 3,134,906 Aer Total Ass $ 1,340,990 1.118,902 $ 997,802 3,457,694 Minimum Operating Income Required $ 268,1985 220.7805 199,5605 591,539 Residual income 14 05 Return on investment In this ciment, create a pivot table with the cost center data provided. Use the pivot table to accurately categorise the cost of sales and operating expenses by division (..hardware sowe. Once you have the pivot table correct, copy and paste the values in Call 19 and complete the table to arrive at net operating income Complete the required calculations once operating income la properly calculated. Nest, rearrange pivot table by engineer to show costs by engineer. This will provide the answer to the third question below. Submit and priment with pivottabletab included 10 11 12 3 4 15 Which Divind the lowest RO Which in the Highest RI? Which Software Engineer Had the Most Costs 2 7 2 Profit Center Data Cost Center Data + B D E F G 5 6 International Machines & Software Inc.'s Cost Center Data for the Period Ending 12/31/2021 7 8 Account Account Description Lead Engineer Transaction Date Division Region 9 6000-0001 Operating Expenses Stacey Sware 1/1/21 Software US 10 5000-0001 Cost of Sales Hazel Harden 1/8/21 Hardware US 11 6000-0002 Operating Expenses Hazel Harden 1/15/21 Hardware US 12 6000-0001 Operating Expenses Hazel Harden 1/24/21 Hardware US 13 6000-0001 Operating Expenses Hazel Harden 1/30/21 Hardware International 14 5000-0001 Icost of Sales Hazel Harden 1/31/21 Hardware 15 6000-0002 Operating Expenses Sandra Server 2/3/21 Services 16 5000-0002 Cost of Sales Stacey Sware 2/8/21 Software 17 6000-0001 Operating Expenses Steve Soft 2/11/21 Software International 18 6000-0001 Operating Expenses Sandra Server 2/25/21 Services 19 6000-0002 Operating Expenses Hazel Harden 2/25/21 Hardware 20 6000-0001 Operating Expenses Hazel Harden 2/28/21 Hardware 21 6000-0001 Operating Expenses Sandra Servee 3/3/21 Services 22 16000-0001 Operating Expenses Hazel Harden 3/4/21 Hardware International 23 5000-0002 Cost of Sales Sandra Server 3/4/21 Services International 24 6000-0001 Operating Expenses Hazel Harden 3/13/21 Hardware International 25 6000-0001 Operating Expenses Hazel Harden 3/22221 Hardware 26 6000-0001 Operating Expenses Hazel Harden 3/23/21 Hardware International 27 6000-0002 Operating Expenses Sandra Servee 3/27/21 Services 28 S000-0002 Cost of Sales Sam Softstone 3/28/21 Software 29 5000-0001 Cost of Sales Sam Softstone 3/30/21 Software 30 5000-0002 Cost of Sales Hazel Harden 3/31/21 Hardware 31 6000-0002 Operating Expenses Stacey Sware 4/4/21 Software 32 6000-0001 Operating Expenses Hazel Harden 4/22/21 Hardware International 33 6000-0002 Operating Expenses Hazel Harden 4/23/21 Hardware international 34 5000-0001 Cost of Sales Stacey Sware 4124221 Software 35 5000-0001 Cost of Sales Sandra Servee 4/27/21 Services International 36 6000-0001 Operating Expenses Steve Soft 5/21 Software International 37 16000-0001 Operating Expenses Steve Sofft 5/4/21 Software International 38 5000-0002 Cost of Sales Sandra Servee 5/4/21 Services Profit Center Data Cost Center Data + ............. Amount 32,030.56 24,638.05 15,821.69 40,162.46 5,731.53 49,111.86 16,323.69 44,795.28 36,784.35 36,646.14 39,163.66 24,271.59 31,029.71 31,813,50 23,367.99 40,605.96 47,786,12 3,784.12 12,138.82 45,849.98 19,940.09 12,754.12 20,591.59 12.116.43 12,970.98 40,597,68 36,935.10 7,847.46 45,656,46 3,485.99 A B D G H | 39 40 41 42 43 44 45 46 47 48 49 SO 51 52 53 54 55 56 57 58 59 60 61 62 63 54 65 66 67 68 69 70 71 22 6000-0002 Operating Expenses 5000-0002 Cost of Sales 5000-0002 Cost of Sales 6000-0002 Operating Expenses 6000-0002 Operating Expenses 6000-0001 Operating Expenses 6000-0001 Operating Expenses 5000-0001 Cost of Sales 6000-0002 Operating Expenses 6000-0002 Operating Expenses 6000-0002 Operating Expenses S000-0001 Cost of Sales 5000-0001 Cost of Sales 6000-0001 Operating Expenses 6000-0001 Operating Expenses 6000-0001 Operat Expenses 5000-0001 Cost of Sales 5000-0001 Cost of Sales 6000-0001 Operating Expenses 6000-0001 Operating Expenses 5000-0001 Cost of Sales 5000-0001 Cost of Sales 6000-0001 Operating Expenses 6000-0001 Operating Expenses 6000-0001 Operating Expenses 5000-0001 Cost of Sales 5000-0002 Cost of Sales 6000-0001 Operating Expenses 6000-0001 Operating Expenses 5000-0002 Cost of Sales 6000-0001 Operating Expenses 5000-0002 Cost of Sales 5000-0001 Cost of Sales 000-00 CASAM Profit Center Data Steve Soft Sam Softstone Sam Softstone Hazel Harden Hazel Harden Hazel Harden Sandra Serve Sam Softstone Sandra Serve Stacey Sware Hazel Harden Steve Soft Sandra Servee Sandra Servee Hazel Harden Sandra Servee Hazel Harden Hazel Harden Hazel Harden Stacey Sware Stacey Sware Stacey Sware Sam Softstone Stacey Sware Sandra Servee Hazel Harden Sam Softstone Stacey Sware Stacey Sware Sandra Servee Hazel Harden Sam Softstone Hazel Harden Har Harian Cost Center Data 5/23/21 Software 5/25/21 Software 6/2/21 Software 6/13/21 Hardware 6/1721 Hardware 6/17/21 Hardware 6/21/21 Services 7/5/21 Software 7/14/21 Services 7/18/21 Software 7/24/21 Hardware 7/26/21 Software 7/30/21 Services 7/31/21 Services 8/1/21 Hardware 8/3/21 Services 8/4/21 Hardware 8/5/21 Hardware 8/9/21 Hardware 8/12/21 Software 8/13/21 Software 8/19/21 Software 8/24/21 Software 8/26/21 Software 8/30/21 Services 9/10/21 Hardware 9/20/21 Software 9/22/21 Software 9/25/21 software 9/27/21 Services 10/8/21 Hardware 10/10/21 Software 10/11/21 Hardware 10/1401 Hardware US US US International International International International US US US International International US US International US International US International International US International International US US US US US International US International US International International 25,137.30 41,774.25 8,245.85 13,962.12 29,852.35 35,495.36 40,231.37 25,650.36 15,621.78 40,180.55 1,059.10 2,642.62 33,177.25 44,354.73 13.480.45 23,301.90 46,837.22 50,252.44 36,613,15 5,283.78 30,642.71 1,692.48 10,196.94 2,893,40 16,096.89 5,034.42 8,748.31 46,115,83 13,787.28 24,469.89 25,567.91 4,381.20 12,745.95 26,272 49 + C D G International International A B 715000-0001 72 5000-0002 73 5000-0001 74 6000-0001 75 6000-0002 76 5000-0002 77 6000-0001 78 6000-0001 79 6000-0001 80 6000-0001 81 6000-0002 82 6000-0001 83 S000-0001 84 5000-0001 85 6000-0001 86 6000-0001 82 S000-0002 88 5000-0001 89 6000-0001 90 5000-0002 91 5000-0001 92 5000-0001 93 6000-0001 94 5000-0002 95 6000-0002 96 92 98 99 100 101 102 103 Cost of Sales Cost of Sales Cost of Sales Operating Expenses Operating Expenses Cost of Sales Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Operating Expenses Cost of Sales Cost of Sales Operating Expenses Operating Expenses Cost of Sales Cost of Sales Operating Expenses Cost of Sales Cost of Sales Cost of Sales Operating Expenses Cost of Sales Operating Expenses Hare Harden Hazel Harden Steve Sofft Sandra Servee Sandra Servee Hazel Harden Sandra Serve Stacey Sware Hazel Harden Steve Soft Hazel Harder Stacey Sware Sandra Serve Sandra Service Harl Harden Sam Softstone Harl Harden Hazel Harden Sandra Server Harel Harder Sam Softstone Sandra Servee Steve Soft Sandra Servee Hazel Harden E F 10/11/21 Hardware 10/14/21 Hardware 10/17/21 Software 10/20/21 Services 10/22/21 Services 10/27/21 Hardware 10/28/21 Services 10/28/21 Software 10/31/21 Hardware 11/3/21 Software 11/3/21 Hardware 110/21 Software 11/21 Services 11/15/21 Services 11/15/21 Hardware 11/16/21 Software 11/18/21 Hardware 11/22/21 Hardware 12/4/21 Services 12/9/21 Hardware 12/14/21 Software 12/20/21 Services 12/23/21 Software 12/27/21 Services 12/29/21 Hardware US US International US US US US US US International US US International International International International US International US International US H 12,745.95 36,772.49 2,881.37 10,445.20 21,699.00 41,038.15 45,696.76 44,951.20 29,607.28 23,635.34 28,936.06 21,510.04 6,931.58 27,920.9 21,839.57 32,403.04 7,538.43 26,441.82 20,58542 17,724.10 42,141.07 41,274.83 36,823.89 22,673.65 30,747.84

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Pioneers Of Critical Accounting A Celebration Of The Life Of Tony Lowe

Authors: Jim Haslam, Prem Sikka

1st Edition

113754211X, 9781137542113

More Books

Students also viewed these Accounting questions

Question

What is the purpose of a responsibility accounting system?

Answered: 1 week ago