In many situations, a retailer may not compile a profit or loss statement for individual departments.Suppose that you are a retail buyer, and you decide to recreate the statement for your own department using the following data:

Gross sales of $72,300, net sales of $60,900, opening inventory of $13,900, ending inventory of $19,400, purchases of $14,000, freight of 2%, cash discounts of 5%, returns to vendors of $500, alterations and workroom costs of 3%, direct expenses of $19,600, and total expenses worth $35,000.

(Hint: Remember that freight and cash discount %'s are calculated as % of Purchases, while the alteration/workroom costs % is expressed as % of NS).

1)Create a spreadsheet in Microsoft Excel that looks exactly like the chart shown below.

2)Using the operational data provided above, enter the given data into the appropriate cells. (0.5 points for each correct cell)

3)Using formulas, calculate theremaining SHADED cells(Do NEVER type in number answers directly in the spreadsheet. You MUST enter formulas using cell references [e.g., C2, B5] and mathematical functions [e.g., +, -, *, /]) (0.5 points for each correct cell). You may leave non-shaded cells below blank.

$

%

Gross Sales

$72,300.00

Customer Returns & Allowances

Net Sales

Opening Inventory

Purchases

Transportation Costs

Cash discounts

Returns to Vendors

Alteration & Workroom Costs

Ending Inventory

Cost of Goods Sold

Gross Margin

Direct Expenses

Indirect Expenses

Total Operating Expenses

Operating profit or loss

4) Remember that:

The spreadsheet should be formatted so that it looks exactly like the above table (e.g., bold, cell alignment)(0.2 points)

All $ figures should have $ signs in front, and all % figures should have % signs at the end.(0.2 points)

Cells containing numbers should be right justified, and all figures should be expressed with 2 decimal places(0.2 points)

You should apply all the spreadsheet assignment requirements on the syllabus (font, gridlines, header, landscape, one page).(0.4 points)