关键词 > Excel代写
Practical Exercise 1: Getting to know Microsoft Excel
发布时间:2023-03-23
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Practical Exercise 1: Getting to know Microsoft Excel
Create a Workbook file that compares budgeted to actual income and expenses for the month . Start or Open Microsoft Excel (depends on version of operating system).
Create the Spreadsheet.
• Click on the Newbutton on the standard toolbar.
• A new spreadsheet document appears.
• Choose Save from the File menu. The Save AS dialog appears. Type Budget in the File Name box. Your file will be saved as Budget.xlsx
**** Important: Save your spreadsheet regularly ****
Instructions
Type in the following spreadsheet, and format it to look like the sample below. Be sure that the information is entered in the same cells as given, or the formulas given below will not work. To enter information into a cell you must activate it. That means moving the cell pointer to the cell in which you want to enter a label (text entry), value (number, date, time) or formula (calculation written in special notation.).
Income Items |
Budget |
Actual |
Difference |
%Difference |
|
|
|
|
|
Sales |
8200 |
9103 |
|
|
Interest Income |
100 |
83 |
|
|
Other Income |
200 |
115 |
|
|
Purchases |
5800 |
6020 |
|
|
Repairs |
2300 |
2400 |
|
|
Total Income |
|
|
|
|
1. Enter Row Headings
• Activate cell A1 (the first cell in the worksheet).
• Type Income Items
• Press Enter. The cell pointer moves down one cell.
• Repeat these steps for the following labels:
• Sales
• Interest Income
• Other Income
• Purchases
• Repairs
2. Enter Column Headings
This budget worksheet includes several columns of data and calculations. We will use column heading to identify them.
• Activate cell B1 (the one to the right of where you entered Income Items).
• Type Budget.
• Press Tab. The cell pointer moves one cell to the right.
• Repeat these steps for the following labels:
• Actual
• Difference
• %Difference
3. Enter Values
The whole purpose of the worksheet is to compare budgeted to actual amounts so enter values into the two columns Budget and Actual as shown in the spreadsheet above.
4. Create a formula to calculate a Difference
Column D will display the difference between budgeted and actual amounts. It will use simple formulas that subtract the contents of one cell from the contents of another cell using cell references.
• Activate cell D3.
• Type =
• Click in cell C3. Cell reference C3 appears in cell D3.
• Type -
• Click in cell B3. Cell reference B3 is appended to the formula in cell D3.
• Press Enter.
• The result of the formula you entered appears in cell D3. Your formula should read =C3-B3
5. Create a formula to calculate a Percentage Difference
Column E calculates the percent difference between the budgeted and actual amounts. The percentage is based on the budgeted amount. We will write the formula now and copy it to other cells in Column E later.
• Activate cell E3.
• Type =
• Click in cell D3. Cell reference D3 appears in E3.
• Type /
• Click in cell B3. Cell reference B3 is appended to the formula in cell E3.
• Press Enter.
• The result of the formula you entered appears in cell E3. Your formula should read =D3/B3
6. Sum (add) certain values: Create formulas to display the Total Income for Budget and Actual.
Although you can write a formula that adds multiple cell references, one cell at a time, it is much easier and faster to use Excel’s SUMfunction to add up the contents of a range of cells. Here is a way to enter the SUMfunction in a formula to create a subtotal in column B.
• Activate cell B8.
• Type =SUM(
A function tool-tip may appear as you enter the formula.
• Position the mouse pointer on cell B3.
• Press the mouse button and drag down to cell B7. All cells you dragged over are selected and referenced in the formula in cell B8.
• Type )
• Press Enter. The result of the formula appears in cell B8. Your formula should read =SUM(B3:B7)
• Repeat these steps to calculate Total Income for the Actual Column.
7. Copy and paste using the fill handle.
Excel lets you copy a formula in one cell to another cell that needs a similar formula. This can save a lot of time when building a worksheet with multiple columns or rows that need similar formulas. A quick way to copy the contents of one cell to one or more adjacent cells is with the fill handle. A fill handle is a tiny square in the bottom-right corner of the selection box. We will use the fill handle to finish the worksheet entries. The mouse pointer turns into a black cross.
• Drag to select cells D3 and E3.
• Position the mouse pointer on the selection’s fill handle. The mouse pointer turns into a black cross.
• Press the mouse pointer down and drag so the border completely surrounds cells D3 through to E7.
• Release the mouse. The two formulas are copied down to the cells you dragged over.
8. Change a value.
In reviewing this worksheet, we realise that we have made an error when entering values. The actual sales amount for the month was not 9103 as we entered. It was actually 8103! Enter the correct value now.
• Activate cell C3.
• Type 8103. This new value overwrites the value already entered.
• Press Enter.
What do you observe?
The value changes, but what’s more important is that all the formula results that referenced that value, either directly or indirectly, also change.
9. Format Currency
• Select cells B3 to D8
• Click right mouse button
• Select Format Cells from the menu
• Under the Number tab select Currency
• Make sure you have 2 decimal places & the $ symbol
• Click ok.
10. Format Per(.)centage
• Select cells E3 to E7
• Click right mouse button
• Select Format Cells from the menu
• Under the Number tab select Percentage
• Make sure you have 0 decimal places
• Click ok.
11. Rename the worksheet Income.