Could you please give instructions on how you created the below data table for P
ID: 2415844 • Letter: C
Question
Could you please give instructions on how you created the below data table for Part B:
Part A) The following model can be easily build in EXCEL:
Amount Sales (3,500*46) 161,000 Less Variable Cost (3,500*6) 21,000 Contribution 140,000 Less Fixed Cost 160,000 Net Profit/Loss -$20,000 For a demand of 3,500 copies, the company would incurr a loss of -$20,000 ___________
Part B) The data table with increments of 200 starting from 1,000 copies to 6,000 is given below:
1,000 1,200 1,400 1,600 1,800 2,000 2,200 2,400 2600 2,800 3,000 3,200 3,400 3,600 3,800 4,000 4,200 4,400 4,600 4,800 5,000 5200 5,400 5,600 5,800 6,000 Sales 46,000 55,200 64,400 73,600 82,800 92,000 101,200 110,400 119600 128,800 138,000 147,200 156,400 165,600 174,800 184,000 193,200 202,400 211,600 220,800 230,000 239,200 248,400 257,600 266,800 276,000 Less Variable Cost 6,000 7,200 8,400 9,600 10,800 12,000 13,200 14,400 15600 16,800 18,000 19,200 20,400 21,600 22,800 24,000 25,200 26,400 27,600 28,800 30,000 31,200 32,400 33,600 34,800 36,000 Contribution 40,000 48,000 56,000 64,000 72,000 80,000 88,000 96,000 104,000 112,000 120,000 128,000 136,000 144,000 152,000 160,000 168,000 176,000 184,000 192,000 200,000 208,000 216,000 224,000 232,000 240,000 Less Fixed Cost 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 160,000 Net Profit/Loss -$120,000 -$112000 -104,000 -96,000 -88,000 -80,000 -72,000 -64,000 -56,000 -48,000 -40,000 -32,000 -24,000 -16,000 -8,000 0 8,000 16,000 24,000 32,000 40,000 48,000 56,000 64,000 72,000 80,000 ___________
Part C) The goal seek function can be derived with the use of following table: Demand 3,500 Unit Price 46 Unit Cost 6 Fixed Cost 160,000 Sales Revenue 161,000 Variable Cost 21,000 Profit -20,000 In the goal seek function, you will select the cell containing profit figure (here, $20,000) in the "Set Cell" box. "To Value" will be put as 0, because at break even, there is no profit or loss. In "By Changing Cell", we will select the cell containing the unit price (here, $46). Once all the information has been put in the Goal Seek dialog box, we will get a price of $51.71 for a demand of 3,500 copies. Answer for Part C is $51.71.
Explanation / Answer
As you are asking about the part B issue only. Iam giving instructions for part b only.
UNITS ARE ALREADY GIVEN FROM 1000 TO 6000 AND SAID 200 INCREMENT SO ADD UP 200 FROM 1000 IN EXCEL USING IN FIRST CELL 1000 AND IN NEXT = 1000 (PREVIOUS CELL) + 200 AND DRAG THE BOX UPTO 6000
AS THE UNIT PRICE IS GIVEN AS $ 46 PER UNIT MULTIPLY THE 1000 UNIT WITH 46 AND SO ON YOU WILL GET SALES FIGURES
VARIABLE COST IS ALSO GIVEN AS $ 6 PER UNIT SO MULTIYPLY THE UNIT 1000 AND SO ON UPTO 6000 UNITS WITH $ 6 THEN WE WILL GET VARIABLE COST
USING EXCEL FORMULA = SALES FIGURE - VARIABLE COST FIGURE = WE WILL GET CONTRIBUTION FIGURES
AGAIN THEN LESS THE FIXED COST AS FIXED COST DOSENOT CHANGE THE 160000 IS COMMON FOR ALL
NOW GET THE NET PROFIT OR LOSS BY = VARIBLE COST FIGURES - FIXED COST in this mostly we got the loss.
UNITS 1000 1200 1400 1600 1800 2000 2200 2400 2600 2800 3000 3200 3400 3600 3800 4000 4200 4400 4600 4800 5000 5200 5400 5600 5800 6000 SALES 46000 55200 64400 73600 82800 92000 101200 110400 119600 128800 138000 147200 156400 165600 174800 184000 193200 202400 211600 220800 230000 239200 248400 257600 266800 27600 LESS VARIABLE COST 6000 7200 8400 9600 10800 12000 13200 14400 15600 16800 18000 19200 20400 21600 22800 24000 25200 26400 27600 28800 30000 31200 32400 33600 34800 36000 CONTRIBUTION 40000 48000 56000 64000 72000 80000 88000 96000 104000 112000 120000 128000 136000 144000 152000 160000 168000 176000 184000 192000 200000 208000 216000 224000 232000 -8400 LESS FIXED COST 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 160000 NET PROFIT / LOSS -120000 -112000 -104000 -96000 -88000 -80000 -72000 -64000 -56000 -48000 -40000 -32000 -24000 -16000 -8000 0 8000 16000 24000 32000 40000 48000 56000 64000 72000 -168400