Developing an Exchange Rate Calculation System

I.T. Coursework

fter the system has been designed it is necessary to implement it. The most important stage is to input the design, which is down on paper into the computer, as the end user will use the produced system on the computer. By implementing the system it will be possible to see whether the design of the system is correct, and therefore if any editing to the design is needed.

Due to the company changing its specifications for the system, I have had to add two more columns to each year to include the price of the imports and exports and the average price per kilo of molybdenum containment.

Task One Create a title Page.

Firstly I opened a new workbook and saved it as “Trade Statistics”. Next I renamed sheet one as “trade statistics” also. Next I inserted the company name at the top of the screen using Caligula font merged and centred, across cells A1 to J1. Underneath this I typed UK Trade Statistics in cell C, merged and centred this from cell C2 to H2. To show the source of the data, I typed Source: Department of Trade and Industry Library, and merged and centred this over cells C3 to H3 using Arial bold font. After this it was necessary to insert six more sheets to include all the data that needs to be show. I named these sheets: Annual Imports, Annual Exports, Imports by Type, Import Origins, Exports by Type and Export Destiny. To link these sheets to the main menu it was necessary to use macros. To do this I right click on the toolbar and pointed to forms, this added a new toolbar to the screen, which would give me all the tools I needed to insert macros. I used the button tool to create a button, and a pop up screen appeared. I pressed record macro and clicked OK. Then I selected the desired sheet I wanted the button to link to and clicked on the stop button.The final product:

This new button would now link the main menu to the selected sheet. I typed the linked sheet’s name within the box and increased the font size to 14pt. For each sheet I created a new button and macro, each of the same size, arranged in the centre of the page. At the bottom I inserted a hyperlink by using the hyperlink function from the Insert menu, this would be linked to the International Molybdenum Association website. To finish the main menu, I added a background, to do this click on the Format menu, point to Sheet, and then click Background. Then select the graphics file to use for the background pattern.

Task Two Annual Imports Sheet

To start this sheet I added a title of UK Exports of Molybdenum Products and used the Ariel font for this in 18pt. I merged and centred the title over cells A1 to E1. Then I decided to add a menu at the top of each sheet, which would be easier for the end user to navigate around the workbook. I filled cells A2 to I3 in black to create an outstanding effect for the menu. In cell A2 I typed the company name, in Caligula font, filled green text, and merged and centred it over A2 and A3. I increased the size of column A to a suitable width. In cells B2 to I3 I added four menu buttons assigned with macros. These would be a new Back to Menu button, and two imports related buttons copied from the main menu, and a button linked to annual exports also copied from the main menu. I resized these buttons appropriately to fit in four cells each. In cell AR1 was entered Complied by in italics, and merged and centered across three cells. Then the company logo was entered in cells AV1 to AZ1. I then entered Country in cell A4, and Of Origin in cell A5, using 14pt and colouring the text blue. This column “A” would include all the main products of molybdenum in 14pt Arial:

Molybdenite Concentrates & Lubricant Grade. Code: 2613.9000
Technical Moly Oxide. Code: 2613.1000.
Ferro Molybdenum. Code: 7202.7000.
Pure Moly Oxides and Hydroxides. Code: 2825.700
Molbdates. Code: 2841.7000
Molybdenum Metal products and Powders. Code: 8202.

Under each of these products I typed the main producers of Molybdenum around the world, all in 12pt blue coloured. At the end of each subsection I inserted Total. Appropriate borders were used to give the right effect.

Next was to include the data cells. In cell B4 I typed the year and “Totals”, this was in 14pt and in blue, merged and ...

This is a preview of the whole essay

Molybdenite Concentrates & Lubricant Grade. Code: 2613.9000
Technical Moly Oxide. Code: 2613.1000.
Ferro Molybdenum. Code: 7202.7000.
Pure Moly Oxides and Hydroxides. Code: 2825.700
Molbdates. Code: 2841.7000
Molybdenum Metal products and Powders. Code: 8202.

Next was to include the data cells. In cell B4 I typed the year and “Totals”, this was in 14pt and in blue, merged and centred over cells B4, C4, D4 and E4. Underneath this in cell B5, I’ve typed Nett MT in blue 14pt, in cell C5 I typed Nett £ again in blue, 14pt, in cell D5 I typed Mo MT and in cell E5 typed Mo US$ in the same format. The set of cells related to the countries in the C column were left blank for the data, except for the total for each sub section, cells which would include a formula adding the previous cells from each sub section in column C together. For example, for Moly Concentrates, the total formula would be =SUM(B39:B41).

Column C was also left blank for data input, this data would be the price of the total annual import from a country in £ sterling, therefore these cells were formatted to use £ as a currency format. The last cell of each sub section would deduce the total price for all imports of each compound (e.g. =SUM(C39:C41) ). Column D, Mo MT, is to include formulas to work out the amount of molybdenum in each of the compounds, this is done by multiplying the figure in the Nett MT column by the common concentration of molybdenum in each compound, e.g. Technical moly Oxide is made up of 59% molybdenum, so the sum to work out the concentration would be =B17*0.59.

Concentrations of Molybdenum:

Molybdenite Concs and Lub Grade: 50%

Technical Moly Oxide: 59%

Ferro Molybdenum: 70%

Pure Moly Oxides and Hydroxides: 66%

Molybdates: 66%

Mo Metal Powder and Products: 100%

Therefore appropriate formulas were made up and copied down for each country. Ecah subsection also had a total which was these figures added together, this was done again by using the SUM formula, e.g. =SUM(D61:D69). For the last column, column E, I included the price per kilo of molybdenum in US$, this was worked out by dividing the amount of molybdenum in each import (Mo MT) by the total price of the import Nett £, then multiplying this price by the annual average exchange rate, which was linked from a separate sheet (see Excahnge Rate sheet below). Therefore the formula =C9/D9*'[Exhange Rates.xls]Sheet1'!$B$4 as an example was entered in each cell, relating to the countries, in column E. This was changed accordingly depending on the cell:

These cells are then formatted to use US$ in the format menu. Although problems arose because linking the sheets would include the full destinations, therefore if the system was moved to another computer the formulas would not work. If I had furthur time to produce the project, I would pursue this problem, but as time is short I must carry on.

Below the prices per kilo from each country, an average price per kilo was calculated for each compound, an example of the formula would be: =AVERAGE(E66:E69) and this would be appropriately changed in each subsection.

Exchange Rate Sheet:

A new sheet had to be created including average annual exchange rates which were given to me by the end user. For this I typed in cell A1 Exhange Rates in bold and merged and centered this over cells A1, B1 and C1. Underneath was typed Year and merged and centered across cells A2 and A3. Beneath this I entered years from 1984 to 1996 in column A. In cell B2 I typed £ to $ exchange rate and merged and centred this over B1 and B2. Below in cell B3 entered (yearly average) merged and centered again. The yearly averages were entered, according to years in column A, in column B.

At the bottom of the sheet, more information is shown. Total Imports MT Mo was typed in cell A73; in cell D73 the total imports of MT Mo data is to be entered from the original source. Total production was then typed in cell A75, and again the data (the total of molybdenum produced by the country) is to be input into cell D75. Total availability was entered in cell A77, and the formula =D75+D73 was entered in cell D77 to work out the available molybdenum. Total Exports was typed in cell A 79, and the formula ='Annual Exports'!$AB$74 was enetered in cell D79, therefore the total was linked from the annual Exports sheet (see below). Apparent Consumption was then typed in cell A81, and in cell D81, the formula =D77-D79 was entered to calculated the apparent consumption of the country. Then Total Check was entered in cell A83, and in cell D83, the formula =IF(D70+D57+D47+D37+D23+D13-D73=0,"Total OK","Error") was entered. This is to check the total by adding the each of the total of MT Mo from each compound together and subtracting the Total Imports input by the end user from the original source. If this is equal to 0, the box come up with “Total OK”, if it is not equal to 0, “Error” will appear.

Columns B, C, D and E were then copied for years 1984 to 1996, changing appropriate formulas, and borders and shading were added in relevant places. This ends the Annual imports sheet.

The final product:

Task Three: Annual Exports sheet

Next was to implement the annual exports sheet. This is almost the same as the annual imports sheet, with little differences. Therefore repeat instructions for implementation of Annual Imports sheet, changing: of Destination typed in cell A5; UK Exports of Molybdenum Products typed in cell A1; macros changed to relevant macros; Total Imports MT Mo in cell A76 entered and formula ='Annual Imports'!$D$73 typed in cell D76 to link the annual imports total. Do not include Totals of Availablity neither Production, nor Apparent consumption. Change appropriate formulas for Annual Exports and add appropriate boarders and shading where needed.

Final product:

Task Four: Imports by type sheet:

Firstly I typed the company company name in Caligula font, italics and green, merged and centered across 3 cells, A1, B1, A2, B2, B3, and A3. I added the back to Menu, Annual Exports, Imports and Import Origins macros in a cell merged and centered across D1 to N2. I then filled cells A1 to N2 with black background. I selected cells A3 to N10 and used AutoFormat to produce a table. In coloumn A I added each of the compounds of Molybdenum, Chemicals (cell A8): (includes Pure Moly Oxides and Hydroxides and Molybdates) a Sub Toatl (cell A7) for ferromolybdenum (cell A6), Tech Oxide (cell A5) and Concentrates (cell A4) and a Total (cell A10) for all, and in row 3 the years from 1984 to 1996. I then linked the totals of each compound from the Annual imprts sheet to the appropriate cells, e.g. for Molybdenum Concentrates 1984: ='Annual Imports'!$D$13, 1985 ='Annual Imports'!$H$13, etc. For Chemicals linked the two totals added together of Molybdates and Pure Moly Oxides and Hydroxides e.g.: ='Annual Imports'!$D$47+'Annual Imports'!$D$57 for 1984. The formula for the sub total for Ferro Molybdenum, Tech Oxide and Concentrates was =B6+B5+B4 for 1984, =C6+C5+C4 for 1985 etc. And the formula for total Annual Imports was =SUM(B7:B9) for 1984 etc. This completed the table, which is to be used for a bar chart. I then used the chart wizard to process the chart, using a column chart and the whole table as the data range, excluding the Sub Total and Total. I named the Y axis: Metric tons Mo contained, and the X axis, year. The title of the chart was UK Molybdenum Imports by Type, Source: DTI Library. This concluded the implementation of the imports by type sheet.

Final product:

Task Five: Import Origins sheet:

This again is almost the same as the imports by type sheet. It can be directly copied, except changes have to be made. I selected the whole sheet, and created a new sheet, then pasted the previous sheet, naming the new sheet, Import Origins. I then changed the Import Origins macro to the Imports by type macro. In the table, the compounds and sub total was changed to countries. I included the main markets that the company are interested in. This included: US, Canada, Chile, China, Europe and an Others total. All the formulas within the tables had to be changed. The total of imports from each country for each year were linked from the annual import sheet and added together for a total to produce the cells, e.g. I linked the totals of imports from the United States in 1984 from each compound and added them together to produce the overall total imports from the US, formula ='Annual Imports'!$D$9+'Annual Imports'!$D$17+'Annual Imports'!$D$27+'Annual Imports'!$D$41+'Annual Imports'!$D$51+'Annual Imports'!$D$61. For 1985 the formula would be ='Annual Imports'!$H$9+'Annual Imports'!$H$17+'Annual Imports'!$H$27+'Annual Imports'!$H$41+'Annual Imports'!$H$51+'Annual Imports'!$H$61 etc. But for Others, the formula included all other countries totals not included in the table, e.g. for 1985 formula ='Annual Imports'!$H$12+'Annual Imports'!$H$22+'Annual Imports'!$H$36+'Annual Imports'!$H$46+'Annual Imports'!$H$56+'Annual Imports'!$H$69. Europe was also done in the same way e.g. 1996 formula for Europe =SUM('Annual Imports '!$AZ$20:$AZ$21) +SUM ('Annual Imports'!$AZ$29:$AZ$33) +SUM('Annual Imports'!$AZ$43)+SUM('Annual Imports'!$AZ$52) +SUM('Annual Imports'!$AZ$62:$AZ$65). The total for each year also had to be corrected, adding the totals of all countries in the corresponding year. E.g. for 1990: =SUM(H4:H9). The chart title is then changed to UK Molybdenum Imports by Origin, Source: DTI Library. This ends the Import Origins sheet implementation.