Module 1 Assignment (MBA 550 Decisions Support Systems)

Excel Tutorial

Sample solution: Chapter 1 Problem 1

Problem Analysis: From the problem statement in exercise 1, it is apparent that the objective of the problem is to compute: Total Cost, Total Revenue, Profit, and break-even volume (part b).

The following data is given:

Fixed Cost = $8000

Variable Cost/item = $65

Sale Price/Item = $180

Number of tables = 300

Solution:

You will create an Excel worksheet to perform calculations. Although calculations in this problem are simple and can be performed using a calculator, I strongly recommend that you use Excel for this purpose. Excel itself is a very powerful calculator. This tutorial will also introduce you to how to enter text, numbers, and formulas in Excel. This will be particularly helpful, if you are not familiar with the Excel software. The screenshots are for Excel 2007, but instructions are the same even if you have an older version of Excel.

Start up Microsoft Excel 2007 or older version.

You can use multiple worksheets in an Excel file called Workbook. These worksheets are by default named sheet 1, sheet 2, etc. You will change the name of sheet 1. Right click on the sheet 1 tab (task bar at the bottom of the window) and click on Rename. Change the default name to ‘problem#1’.

In Cell A1, enter the title “The Willow Furniture Company”

In Cell A2, enter the Chapter and exercise #

In Cell A3, enter your name

In Cells A5 – A8, enter the following labels:

A5, Fixed Cost =

· In Cell A6 type Variable cost/item

· In Cell A7 type Sale Price/item

· In Cell A8 type Number of tables

In Cells B5 – B8, enter the following data:

· In Cell B5 enter 8000

· In Cell B6 enter 65

· In Cell B7 enter 180

· In Cell B8 enter 300

In this problem, our objective is to calculate:

· Total Variable Cost

· Total Cost

· Total Revenue

· Profit

· Break-even volume – part b

Enter the following labels in cells A9-A15:

· In Cell A9 type Total Variable Cost

· In Cell A10 type Total Cost

· In Cell A11 type Total Revenue

· In Cell A12 type Profit

· In Cell A14 type Part b

· In Cell A15 type Break-even volume

The screenshot below shows the data entered in the problem#1 worksheet.

Next, you will perform calculations by entering formulas in cells B9- B12, and B15. Remember to enter a formula you must start by entering an equal to (=) sign in the cell. This way Excel knows that this cell contains a formula to perform a calculation.

Total Variable Cost = Variable Cost/item * Number of tables.

Variable cost/item and number of tables are already stored in cells B6 and B8 respectively, so you will use these cell references to enter a formula to calculate total variable cost in cell B9 in the following way:

click on cell B9 and type =

click on the cell B6

type *

click on the cell B8

then press the enter key to complete entering the formula in cell B9.

Click back on cell B9, it will show the result of your calculation as 19,500 and the formula used in the calculation is shown in the formula bar.

Note that we did not type the cell references B6 and B8, instead we clicked on these cells and Excel entered these cell references automatically for us. This is highly a recommended practice as it is quick and it eliminates the possibility of making a typing error.

Next, you will calculate the Total cost in cell B10.

Total Cost = Fixed Cost + Total Variable Cost

Carry out the following:

click on cell B10

type =

click on the cell B5

type +

click on the cell B9

then press the enter key to complete entering the formula in cell B10

Cell B10 will display total cost as 27,500 and the formula used in this cell is shown in the formula bar.

Next, you will compute the Total Revenue in cell B11

Total Revenue = Sale Price/item * Number of Tables

Carry out the following:

click on cell B11

type =

click on the cell B7

type *

click on the cell B8

then press the enter key to complete entering the formula in cell B11.

Cell B11 will display total revenue as 54,000 and the formula used in this cell is shown in the formula bar.

Next, you will calculate the profit in cell B12.

Profit = Total Revenue – Total Cost

Carry out the following:

click on cell B12

type =

click on the cell B11

type -

click on the cell B10

then press the enter key to complete entering the formula in cell B12.

Cell B12 will display profit as 26,500 and the formula used in this cell is shown in the formula bar.

Part b of the exercise requires calculation of break-even volume. Remember that at break-even point, the profit is zero or total revenue is equal to total cost.

Break-even volume = Fixed Cost/(Sale Price/item – Variable Cost/item)

Carry out the following:

click on cell B15

type =

click on the cell B5

type in /(

click on cell B7

type in –

click on cell B6

type in )

then press the enter key to complete entering the formula in cell B15.

Note that it is very important to use parenthesis in the formula otherwise you will get the wrong answer.

Next, you will format cells. Cells B5:B7 and B9:B12 contain currency figures. You will format these cells to show a dollar sign in front of the numbers and display data without a decimal point.

Carry out the following:

Select cells B5:B7

Right click the mouse button and click on format cells. Click on currency and change the number of decimal places to zero.

Perform a similar procedure to format the cells B9:B12 as currency.

The screenshot below shows the formulas entered.

The screenshot below shows the results after the formulas have been entered.