Excel / Calc Assignment 1 In this assignment, you will create a spreadsheet for
ID: 667775 • Letter: E
Question
Excel / Calc Assignment 1
In this assignment, you will create a spreadsheet for a small home business which is selling three vitamin products: CoQ-10, Iron, and Zinc. The owner wants to look at sales by product for the first quarter of the year and calculate monthly profit on the products. Open a spreadsheet and do the following:
Merge cells B1 through D1 and type Sales & Profit in all caps. Center this in the merged cells.
In cell B2, type CoQ-10. In cell C2, type Iron. In cell D2, type Zinc. Center these in their cells and make them bold.
In A3, type Jan. In A4, type Feb. In A5, type Mar. These should all be bold as well.
Type in some numbers to represent the sales of each vitamin product for each month. Each number should be different. Make sure they all have a dollar sign. You need to do this by formatting the cells as currency with no decimal places.
In A7, type Sales and make it bold and in italics.
Using the SUM function, calculate the total sales for each product. These results should be in cells B7, C7, and D7.
In A8, type Average Sales in bold and italics.
Using the average function, find the average sales for each product. These results should be in cells B8, C8, and D8.
In E2, type Profit in bold and italics and centered in the cell.
Each product has a different profit margin. The CoQ-10 product has a 10% profit margin. This means if $80 is sold, the profit is 0.10*80 or $8. The profit margin for Iron is 15%, and the profit margin for Zinc is 5% (which is a multiplication factor of 0.05). In E3, calculate the total profit for January from the sales of the three products. You will need to type in the formula rather than use a function (like SUM) to do this. Remember that to multiply, you must use the asterisk symbol (hold shift while hitting the 8 key). Do the same to determine the February and March profits.
In A9, type Total Profit in bold and italics.
In E9, determine the total profit. Fill this cell with a different color so that it stands out.
Save your spreadsheet with the filename LastNameWeek6 and upload it in the assignment area.
Explanation / Answer
SPREADSHEET
A spreadsheet is generic termfor a software package that simulates a paper worksheet often usedby people in management. A spreadsheet supports fast calculations, what-if-analysis, data-graph,auto recalculation, etc. It iscombination of worksheets that comprise of data arranged in rowsand columns. The first spreadsheet ever to be
created was the VisiCalc by Can Bricklin ofHarvard University. In the market there are a lot of spreadsheetpackages available like Lotus
MS-EXCEL of MS-OFFICE.
MS-EXCEL
MS-Excel is an advanced Worksheet (or spreadsheet) product for IBM AT or PS/2 compatibles
that also supports database, graphic and presentation features. Excel incorporates all the user-friendly features of Windows. Like most of the Windows application in Excel you can minimize or maximize worksheets. FEATURES OF MS-Excel There are two main features of
1. MS-Excel Analysis features.
2. Presentation features. Analysis Features
The windows interface includes windows, pull down menus, dialog boxes and mouse support.
Linking of worksheets.
• Repetitive tasks can be automated with MS-Excel Easy to use macros and user defined functions.
• Full featured graphing and charting facilities.
• Supports on screen databases with querying, extracting and sorting functions.
• Perm its the user to add, edit, delete and find database records. Presentation Features
• Individual cells and chart text can be form atted to any font and font size supported by Windows.
• Variations in font size, style and alignment control can be determined.
• The user can add legends, text, pattern, scaling and symbols to charts.
• Worksheets can be printed horizontally or vertically. Getting started with worksheet
• Click on the Start button.
• Select Program menu.
• Select Excel icon from the icon list. Now a blank worksheet labeled Sheet 1 will be displayedand ready for use.
MS-Excel: An Overview
Quitting MS-Excel
To Exit from
MS-Excel
• Click on File menu and select the Exitoption otherwise press the AltKey and Function Key F4
together.
• You can also press the X button that appears in the right hand corner to exit from Excel.
Introduction of a worksheet The primary document you use in Microsoft Excel to store and work with data. A worksheet consists of cells organized into columns and rows and is alwayspart of a workbook, also called as a spreadsheet. In Microsoft Excel, a workbook is
a file in which you can store your data. Since
each workbook can contain many sheets, you can organize various kinds of related information in a single file. RowsRows are horizontally defined lines, numbered from top to bottom along the left edge of the worksheet. The first row is numbered 1, the second 2 and so on. There are 65,536 rows. ColumnsColumns are vertical lines labeled from left toright with letters. The first column is A, thesecond is B and so on until the user reaches Z.
e cells also have their specific address with the combination ofrow number and column label. For exam
ple, Cell E6 is the intersection of column E and row 6.MenusThe Menu bars have the option for creating, editing,intersecting charts or graphs and providingthe same features as in MS-Word. Each menu is accessedbyeither clicking on it with the mousepointer or by pressing theALT Key to activate the menu barand then typing the underscoredletter. This pulls down the corresponding menu. Press Escape key to cancel down the lastselection.
Formula BarFormula Bar is the blank line that appears on the top of the sheet. This area is used to writeformula tonmanipulate the data. Instead of thata box also appears along with the formula bar thatdisplays the address of every cell.
MS-Excel Standard Toolbar
A number of icon are available under the MS-Excel Standard Toolbar, description of thesymbols are given below12
MS-ExcelData Entry in WorksheetTo enter the data in a worksheet, the following steps are to be followed.
•Activate the cell by moving the MS-Excel cursor (rectangular box) or by clicking with the leftmouse button.
•Enterthe data from the keyboard.
•Lock the data by pressing the Enter key.Use Formula to Analyze DataA formula is a relationship between cells. It specifies what operations are to be performed on thevalues of the cells it relates to. It usually contains arithme
tic values, expressions cell addresses orranges. Using a formula can help you analyzedata on a worksheet. Theseare required to domathematical functions like sum
,
Click Bring to Front or Send to Back button.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.