JF MSISS Excel 2010 Tutorial 3
In this session you will learn how to:
Enter data Enter formulas Use absolute addressing Format data
1. Entering Data •
In this tutorial, you will set up a worksheet for an importing business. The worksheet will show income and expenditure for the company.
•
Open Excel and enter the data as follows: Remember to enter the weeks and months using fill down.
•
Highlight the cells B5:C16, as shown above.
•
Enter the data for the range one cell at a time, pressing Tab each time you are ready to move to the next cell. The data are as follows:100 200 100 200 300 400 100 200 500 200 100 200
•
200 100 150 200 100 100 150 200 150 100 100 200
If you select a range before entering data, as you did here, pressing TAB moves you to another cell but keeps you within the selected range. That's why when you reached the end of the January row, pressing Tab took you to the beginning of the February row.
JF MSISS
-2-
Excel Tutorial 3
•
Save your work to your filestorage area, or to the local hard disk. 2. Entering Formula
•
The first step in this example is to calculate a monthly balance. For January this is income minus expenditure.
•
The balance for February is slightly different - the difference is simply that the starting balance is denoted by the month above. I.E. previous months balance + income - expenditure
•
Calculate a balance for the remaining months. To do this, copy the formula in cell D6 to the remaining cells using the fill handle as before.
•
Now calculate the column total. This is already calculated for us, it is the total for December. Type the title "Total" in the cell B18. Enter the formula = D16 in the cell D18.
•
At this point your spreadsheet should appear as follows:
3. Using Absolute Addressing •
The tax rate is 12% and it is now necessary to calculate the new balance after tax.
•
Create two new columns after B (Income). Use the Insert button in the Cells group
•
Enter two new column titles "Income Tax" and "New Income" in cells C4 and D4 respectively.
•
The tax rate is 12%. Click on the cell D1 and type in .12 Make the cell format percentage.
JF MSISS
-3-
Excel Tutorial 3
•
Now enter the formula to multiply income by this tax rate. The formula for January is = B5 * $D$1 The dollar signs indicate absolute referencing. Pressing the F 4 key while a cell is highlighted will toggle between the relative and absolute address of the cell reference within that cell •
Copy this formula to the remaining months.
•
In the column entitled New Income, calculate the new income as Income minus Income Tax.
•
Recalculate the balance using this New Income. The spreadsheet should appear as follows:-
•
The worksheet should also show totals for Income, Income Tax, New Income and Expenditure along the bottom. Use the SUM function to produce these totals. Move the word ‘Total’ from B18 to A18.
4. Format the Data •
So far, the data in this worksheet is all formatted in the same way. After looking at it, you decide you want to show the sales figures as currency and format the title and labels.
•
Firstly change the totals at the bottom to the preset currency format. Select the range B18 to F18
•
Click on the Currency button in the Number group. The Currency format is preset to use two decimal places.
To display no decimal places click the decrease decimal places
JF MSISS
-4-
button twice.
Excel Tutorial 3
Make sure the currency displayed is Euro.
•
A few visual changes to the title and row labels will help distinguish them from the other data. Click the title in cell C3 and make the font size 14pt and Bold, again using the buttons on the Font group.
•
Center the title by clicking the centre across columns button in the Alignment group. Select A4..F4 and choose Bold and Center from the toolbar. The column labels are then boldfaced and center-aligned.
JF MSISS
-5-
Excel Tutorial 3
Assignment 3 Use Excel to calculate the economic order quantity for the basic EOQ model, and then to investigate how the quantity changes as a function of the different inventory costs. The basic economic order quantity model is a simple model to work out the costs of maintaining an inventory. Recall that the model assumes the total inventory cost per year, C(q), is: C(q)
KD hq pD , q 2
(1)
where q is the order size, K is the setup cost of an order, D is the total demand per year, p is the unit price and h is the holding cost per unit per year The aim is to find the order size q that minimises this cost. The value of q that does this is:
√
(2)
First of all, use Excel to plot the cost C(q) as a function of q, for given values of K, D, p and h that you specify. (a) Set up a worksheet with: a cell for each of the four costs K, D, p and h (initially set them to the values 0.4, 300, 50 and 0.2 respectively), a column of values of q (from 20 to 220, in units of either 20 or 10), and the corresponding value of C(q), making use of the formula (1). Create another cell with the formula for the EOQ, as in formula (2). (b) Set up a plot of order quantity (q) on the x-axis against inventory cost C(q) on the y-axis. Note that the EOQ value corresponds to the value of q on the graph where the cost is minimised!
JF MSISS
-6-
Excel Tutorial 3
(c) Now, alter the value of D from 300 to 600. This corresponds to a doubling of the demand each year. The values for C(q) will change, as will the plot, to reflect the new values of C(q). Alter D to other values and see the result.
Produce a report in Word that answers the following questions: Question 1:
Does the cost C(q) increase or decrease as D is increased? What happens to the EOQ as D is increased?
Question 2:
Repeat question 1 for the other 3 costs: K, p and h. Say what happens to the cost C(q) as each of these costs is increased or decreased? What happens to the EOQ as they are increased?
This assignment should be submitted by Friday 28th October. Please submit BOTH the word document and the Excel spreadsheet in a folder. Name the folder using your username and add – ass3.
JF MSISS
-7-
Excel Tutorial 3
