All the information needed to work this problem is on here, I tried copying the following into the boxes and the self-check was saying it was wrong
| Direct expenses | | | 25,000,000 | | 14,000,000 | | 7,000,000 |
Excel File Edit View Insert Format >) 79% O Mon 9:38 PM Q AutoSave OFF Esu = Tools Data Window Help Copy of Budget Tools 2e Module 05 Cost Allocation 5.6 (Self Checking) (2015-d) Data Review View Tell me Q Home Insert Draw Page Layout Formulas Share Comments ab Insert v AY 5 Ix Delete le Paste BI U $ v % ~ % - = = = Ideas Conditional Format Formatting as Table Styles Format v Sort & Filter Find & Select A1 A B C D E F G . K L M N O Q R S T To show the cell checking, put a check here: Percent Correct (Excludes Manual Grades) Grade in Points 14% 0.564 20% $5,000,000 Worksheet Plan 1 1 O 0 2 A college's School of Liberal Arts has 3 departments. Estimated information for next year is presented in the table below: 3 4 5 Science History English 6 Number of Students per Year 1,400 800 400 7 Number of Classes per Semester 70 40 30 8 Number of Professors 20 24 10 9 Revenues $30,000,000 $17,000,000 $9,000,000 10 Direct expenses 25,000,000 14,000,000 7,000,000 11 12 Each department creates an Income Statement. Departments are rewarded for being profitable - the college allows each 13 department to "keep" 20% of the profit it generates to use as it desires. 14 15 Indirect Costs (for administrative salaries and office expesens, costs of the registrar's office, etc.) are projected to be: 16 17 18 Plan 1: Allocate Equal Amounts Plan 2: Allocate Indirect Costs 19 of Indirect Costs to Each Department Using # of Students as Cost Driver 20 21 Science History English Science History English 22 Revenues $30,000,000 $17,000,000 $9,000,000 Revenues $30,000,000 $17,000,000 $9,000,000 23 Direct Exp Direct Exp 24 Indirect Exp Indirect Exp 25 Net Inc. Net Inc. 26 20% Net 20% Net 27 28 29 Plan 3: Allocate Indirect Costs using Plan 4: Allocate Indirect Costs 30 # of Classes/semester as Cost Driver Using # of Professors as Cost Driver 31 Science History English Science History English 32 Revenues $30,000,000 $17,000,000 $9,000,000 Revenues $30,000,000 $17,000,000 $9,000,000 33 Direct Exp Direct Exp 34 Indirect Exp Indirect Exp 35 Net Inc. Net Inc. 36 20% Net 20% Net 37 38 29 A Questions Exercise 5.6 A Grade Worksheet + Plan 2 Basis Shares Divisor Science Allocation History English Total Plan 3 Basis Plan 4 Basis Shares Shares Science History English Total Science History English Total Ready E + 100% >) 79% O Mon 9:38 PM Q OFF Q Share Comments Excel File Edit View Insert Format Tools Data Window Window Help AutoSave Esu = Copy of Budget Tools 2e Module 05 Cost Allocation 5.6 (Self Checking) (2015-d) Home Insert Draw Page Layout Formulas Data Review View Tell me ab Paste BI U = = = $ v % ~ % , o Conditional Format Formatting as Table Insert v AY 5 Ix Delete le Ideas Styles Format v Sort & Filter Find & Select A1 X A B C D E F . i K L M N O Q R S T To show the cell checking, put a check here: Percent Correct (Excludes Manual Grades) Grade in Points 14% 0.564 Grade Text Answer to Question 1 Here Use: A, B, C, DF Professor Basis 1 O 0 2 A college's School of Liberal Arts has 3 departments. Estimated information for next year is presented in the table below: 3 4 39 40 1. Does the allocation of equal amounts of indirect cost provide a "fair" allocation? Answer "Yes" or "No" and explain! 41 No because allocation of Indirect cost as equal proportion is not justifiable at any cost, because different department have 42 43 different student and no, of lecturers to be talen and no of teacher to be appointed for the same, so alloctaion equally is not justificable 44 45 46 47 48 49 Select one for each: Equal Amounts Student Basis Classes Basis 50 2. Which cost driver best serves the self-interest of the Science Department? 51 Which cost driver best serves the self-interest of the History Department? 52 Which cost driver best serves the self-interest of the English Department? 53 54 55 3. What cost driver(s) would you suggest that the school use to assign a "fair" portion of overhead costs to each department? 56 Put text answer here. 57 58 59 60 61 62 63 64 65 66 Check 67 TRUE TRUE TRUE TRUE TRUE 68 69 70 71 72 TRUE TRUE TRUE TRUE TRUE A Questions Exercise 5.6 Grade Worksheet + Grade Text Answer to Question 1 Here Use: A, B, C, DF TRUE TRUE Ready + 100%
