Question

i have a project to finish, it is asking me this: Using the three columns in the Data worksheet (K, L, and M) put in the respective formulas to calculate sales revenue, variable cost, and contribution margin for the first row. Then highlight the three cells and right click on the block in the lower right to copy down the entire spreadsheet. Format numbers in the three columns as Accounting with two decimal places and no “$”.

I DONT KNOW HOW TO GET THE VARIABLE COST AND THE CONTRIBUTION MARGIN, this is all the information I have

N sales revenue variable cost contribution margin quantity_sold 434 54 450 467 380 689 85 649 100 762 340 customer_number r egion 20943 Midwest 126101 Northwest 161675 West 175749 West 216582 West 272896 Northeast 350910 East coast 394659 East coast 405112 Central 419198 West 436082 South 461385 Central 469194 Northeast 505041 East Coast 527133 Northeast 536445 South 550847 Northeast 585268 West 604530 South 620461 East coast 743401 Northeast 826584 Central 865014 West 895029 Midwest 5530 East coast 15373 Northeast 15691 West 51652 Northwest 83058 Central date_of_sale item 1/1/15 918DP 1/1/15 2981AB 1/1/15 910-128PC 1/1/15 351-128PC 1/1/15 S271-3PB 1/1/15 293-160PC 1/1/15 972DBAC 1/1/15 976-128PC 1/1/15 286-192DBAC 1/1/15 264-192BNBDL 1/1/15 B812-SN 1/1/15 193-128BC 1/1/15 918-12AEM 1/1/15 2280-ABSB 1/1/15 286-160DBAC 1/1/15 737-128BNBDL 1/1/15 749-96B-DBAC 1/1/15 527-160SIM 1/1/15 531-96ABSB 1/1/15 527-128DACM 1/1/15 80152-128SN 1/1/15 423-96PC 1/1/15 867-320BNBDL 1/1/15 110SN 1/2/15 737-128DBAC 1/2/15 293-224NI 1/2/15 667-160NI 1/2/15 935SN 1/2/15 225-160BNBDI brand Jeffrey Alexander Elements Jeffrey Alexander Elements Elements Jeffrey Alexander Jeffrey Alexander Elements Jeffrey Alexander Jeffrey Alexander Jeffrey Alexander Elements Jeffrey Alexander Jeffrey Alexander Jeffrey Alexander Jeffrey Alexander Jeffrey Alexander Jeffrey Alexander Jeffrey Alexander Jeffrey Alexander Jeffrey Alexander Elements Jeffrey Alexander Elements Jeffrey Alexander Jeffrey Alexander Jeffrey Alexander Jeffrey Alexander Jeffrey Alexander collection Prestige Florence Modena Calloway Torino Zane Marlo Belfast Leyton Alvar Backplates Asher Prestige Cordova Leyton Chesapeake Tuscany Bremen Kensington Bremen Mirada Drake Anwick Vienna Chesapeake Zane Rae Montclair Mavherk description Knob 3"pull 128 mm CC pull 128" CC pull 35CC pull 160 mm CC pull Knob 128"CC pull 192 mm CC pull 192 mm CC pull Knob Backplate 128" CC pull 12" CC app. Pull 96 mm CC pull 160 mm CC pull 128 mm CC pull 96 mm CC pull 1 160 mm CC pull 96 mm CC pull 1128 mm CC pull 128 mm CC pull 96" CC pull 320 mm CC pull knob 128 mm CC pull 224 mm CC pull 160 mm CC pull Knob 160 mm CC null HI list_price cost $ 14.14 $ 8.62 $ 6.83 $ 4.27 $ 17.68 $ 11.08 $ 7.63 $ 4.85 2.52 $ 1.60 17.15 $ 10.77 8.05 $ 5.13 7.35 $ 4.90 $ 19.50 $ 11.24 13.83 $ 8.65 5.09 $ 3.22 $ 8.55 $ 5.94 $ 167.50 $ 107.39 8.59 $ 5.36 $ 16.59 $ 10.50 $ 10.60 $ 6.87 $ 9.45 $ 5.85 $ 15.99 $ 9.92 $ 13.53 $ 7.77 $ 12.45 $ 8.05 $ 16.19 $ 8.99 6.10 $ 3.91 $ 53.75 $ 31.57 $ 3.19 $ 2.13 $ 10.60 $ 6.24 $ 22.65 $ 13.18 $ 21.05 $ 12.77 $ 13.59 $ 8.23 $ 26.62 $ 15.94 856 704 742 496 712 577 279 487 994 689 516 450 955 641 274 238 89 527

Answer 1

In the given senario,

The Situation calls for use of Marginal Costing Technique, which is one of the most widely used cost management technique for Decision Making.

In Marginal Costing technique, Variable Cost is epressed as a percentage of Sales.

Sales Revenue = Quantity sold×Sales price per unit

Variable Cost = Quantity sold × Variable Cost per unit

Contribution Margin = (Sales-Variable cost)÷Sales

For Example let us take customer number 20943, to illustrate how sales and Contribution margin could be found out in these kind of scenarios.

Sales Revenue = 434 units×$14.14=$6136.76

Variable Cost = 434 units×$8.62=$3741.08

Contribution Margin = (6136.76-3741.08)÷6136.76 =39.04%

In the above example as we have considered single customer, the Contribution margin is also only for a single customer per bill.

To get the complete picture we need to do this exercise for total sales and total Variable Cost as a percentage of Sales to get to the Contribution margin.