Explaning Linear Models using Excel Solution

Excel Solution

Although linear equations can be graphed manually, Excel provides us with excellent facilities to produce graphs that are
accurate and look more professional.

Exercise with Solution

Exercise 1: The total monthly rental cost for a Mexx store is computed from the equation C = 0.15 R + 20000
Where R is the total monthly revenue, in dollars and C is the total monthly rental cost, in dollars.

Questions:

  1. Draw an accurate graph of this equation for R=0 to R = 30000.
  2. Use the horizontal axis for R and the vertical axis for C.

Solutions:

Step 1: A table is created to include values for R and C.
    The R values go from a minimum of 0 to a maximum of 30 000 as requested by the question.
    Use an increment of 30000 / 10 = 3000 to ensure a nice spread of 10 values in your table.

    In cell A1 type "Revenue, R".
    In cell A2 type 0 and in cell A3 type 3000.

    Highlight the two cells A2:A3 and copy down until you reach the value 30 000. To create the sequence, point your cursor at the bottom right corner of the highlighted range. The shape of the cursor changes to thin plus sign. Release your cursor when you reach the desired value. In this case, the value 30 000 is reached in cell A12.

    In cell B1 type the heading "Rental Cost, C".

    In B2 enter the formula that calculates the rental cost for every value in the revenue column: =0.15*A2+20000
    Once you press enter you should get the value 20 000 displayed in cell B2.
    Copy the formula from B2 all the way down to B12.


    Your table should look like this:
    Revenue, R Rental Cost, C
    0 20000
    3000 20450
    6000 20900
    9000> 21350
    12000 21800
    15000 22250
    18000 22700
    2100 23150
    24000 23600
    27000 24050
    30000 24500
Step 2:
    Create a graph of the table values obtained in step 1.
    Highlight the entire table range A1:B12.
    Click on the Chart Wizard icon: See the rest of steps here: Excel solution to a linear equation graph


For more details, please contact me here.