Question

Assignment C Calculating Components of Operating Statements CREATING AND USING YOUR OWN SPREADSHEET 1. Download and Open file SPREADSHEET C. 2. Set up formulas in the appropriate cells to calculate the dollar value of gross margin and profit/loss. 3. Now set up formulas in the appropriate cells to calculate percentages for net sales, cost of goods sold, gross margin, operating expenses, and profit/loss. 4. Use this completed spreadsheet to make calculations for the problems that follow. Record your answers on the tables at the end of this assignment. 5. When you have completed the activity, save the file as SPREADSHEET C_COPY and then close the file. PROBLEMS Using the spreadsheet that you have prepared, enter the components of the operating statement that you have available in each problem. Then calculate the dollar value of gross margin and profit/loss. Finally, calculate the percentage value of each component of the operating statement. 1. The swimwear department has the following figures available: sales were $135,000, cost of goods sold was $120,000, and operating expenses were $11,500. 2. ABC department has the following figures available: sales were $110,000, cost of goods sold was $80,000, and operating expenses were $35,000. 3. A department had sales of $300,000. Cost of goods sold was $180,000 and operating expenses were $90,500. 4. A store has the following figures available: sales were $278,000, cost of goods sold was $190,000, and operating expenses were $78,500. 5. A convenience store had net sales of $495,000 with cost of goods sold at $250,000. Operating expenses totaled $198,568.

Answer 1

The solution to these problems is attached as follows.

The difference between the net sales and cost of goods sold is equal to the gross margin . By subtracting the operating expenses from gross margin we get Profit/ Loss.

Percentage values are calculated as a percentage of net sales.

х 128 fr P Q R Cla SPREADSHEET C - Microsoft Excel Home Insert Page Layout Formulas Data Review View Σ Pa Define Name - Trace Precedents 5 Show Formulas Calculate Now f Use in Formula Trace Dependents Error Checking Insert AutoSum Recently Financial Logical Text Date & Lookup & Math More Name Watch Calculation calculate Sheet Function Used Time Reference & Trig. Functions Manager Create from Selection Remove Arrows Evaluate Formula Evaluate Formula Window Options Function Library Defined Names Formula Auditing Calculation D12 fox А B с D E F G H 1 J K L M N o 1 Problem 1 $(Dollars) %(Percentages) 2 Net sales $ 135,000.00 100 3 Cost of goods Sold $ 120,000.00 88.88888889 4 Gross Margin $ 15,000.00 11.11111111 5 Operating Expense $ 11,500.00 8.518518519 6 Profit/ Loss $ 3,500.00 2.592592593 7 8 9 10 Problem 2 $(Dollars) %(Percentages) 11 Net sales $ 110,000.00 100 12 Cost of goods Sold $ 80,000.00 72.72727273 13 Gross Margin $ 30,000.00 27.27272727 14 Operating Expense $ 35,000.00 31.81818182 15 Profit/ Loss $ (5,000.00) -4.545454545 16 17 %(Percentages) 100 60 18 Problem 3 19 Net sales 20 Cost of goods Sold 21 Gross Margin 22 Operating Expense 23 Profit/ Loss 24 $(Dollars) $ 300,000.00 $ 180,000.00 $ 120,000.00 $ 90,500.00 $ 29,500.00 40 30.16666667 9.833333333 25 26 Problem 4 27 Net sales RE Sheet1 Ready $(Dollars) $ 278,000.00 Sheet3 %(Percentages) 100 Sheet2 BOU 100% +

х Cla SPREADSHEET C - Microsoft Excel 128 fr Σ Insert Function Home Insert Page Layout Formulas Data Review View Pa Define Name - Trace Precedents 5 Show Formulas Calculate Now f Use in Formula Trace Dependents Error Checking AutoSum Recently Financial Logical Text Date & Lookup & Math More Name Watch Calculation calculate Sheet Used Time Reference & Trig. Functions Manager Create from Selection Remove Arrows Remove Arrows Evaluate Formula Evaluate Formula Window Options Function Library Defined Names Formula Auditing Calculation C12 fx =B12/B11*100 A B С D E F G H 1 J K L M N o P Q R 16 17 %(Percentages) 100 60 $(Dollars) $ 300,000.00 $ 180,000.00 $ 120,000.00 $ 90,500.00 $ 29,500.00 40 30.16666667 9.833333333 %(Percentages) 100 68.34532374 18 Problem 3 19 Net sales 20 Cost of goods Sold 21 Gross Margin 22 Operating Expense 23 Profit/ Loss 24 25 26 Problem 4 27 Net sales 28 Cost of goods Sold 29 Gross Margin 30 Operating Expense 31 Profit/ Loss 32 33 34 Problem 5 35 Net sales 36 Cost of goods Sold 37 Gross Margin 38 Operating Expense 39 Profit/ Loss 40 $(Dollars) $ 278,000.00 $ 190,000.00 $ 88,000.00 $ 78,500.00 $ 9,500.00 31.65467626 28.23741007 3.417266187 %(Percentages) 100 50.50505051 $(Dollars) $ 495,000.00 $ 250,000.00 $ 245,000.00 $ 198,568.00 $ $ 46,432.00 49.49494949 40.11474747 9.38020202 41 42 KI Sheet1 Sheet2 Sheet3 Ready BOU 100% +

The images below show the formulas used to calculate the above solutions.

х Cla SPREADSHEET C - Microsoft Excel Home Insert Page Layout Formulas Data Review View 128 fr Calculate Now Pa Define Name - Trace Precedents 5 Show Formulas f Use in Formula Trace Dependents Error Checking Name Manager Create from Selection Remove Arrows Remove Arrows - F Evaluate Formula Defined Names Formula Auditing Σ Insert AutoSum Recently Financial Logical Text Date & Lookup & Math More Function Used - Time - Reference & Trigº Functions Function Library G15 foc Watch Window Calculation Calculate Sheet Options Calculation A B D E F G $(Dollars) 135000 120000 1 Problem 1 2 Net sales 3 Cost of goods Sold 4 Gross Margin 5 Operating Expense 6 Profit/ Loss 7 C %(Percentages) =B2/B2*100 =B3/B2*100 =B4/B2*100 =B5/B2*100 =B6/B2*100 =B2-B3 11500 =B4-B5 8 9 $(Dollars) 110000 80000 10 Problem 2 11 Net sales 12 Cost of goods Sold 13 Gross Margin 14 Operating Expense 15 Profit/ Loss 16 %(Percentages) =B11/B11*100 =B12/B11*100 =B13/B11*100 =B14/B11*100 =B15/B11*100 =B11-B12 35000 =B13-B14 17 $(Dollars) 300000 180000 18 Problem 3 19 Net sales 20 Cost of goods Sold 21 Gross Margin 22 Operating Expense 23 Profit/ Loss 24 %(Percentages) =B19/B19*100 =B20/B19*100 =B21/B19*100 =B22/B19*100 =B23/B19*100 =B19-B20 90500 =B21-B22 $(Dollars) 25 26 Problem 4 27 Net sales RE Sheet1 Ready %(Percentages) =B27/B27*100 278000 Sheet2 Sheet3 HH OU 100% +

х Cla SPREADSHEET C - Microsoft Excel 128 fx Calculate Now Insert Function Home Insert Page Layout Formulas Data Review View Σ e Define Name Trace Precedents 5 Show Formulas Use in Formula Trace Dependents Error Checking AutoSum Recently Financial Logical Text Date & Lookup & Math More Name Watch Used Time Reference & Trig. Functions Manager Create from Selection Remove Arrows Remove Arrows - Evaluate Formula Evaluate Formula Window Function Library Defined Names Formula Auditing C12 foc =B12/B11*100 A B D E Calculation Calculate Sheet Options Calculation F G 16 $(Dollars) 300000 180000 =B19-B20 90500 %(Percentages) =B19/B19*100 =B20/B19 100 =B21/B19*100 =B22/B19 100 =B23/B19*100 =B21-B22 17 18 Problem 3 19 Net sales 20 Cost of goods Sold 21 Gross Margin 22 Operating Expense 23 Profit/ Loss 24 25 26 Problem 4 27 Net sales 28 Cost of goods Sold 29 Gross Margin 30 Operating Expense 31 Profit/ Loss 32 $(Dollars) 278000 190000 =B27-B28 78500 =B29-B30 %(Percentages) =B27/B27*100 =B28/B27*100 =B29/B27*100 =B30/B27*100 =B31/B27*100 33 $(Dollars) 495000 250000 34 Problem 5 35 Net sales 36 Cost of goods Sold 37 Gross Margin 38 Operating Expense 39 Profit/ Loss 40 %(Percentages) =B35/B35*100 =B36/B35*100 =B37/B35*100 =B38/B35*100 =B39/B35*100 =B35-B36 198568 =B37-338 41 42 KI Sheet1 Sheet2 Sheet3 Ready HH OU 100% +