AB FG Excel formulas must be used. Please be sure to submit your file as an Excel file. Any submissions in numbers format will receive a 0. 4 8 9 1237560 ROI & Service Cost Allocation Student Name: Section Number: 10 11 Revenues Instructional Instructional Accounting 500,000 Business $1,000,000 12 Operating Expenses 250,000 $ 550,000 13 Income before Service Charges FORMULA 1 FORMULA 2 14 15 Investment 300,000 $ 500,000 16 17 ROI before service charges FORMULA 3 FORMULA 4 18 19 Number of professors 5 10 20 Number of students in program 200 500 21 22 23 Payroll Advising (Driver is # of (Driver is # of 24 professors) students) 25 Service department expenses 100,000 200,000 26 27 Service department charge rate FORMULA 5 FORMULA 6 28 Instructional 29 30 Revenues 31 Operating expenses 32 Income before service charges 33 34 35 Service charges: Payroll Advising 36 Income from operations Accounting 500,000 Instructional Business $1,000,000 $ 250,000 $ 550,000 $ 250,000 $450,000 FORMULA 7 FORMULA 8 FORMULA 9 FORMULA 10 FORMULA 11 FORMULA 12 LM AB 10 Instructional Accounting Instructional 1 Revenues 500,000 2 Operating Expenses 250,000 $ 550,000 13 Income before Service Charges FORMULA 1 Business $1,000,000 FORMULA 2 4 15 Investment 300,000 $ 500,000 16 17 ROI before service charges FORMULA 3 FORMULA 4 18 19 Number of professors 5 10 20 Number of students in program 200 500 21 22 23 Payroll Advising (Driver is # of (Driver is # of 24 25 Service department expenses professors) 100,000 students) 200,000 26 27 Service department charge rate FORMULA 5 FORMULA 6 28 Instructional 29 30 Revenues 31 32 33 34 35 Advising Operating expenses Income before service charges Service charges: Payroll Instructional Accounting Business $ 500,000 $ 1,000,000 $ 250,000 $ 550,000 250,000 $ 450,000 $ FORMULA 7 FORMULA 8 FORMULA 10 FORMULA 9 36 Income from operations FORMULA 11 FORMULA 12 37 38 ROI after service charges FORMULA 13 FORMULA 14 39 40 +1 FG H J K Module 6 Excel Spreadsheet Instructions Below you will see three sets of inputs. After inputting all of your formulas, you should be able to use any of these sets of data and have the answers automatically update. For this assignment, please choose one of the data sets above and input all of the necessary formulas to find the answers. Once you are done, choose a different data set, enter it into your spreadsheet, and check the updated answers to ensure that everything is flowing through the formulas appropriately. A check answer for each one has been provided. Data set #1 Inputs: Instructional Accounting 500,000 Instructional Business $1,000,000 Revenues Operating Expenses 250,000 $ 550,000 Income before Service Charges FORMULA 1 FORMULA 2 Investment 300,000 $ 500,000 ROI before service charges FORMULA 3 FORMULA 4 Number of professors 5 10 Number of students in program 200 500 Payroll Advising Service department expenses (Driver is # of (Driver is # of professors) students) 100,000 $ 200,000 Check figure (ROI after service charges - Instructional Accounting): 53%