The Commodity Prices Worksheet provides calculations about price changes for commodities and currency conversions to show prices
Question:
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.
Spreadsheet Modeling & Decision Analysis A Practical Introduction to Management Science
ISBN: 978-0324656633
5th edition
Authors: Cliff T. Ragsdale