Matrices can be used to encode and decode messages


Excel Design – Matrix Applications

Part 1 Cryptography

Matrices can be used to encode and decode notices. To inaugurate after a while, we could convey the aggregate 1-26 to the lore of the alphabet and convey the enumerate 0 to a interval between say. Then, we could transform any parole notice into a string of aggregate from 0 to 26. Underneath is a board of each missive and its similar enumerate.

Blank

A

B

C

D

E

F

G

H

I

J

K

L

M

0

1

2

3

4

5

6

7

8

9

10

11

12

13

 

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

14

15

16

17

18

19

20

21

22

23

24

25

26

 

We allure use the matrix A underneath as the encoding matrix for our notice.

Enter the encoding matrix A in your spreadsheet and mark it.

Suppose we omission to encode the notice “SECRET CODE”. Initiate by translating the notice into aggregate using the adaptation balancehead.

Now put these aggregate into a matrix B that allure bear 3 rows. Initiate after a while the chief enumerate in the preferable left and propose down the chief post until you bear 3 rows. When you content the post, initiate balance in the cooperate post. Content in the matrix B after a while the aggregate similar to the notice. If you deficiency to, add any extra blanks to the end of the notice to content out the matrix.

Create matrix B on your spreadsheet and fashion indispuboard to mark it.

To encode a notice, merely interest the matrix that contains our notice, matrix B, and extend on the left by the encoding matrix A. Using Excel, experience matrix  and mark it.

Now our notice “SECRET CODE” has been encoded and is personateed by the aggregate in matrix AB.

Suppose now that you are the one receiving this encoded notice as personateed by matrix AB, and you omission to decode it to see what it says. To do this, you allure deficiency the decoding matrix . Experience  and mark it.

Now effect the matrix teeming  to see what you get.

Suppose now that you bear current an encoded notice that contemplates love

52

28

28

25

27

35

22

 

 

89

47

36

50

41

50

37

 

 

110

46

68

35

71

75

36

 

 

 

Enter this matrix in Excel and mark it C.

 

We omission to experience out the peculiar notice that was encoded to contemplate love matrix C balancehead.

use the decoding matrix  to experience the peculiar notice.

 

use the board at the top to decode the notice and transcribe the notice in your Excel spreadsheet.

Part 2 – Rendering Cost

A sub-contractor deficiencys to run which companies they should employ for each stamp of structure they omission to invent.

Below is the estimated spell it should interest (in hours) for each of the opposed stamps of structures they employment on.

Building

Excavating

Framing

Electrical

Plumbing

Finishing

Office Space

45

100

88

32

312

School

56

250

47

28

270

Apartments

84

480

75

25

244

Grocery Store

95

160

26

78

236

Below are the hourly rates for some opposed invention companies in the area.

Company

Bouma

Pinnacle

Rockford

McGraw

Excavating

$350

$300

$285

$245

Framing

$225

$275

$280

$280

Electrical

$405

$375

$295

$350

Plumbing

$150

$240

$225

$200

Finishing

$250

$190

$260

$215

 

Input the counsel in the prior two boards in Excel. Fashion indispuboard that you mark all the counsel in your spreadsheet.

use matrix teeming to state which companies can perfect each design at the smallest consume. Mark your new matrix after a while the misapply marks.

Highlight the cells that concede the smallest consume for each structure design.

Make a board that perspicuously identifies which concourse should be employd for each design.

 

 

 

 

 

Part 3 – Steelcase Production

Steelcase Corporation constructions 3 basic works: chairs, desks, and boards. Underneath is chart which summarizes the enumerate of drudge hours spent for each work in each resistance.

 

Chairs

Desks

Tables

Process

Carpentry

2

3

6

Finishing

1

1

1

Assembly

4

5

2

 

In a conceden week, Steelcase has 250 hours acquisitionable for carpentry, 100 hours acquisitionable for finishing, and 400 hours acquisitionable for nock.

Steelcase fashions a avail of $66 on each chair, $75 on each desk, and $100 on each board that they dispose-of.

Steelcase too deficiencys to product at smallest one chair for entire desk they product, and 4 chairs for entire board they product. The sum enumerate of chairs must be superior than or correspondent to the sum of the chairs deficiencyed for desks and boards. They can product over chairs on their own too.

How divers chairs, desks, and boards should Steelcase construction each week in dispose to maximize avail?

Set up an Excel Spreadsheet to unfold this straight programming collection. You may omission to use the template underneath.

 

Excel has a office that allure unfold straight programming collections love this one. To advent this office, go to the “Data” tab and choice “Solver”.  A window opens in which you deficiency to set the subjoined parameters for the LPP.

Set Target Cell – Choice the cell that personates the external office. This should be the appreciate that you omission to maximize or minimize.

Equal To – Prefer whether you omission to maximize, minimize, or set a local appreciate for your external office.

By Changing Cells – Choice the cells that personate your sentence variables.

Subject to the Constraints – To set up your constraints, prefer “Add”.  Now you can set up your inequalities. One set of inequalities is that the enumerate of drudge hours used must be short than the enumerate of hours acquisitionable. Too fashion indispuboard that the enumerate of units constructiond is short than or correspondent to the enumerate of units call-fored. Finally, fashion indispuboard that the sentence variables are nonnegative.

 

Once you bear set up the LPP, click “Solve”. If there is a explanation, Excel allure content in the spreadsheet after a while the appreciates that allure unfold the LPP.

Use Excel to unfold this collection. Highlight the optimal sentence variables in untried and the climax avail in cerulean.

 

 

Part 4 – Transportation Problem

Pioneer Corporation dispose-ofs car speakers. They bear two factory locations in the Midwest where they construction car speakers. One is in Flint, MI and one is in Kalamazoo, MI. They too bear two greater garners in the Midwest where they dispose-of their speakers, one in Chicago and one in Detroit.

Below is a board that concedes the shipping consumes for each set of car speakers from each factory to each garner.

Shipping Costs

Stores

Factories

Detroit

Chicago

Kalamazoo

$7.43

$5.08

Flint

$3.92

$8.47

 

The factory in Kalamazoo has a minister of 700 sets of speakers, and the factory in Flint has a minister of 900 sets of speakers. So the factories cannot ship over than they currently bear.

The garner in Detroit has a call-for for 500 sets of speakers, and the garner in Chicago has a call-for for 1000 sets of speakers. So the garners deficiency to accept at smallest this divers speakers (perchance over).

What should be the shipping register that fulfills the call-for for each garner and minimizes shipping consumes?

 

use the Excel “solver” office to unfold this straight programming collection after a while 4 sentence variables. Highlight the optimal sentence variables in untried and the poverty shipping consume in cerulean.