The Commodity Prices Worksheet provides calculations about price changes for commodities and currency conversions to show prices in selected currencies. This worksheet provides practice with mixed references. You need to provide formulas for computed cells using indicated cell references and format cells.

A commodity is a raw material or primary agricultural product that can be bought and sold on exchange markets. Commodities are priced in ticks, the minimum change in price in a trade. Since commodities are priced in US Dollars (USD), ticks are priced in US dollars. For example, the tick for light crude is $0.01 (one US cent).
a. Rename Sheet2 as Commodity Prices.
b. Provide formulas for the current price in Euros (EUR) in cell E6. The current price is the USD price (D6) times the EUR conversion rate (E3). In this formula, fix the column in the reference to the USD price (D6) and the row in the reference to the EUR conversion rate (E3).
c. Copy and paste E6 to F6:G6. Then copy E6:G6 to E7:G13. If you specified mixed referencing in the formula in E6, the formulas in the pasted cells should have correct references.
d. Provide a formula for the new price in USD (I6). The new price in USD is the current price in USD (D6) plus the product of two cells: ticks changed (H6) and increment in ticks (B6). Copy and paste this formula to I7:I13.
e. Provide a formula for the new price in EUR (J6). The new price in EUR is the current price in EUR (E6) plus the product of three cells: ticks changed (H6), increment in ticks (B6) and the EUR conversion rate (EUR). In this formula, fix the column for increment in ticks (B6) and ticks changed (H6). Fix the rows for the EUR conversion rate (E3). Copy and paste this formula to K6:L6. Then copy/paste J7:L7 to J8:L13. If you used the indicated mixed references in the formula for the new price in Euros (E6), the pasted cells should have correct formulas.
f. Perform the following formatting operations.
• Use the Heading 1 style for “Current Exchange Rates” (D1). Center and merge in D1:G1.
• Use the Heading 2 style for currency abbreviations in cells D2:G2.
• Use the Heading 2 style for D4 and I4. Center and merge D4 to D4:G4 and I4 to I4:L4.
• Use Heading 3 style for A5:L5.
• Use Currency format for cells E6:E13 and J6:J13 with two decimal digits. Use the Euro symbol instead of the $ symbol.
• Use Currency format for cells F6:F13 and K6:K13 with two decimal digits. Use the Chinese Yuan symbol (Chinese PRC) instead of the $ symbol.
• Use Currency format for cells G6:G13 and L6:L13 with two decimal digits. Use the Japanese Yen symbol (Japanese) instead of the $ symbol.
g. Auto fit column width for cells A5:L13.

 

A Platinum B 1 2 3 4 5 Commodity Increment in tick Units 6 Light crude 0.01 Barrell 7 Gold 8 Silver 9 10 Corn 11 Wheat 12 Gasoline 13 Natural Gas 14 0.01 Troy Oz 0.001 Troy Oz 0.1 Troy Oz 0.25 Bushel (100) 0.25 Bushel (100) 0.0001 Gallon 0.001 mmBtu D E Currency Exchange Rates USD EUR 1 0.8752 Current Price USD 57.6 1403 15.415 809.1 455 530.75 1.8191 2.197 EUR CNY F LL CNY JPY 6.7315 106.7603 G JPY H Tick Chang USD 50 125 950 -25 -15 20 -180 150 1 New Price EUR J K CNY JPY L M