Please click on the attachment to access Mini-Project I.

Example 1 Service Charge Allocation (Adapted from Finite Mathematics, Models, and Structure,Revised Edition by William J. Adams, copyright 2009.) is worked out completely. Follow this example to do your mini-project 1, which is the last exercise listed at the end of the document attached i.e.

Exercise: The Sonin company….

Rules:

1. The project should be typed.

2. Clearly model the system and answer all parts of the exercise.

3. Solve using Excel. The Excel printout should be attached.

Example 1 Service Charge Allocation (Adapted from Finite Mathematics, Models, and Struc- ture, Revised Edition by William J. Adams, copyright 2009.)

The Arkin Company, which makes television sets, has three production departments, which we shall denote by P1, P2, and P3, and four service departments—accounting, maintenance, market-

ing, and purchasing. Each service department’s cost must be distributed to the production depart- ments and to other service departments based on their respective usages of the services provided.

For each service department listed in the leftmost column of Table 1, the fraction of its total cost assigned to the service and production departments of the firm is given. Thus from row 1 we have that 2 percent of the total cost of accounting is assigned to maintenance, 10 percent of the total cost of accounting is assigned to marketing, and so on. Column 1 specifies the fraction of the ser- vice departments’ costs that is assigned to accounting, column 2 specifies the fraction of the ser- vice departments’ costs that is assigned to maintenance, and so on. (Such determinations can be made in various ways; one way is based on the floor space occupied by each department.) The problem is to determine each service department’s total costs (overhead plus charges for services provided by other departments) and to allocate these costs to the production departments.

Let x, y, z, and w denote the total costs in January, measured in dollars, of the accounting, maintenance, marketing, and purchasing departments, respectively. From column 1 we have that the costs of the maintenance, marketing, and purchasing departments that are assigned to accounting are 0.10y, 0z, and 0.10w, respectively. The overhead of the accounting department (costs that are directly assigned to accounting such as salaries of employees in accounting, equipment, supplies, etc.) for January is $20,000. Thus x, the total cost of accounting, must satisfy the following condition:

A similar analysis for the maintenance, marketing, and purchasing departments yields the following conditions:

Table 1:

Service Department

Service Departments Production

Departments January

Overhead

Acc. Main. Mar. Pur. P1 P2 P3

Accounting 0 0.02 0.10 0.10 0.24 0.26 .028 $20,000

Maintenance 0.10 0 0.20 0.10 0.20 0.20 0.20 $18,000

Marketing 0 0 0 0 0.30 0.30 0.40 $80,000

Purchasing 0.10 0.10 0.10 0 0.20 0.20 0.30 $10,000

x 20000 0.1y 0.1w+ +=

Rearranging terms gives us the following system:

Here I have included the 0z terms to make the switch below to matrix notation easier to follow.

If let , , and . We can write the above system as

. If A is invertible, we can find and hence know the values of x, y, z, and w.

Using Excel, with the MINVERSE command, we can easily find the answer:

y 18000 0.02x 0z 0.1+ w+ + z 80000 0.1x 0.2y 0.1w+ + + w 10000 0.1x 0.1y 0z+ + +

= = =

1x 0.1y– 0z 1w–+ 20000 0.02x– 1y 0z 0.1w–+ + 18000 0.1x– 0.2y– 1z 0.1w–+ 80000

0.1x– 0.1y– 0z 1w+ + 10000

= = = =

1 0.1– 0 1–

0.02– 1 0 0.1–

0.1– 0.2– 1 0.1–

0.1– 0.1– 0 1

x

y

z

w

20000

18000

80000

10000

=

A

1 0.1– 0 1–

0.02– 1 0 0.1–

0.1– 0.2– 1 0.1–

0.1– 0.1– 0 1

= u

x

y

z

w

= b

20000

18000

80000

10000

=

Au b= u A 1– b=

b u

1 ‐0.1 0 ‐0.1 1.013514 0.112613 0 0.112613 $20,000 $23,423.42

‐0.02 1 0 ‐0.1 0.030713 1.013514 0 0.104423 $18,000 $19,901.72

‐0.1 ‐0.2 1 ‐0.1 0.117936 0.225225 1 0.134316 $80,000 $87,755.94

‐0.1 ‐0.1 0 1 0.104423 0.112613 0 1.021704 $10,000 $14,332.51

Matrix A ‐1

Matrix A

Michael Kazlow

Text Box

Attached to this document are the Excel file I used to solve the above problems. I have also attached pdf files found on the internet that show you how to use the MMULT and MINVERSE commands in Excel. I have posted in the file meta-data the urls at which I found these files. There is no attribution of the original source of these files.

We find and

. To compute the values

of u in Excel we need the MMULT command.

Hence the costs of running the Accounting, Maintenance, Marketing, and Purchasing for the month of January is $23,423.42, $19,901.72, $87,755.04, and $13,332.51, respectively.

From these cost values and the percentages given in columns 5,6, and 7 of Table 1, we obtain the following allocation of service departments costs to each production department. Here we let c1,

c2, and c3, be the cost in dollars of the cost the service departments’ costs assigned to departments,

P1, P2, and P3, respectively.

Here we let C be the cost matrix of the production departments. , where

is the matrix respective cost allocations (i.e, pi,j is the proportion production depart-

ment Pi assigned to service department j.

. We compute C as follows

. So that the service cost for the

month of January of Departments P1, P2, and P3 are approximately, $38,795.25, $39,263.72, and

$49,941.03, respectively. We can compute the costs using Excel, with the MMULT command.

A 1–

1.013514 0.112613 0 0.112613

0.030713 1.013514 0 0.104423

0.117936 0.225225 1 0.134316

0.104423 0.112613 0 1.021704

=

u

x

y

z

w

1.013514 0.112613 0 0.112613

0.030713 1.013514 0 0.104423

0.117936 0.225225 1 0.134316

0.104423 0.112613 0 1.021704

20000

18000

80000

10000

23423.42

19901.72

87755.94

14332.51

= = =

C

c1 c2 c3

Pu= =

P pi j =

P 0.24 0.20 0.30 0.20

0.26 0.20 0.30 0.20

0.28 0.20 0.40 0.30

=

C

c1 c2 c3

0.24 0.20 0.30 0.20

0.26 0.20 0.30 0.20

0.28 0.20 0.40 0.30

23423.42

19901.72

87755.94

14332.51

38795.249

39263.717

49941.031

= = =

Exercise:

The Sonin Company, which makes computers, has two production departments, P1, and P2, and three service departments S1, S2 and S3. Each service department’s total cost must be distributed to the production departments and to the other service departments based on their respective usages of the services provided. For each service department listed in the leftmost column of Table 2, the fraction of its total cost assigned to the service and product departments of the firm is given.

(a) Set up the system of equations that describes the conditions to be satisfied. Be sure to label your variables including units. (b) Solve this system to determine the total costs of the service departments. (c) Determine the allocation of the total costs of the service departments to the production departments.

Table 2:

Service Department

Service Departments Production Department

s

January Overhead

S1 S2. S3. P1 P2

S1 0 0.10 0.05 0.40 0.45 $40,000

S2 0.10 0 0.10 0.40 0.40 $30,000

S3 0.20 0.05 0 0.35 0.40 $20,000

C

0.24 0.2 0.3 0.2 38795.25

0.26 0.2 0.3 0.2 39263.72

0.28 0.2 0.4 0.3 49941.03

Matrix P

- Example 1 Service Charge Allocation (Adapted from Finite Mathematics, Models, and Structure, Revised Edition by William J. Adams, copyright 2009.)
- Table 1:

- Exercise:
- Table 2:

Matrix A | Matrix A-1 | b | u | ||||||||||

1 | -0.1 | 0 | -0.1 | 1.0135135135 | 0.1126126126 | 0 | 0.1126126126 | $20,000 | $23,423.42 | ||||

-0.02 | 1 | 0 | -0.1 | 0.0307125307 | 1.0135135135 | 0 | 0.1044226044 | $18,000 | $19,901.72 | ||||

-0.1 | -0.2 | 1 | -0.1 | 0.1179361179 | 0.2252252252 | 1 | 0.1343161343 | $80,000 | $87,755.94 | ||||

-0.1 | -0.1 | 0 | 1 | 0.1044226044 | 0.1126126126 | 0 | 1.0217035217 | $10,000 | $14,332.51 | ||||

Matrix P | C | ||||||||||||

0.24 | 0.2 | 0.3 | 0.2 | 38795.2497952498 | |||||||||

0.26 | 0.2 | 0.3 | 0.2 | 39263.7182637183 | |||||||||

0.28 | 0.2 | 0.4 | 0.3 | 49941.0319410319 |

Using MS Excel in Matrix Multiplication

Example 1: If � �

� � �

�

− −

= 504 312

A and � � �

�

�

� � �

�

�

− −=

34 13

02 B ; Find A.B and name the resulting matrix as E

a) Enter the matrices A and B anywhere into the Excel sheet as:

Notice that Matrix A is in cells B2:D3, and Matrix B in cells G2:H4

b) We multiply Row by Column and the first matrix has 2 rows and the second has 2 columns, so the resulting matrix will have 2 rows by 2 columns.. Highlight the cells where you want to place the resulting matrix E:

c) Once you have highlighted the resulting matrix, and while it is still highlighted, enter the following formula:

=MMULT(B2:D3,G2:H4) d) When the formula is entered, press the Ctrl key and the Shift key simultaneously, then

press the Enter key. This will change the formula you just wrote to: {=MMULT(B2:D3,G2:H4)}

If you don’t press these keys simultaneously (holding down Shift and Ctrl then press Return), the result will appear only in one cell or, you will get some error message).

e) The resulting matrix will be:

Example 2: Repeat the previous example, but this time find B.A and name the resulting matrix as F

We multiply Row by Column but this time the first matrix has 3 rows and the second has 3 columns, so the resulting matrix will have 3 rows by 3 columns.. Once you have highlighted the resulting matrix, and while it is still highlighted, enter the following formula:

=MMULT(G2:H4,B2:D3) When the formula is entered, press the Ctrl key and the Shift key simultaneously, then press the Enter key. This will change the formula you just wrote to:

{=MMULT(G2:H4,B2:D3)}

Example 3: If � �

� � �

� =

7.03.0 4.06.0

A , find A2, A3, A4 and A8.

Since A has 2 rows and 2 columns and we are multiplying by itself, then the resulting matrices will also have 2 rows and 2 columns. Enter the matrices A anywhere into the Excel sheet as:

The answers can be found as:

A2 = A.A

A3 = A2.A

A4= A2. A2

A8= A4. A4

As we did before, highlight the resulting matrix, and while it is still highlighted, enter the formula. When the formula is entered, press the Ctrl key and the Shift key simultaneously, then press the Enter key.

• A2 = A.A and the formula : =MMULT(B2:C3,B2:C3)

• A3 = A2.A and the formula : =MMULT(B2:C3,B6:C7)

• A4= A2. A2 and the formula : =MMULT(B6:C7,B6:C7)

• A8= A4. A4 and the formula : =MMULT(B11:C12,B11:C12)

Using MS Excel in Finding the Inverse Matrix

Example: If � � �

�

�

� � �

�

�

− −

= 253 504 312

A ; Find the inverse or A-1

a) Enter the matrices A into the Excel sheet as:

Notice that Matrix A is in cells B2:D4

b) We find the inverse of matrix A by Highlighting the cells where you want to place the resulting matrix A-1

c) Once you have highlighted the resulting matrix, and while it is still highlighted, enter the following formula:

= MINVERSE(B2:D4) d) When the formula is entered, press the Ctrl key and the Shift key simultaneously, then

press the Enter key. This will change the formula you just wrote to: {= MINVERSE(B2:D4)}

If you don’t press these keys simultaneously (holding down Shift and Ctrl then press Return), the result will appear only in one cell or, you will get some error message).

e) The resulting matrix will be:

Basic features

- Free title page and bibliography
- Unlimited revisions
- Plagiarism-free guarantee
- Money-back guarantee
- 24/7 support

On-demand options

- Writer’s samples
- Part-by-part delivery
- Overnight delivery
- Copies of used sources
- Expert Proofreading

Paper format

- 275 words per page
- 12 pt Arial/Times New Roman
- Double line spacing
- Any citation style (APA, MLA, Chicago/Turabian, Harvard)