BUSINESS SIMULATION LAB 15X51E00xx



EXERCISE 1CUSTOMIZE THE QUICK ACCESS TOOLBARThe Quick Access Toolbar is a customizable toolbar that contains a set of commands that are independent of the tab on the ribbon that is currently displayed. You can move the Quick Access Toolbar from one of the two possible locations, and you can add buttons that represent commands to the Quick Access Toolbar.Add a command to the Quick Access ToolbarOn the ribbon, click the appropriate tab or group to display the command that you want to add to the Quick Access Toolbar.Right-click the command, and then click Add to Quick Access Toolbar on the shortcut menu.Add a command to the Quick Access Toolbar that isn’t on the ribbonClick Customize the Quick Access Toolbar > More Commands.In the Choose commands from list, click Commands Not in the Ribbon.Find the command in the list, and then click Add.Remove a command from the Quick Access ToolbarRight-click the command you want to remove from the Quick Access Toolbar, and then click Remove from Quick Access Toolbar on the shortcut menu.Change the order of the commands on the Quick Access ToolbarRight-click the Quick Access Toolbar, and then click Customize the Quick Access Toolbar on the shortcut menu.Under Customize Quick Access Toolbar, click the command you want to move, and then click the Move Up or Move Down arrow.Group the commands by adding a separator between the commandsYou can group the commands by using the separator to make the Quick Access Toolbar appear to have sections.Right-click the Quick Access Toolbar, and then click Customize the Quick Access Toolbar on the shortcut menu.In the Choose commands from list, click Popular Commands.Click <Separator>, and then click Add.To place the separator where you want it, click the Move Up or Move Down arrow.Move the Quick Access Toolbar3684270284480The Quick Access Toolbar can be located in one of two places:Upper-left corner next to the icon for a Microsoft Office program, for example, next to the Word icon . (default location) Below the ribbon.If you don't want the Quick Access Toolbar to be displayed in its current location, you can move it to the other location. If you find that the default location next to the program icon is too far from your work area to be convenient, you might want to move it closer to your work area. The location below the ribbon encroaches on the work area. Therefore, if you want to maximize the work area, you might want to keep the Quick Access Toolbar in its default location.Click Customize Quick Access Toolbar .In the list, click Show Below the Ribbon or Show Above the Ribbon.Customize the Quick Access Toolbar by using the Options commandYou can add, remove, and change the order of the commands on the Quick Access Toolbar by using the Options command.Click the File tab.Under Help, click Options.Click Quick Access Toolbar.Make the changes you want.Reset the Quick Access Toolbar to the default settingsRight-click the Quick Access Toolbar, and then click Customize the Quick Access Toolbar on the shortcut menu.In the Customize the Quick Access Toolbar window, click Reset Defaults, and then click Reset only Quick Access Toolbar.TEMPLATE A template that can use to create other workbooks, a template file (.xltx) can include data and formatting.Open the workbook that you want to use as a template.Click the Microsoft Office Button , and then click Save As.In the File name box, type the name that you want to use for the template.In the Save as type box, click Excel Template,Click Save.Use a template to create a new workbookClick the Microsoft Office Button , and then click New.Under Templates, do one of the following:Recently Used TemplatesInstalled TemplatesMy TemplatesNew from ExistingData manipulationa) Select Data To select a cell or data to be copied or cut: ? Click the cell? Click and drag the cursor to select many cells in a rangeClick and drag the cursor to select many cells in a rangeSelect a Row or Column; To select a row or column click on the row or column header.4848225144145Copy and PasteTo copy and paste data: Select the cell(s) that you wish to copy On the Clipboard group of the Home tab, click CopySelect the cell(s) where you would like to copy the data 494411092710On the Clipboard group of the Home tab, click Paste4445000-61595Cut and Paste To cut and paste data:Select the cell(s) that you wish to copy On the Clipboard group of the Home tab, click CutSelect the cell(s) where you would like to copy the data On the Clipboard group of the Home tab, click PastePaste Special when copying from ExcelUse the Paste Special dialog to copy complex items from a Microsoft Office Excel worksheet and paste them into the same worksheet or another Excel worksheet using only specific attributes of the copied data, or a mathematical operation that you want to apply to the copied data.PasteAll ????Pastes all cell contents and formatting of the copied data.Formulas ????Pastes only the formulas of the copied data as entered in the formula bar.Values ????Pastes only the values of the copied data as displayed in the cells.Formats ????Pastes only cell formatting of the copied ments ????Pastes only comments attached to the copied cell.Validation ????Pastes data validation rules for the copied cells to the paste area.All using Source theme ????Pastes all cell contents in the document theme formatting that is applied to the copied data.All except borders ????Pastes all cell contents and formatting applied to the copied cell except borders.Column widths ???Pastes the width of one copied column or range of columns to another column or range of columns.Formulas and number formats ????Pastes only formulas and all number formatting options from the copied cells.Values and number formats ????Pastes only values and all number formatting options from the copied cells.OperationSpecify which mathematical operation, if any, that you want to apply to the copied data.None????Specifies that no mathematical operation will be applied to the copied data.Add????Specifies that the copied data will be added to the data in the destination cell or range of cells.Subtract????Specifies that the copied data will be subtracted from the data in the destination cell or range of cells.Multiply????Specifies that the copied data will be multiplied with the data in the destination cell or range of cells.Divide????Specifies that the copied data will be divided by the data in the destination cell or range of cells.Skip blanks ????Avoids replacing values in your paste area when blank cells occur in the copy area when you select this check box.Transpose ????Changes columns of copied data to rows and vice versa when you select this check box.Paste Link ????Links the pasted data on the active worksheet to the copied data. This example illustrates the various paste options in Excel. Cell B5 below contains the SUM function which calculates the sum of the range B2:B4. Furthermore, we changed the background color of this cell to yellow and added borders.PasteThe Paste option pastes everything.1. Select cell B5, right click, and then click Copy (or press CTRL + c).2. Next, select cell F5, right click, and then click Paste under 'Paste Options:' (or press CTRL + v).Result.ValuesThe Values option pastes the result of the formula.1. Select cell B5, right click, and then click Copy (or press CTRL + c).2. Next, select cell D5, right click, and then click Values under 'Paste Options:'Result.Note: to quickly replace the formula in cell B5 with its own result, select cell B5, press F2 (to edit the formula) and press F9.FormulasThe Formulas option only pastes the formula.1. Select cell B5, right click, and then click Copy (or press CTRL + c).2. Next, select cell F5, right click, and then click Formulas under 'Paste Options:'Result.FormattingThe Formatting option only pastes the formatting.1. Select cell B5, right click, and then click Copy (or press CTRL + c).2. Next, select cell D5, right click, and then click Formatting under 'Paste Options:'Result.Note: the Format Painter copy/pastes formatting even quicker.Paste SpecialThe Paste Special dialog box offers many more paste options. To launch the Paste Special dialog box, execute the following steps.1. Select cell B5, right click, and then click Copy (or press CTRL + c).2. Next, select cell D5, right click, and then click Paste Special.The Paste Special dialog box appears.EXERCISE 2STUDENT MARKSCREATE AN EXCEL SHEET FOR STUDENT MARKS.PROCEDURE:OPEN MS-EXCELStart buttonAll programsMS-OfficeMS-ExcelCreate a table with the following detail Roll no., SUB1, SUB2,--------SUB6, TOTAL,PERCENTAGE,,FAIL(OR)PASSValidate the 6 subjects MARKS DataData toolsData validationGo to settings in allows to select any one the whole number(min 0 and max 100)Calculate total and percentageH3=SUM (B3:G3) and copy the formula for remaining cells to find totalI3=H3/6 and copy the formula for remaining cells to find percentageFind the whether student is pass(or)fail = If (or (B3<50,C3<50,D3<50,E3<50,f3<50,G3<50),”FAIL” ,”PASS”)Find the highest mark in the each subjectFormulasFunction library Auto sumMaximumB13 =MAX(B3:B12) ; copy formula to find max for all subjectsDraw column chart for the highest marks in each subjectTo draw the line chartInsertChartColumn chartDraw the pie chart for the pass(or)failTo draw the pie chart InsertChartpie chartOUTPUT:EXERCISE 3STUDENT FORMCREATE A STUDENT FORM AND APPLY DATA VALIDATION AND PROTECT CELL PROCEDURE:Open Excel and rename it as Student FormEnter data into the sheet as given in the ExerciseApply data validation to each cell depending its valueDATA DATA TOOLSDATA VALIDATIONSETTINGSName: Allow text length –specify min and max number of charactersRoll No: Allow text length –specify min and max number of charactersBranch: allow list-BTech,MBAMobile number : allow numbers onlyProtect the cells that should not be modified by the userSelect the cells for which to provide protectionHomeCellsFormat Lock cells and then HomeCellsFormat Protect Cells and enter password and save the document so that the cell will be protectedOUTPUT: EXERCISE 4A.COLLECT HIGHEST MARKS OF 6 SUBJECTS & DRAW COLUMN CHART FOR EACH SECTION & MULTIPLE COLUMN CHART.B. COLLECT THE INFORMATION RELATED TO GRADES OF STUDENT IN A CLASS & DRAW PIE CHART FOR THE ABOVE INFORMATION.PROCEDURE:Collect highest marks in each subject for two sections.Enter the above values in excel sheet A2:C10.Select cell B2:B10.Insert Charts Column chart.Move chart to new sheetRepresent data labels & outside end Layout labelsdata labelsoutside endCreate column chart for section B by using the same above procedureCompare the marks of two sections using multiple column chartSelect cell B2:C10.Insert Charts Cluster Column chart.Move the chart to new sheetCollect grades & enter the above information in excel from A12:B17Create pie chart from the above information Select cells A12:B17Insert chartspie chartMove the chart into new sheet Select the chart from layoutLabelsdata labelsselect any one % more data labels optionsvalue , %Represent the data value at outside endLayoutlabelsdata labelsoutside endRepresent % for each section Layoutlabelsdata labelsmore data labels option check % & select new line as a separator.OUTPUT:20320211455310197519685EXERCISE 5SCATTER PLOTCREATE A SCATTER PLOT FOR THE FOLLOWING DATAPROCEDURE:Open a new excel sheet and save it as SCATTER PLOT.Rename sheet1 as SCATTEREnter the data into the sheetSelect the data C3:D24Insert scatter plot. Insert-Charts-Scatter PlotMove the chart to a new sheetApply required changes to the graphOUTPUT:EXERCISE 6M?CROSOFT EXCEL EXERC?SEOpen a blank workbookWrite the following entries into the specified cells:C2 : Annual Fruit SalesB3 : 1999A4 : AppleF3 : TotalC3 : 2000A5 : OrangeG3 : AverageD3 : 2001A6 : BananaE3 : 2002B4 : 1000B5 : 2300B6 : 500C4 : 1250C5 : 2500C6 : 300D4 : 800D5 : 1200D6 : 600E4 : 1300E5 : 1450E6 : 250Merge the cells from A2 to G2. Apply the following changes to the title line:Change the horizontal and vertical text alignments as center.Change the row height of row 2 as 25.Change the font, font size, font style and font color as Tahoma, 18, bold, blue.Using range selection, select the cells from B3 to G3. Then press Ctrl key on the keyboard and select the cells from A4 to A6. (this way you can select multiple cells on different parts of the worksheet) Now change the font, font size and font style of the selected cells as Times New Roman, 12, bold-italic and change the horizontal text alignment of these cells as left with indent value 1.Using the AutoSum feature, find and write the sum of cells from B4 to E4 into cell F4. Then copy this cell to F5 and F6. (Hold the right bottom of F4 and drag it to F5 and F6) Observe that the formulas are updated when copied.Using the Average function find and write the average sales of apple over years into cell G4. (Excel will suggest a range automatically to you, however this range will be wrong. So you will have to select the range yourself) Using the same method in the previous question, copy this cell to G5 and G6.Add a comment to G4. Write the text “Average sales over years” into the comment box.Select the cells from B4 to G6. Change format of the selected cells to Currency with Symbol TL and 3 decimal places.Change the column width of Column A such that all the texts on this column can fit into the cells. Select the columns from B to G and apply AutoFit Selection for these columns. Add a new row above the 3rd row (that is between rows 2 and 3). Add outside and inside borders to your table. For the ouside border choose a thicker line. (To add borders, select the range where you want to add borders (that is from A2 to G7), then choose Format Cells from the shortcut menu and click the Border tab) Select the title (select the merged cell in row 2) and change the cell color to yellow. (To change cell color, from the shortcut menu select Format Cells, then click Pattern tab) Select the cells B4 to G4 and A5 to A7 (Use Ctrl key). Change the cell color as pink. Select the cells from B5 to G7. Change the cell color as light blue. Using Print Preview feature, observe how your document would appear on a printed sheet. Add one of the automatic headers to the header. Add the page number to the footer of the document. Change the page orientation as Landscape. (Use the setup menu in the Print Preview mode to add header&footer and change page orientation) Write the following entries into the specified cells:A11 : 2002 StatusB10 : AppleC10 : OrangeD10 : Banana Click on the cell B11. Click on the Paste Function button (or the small arrow at right of AutoSum button) and find If function. Write the necessary entries to test whether the value of E5 (2002 sales of apple) is larger than G5 (Average sales of apple over years). If the value of E5 is larger, then “OK” should be written to B11. If it is smaller, “NOT OK” should be written. Apply similar operations for 2002 sales of orange and banana. (that is for E6 – G6 and E7 – G7 pairs) Select the cells from B5 to G7. Click on the Chart Wizard button. Select Column chart type and the first chart sub-types. Write “Annual Fruit Sales” as the Chart title, “Year” as the Category (X) axis, “Value” as the Category (Y) axis. Insert the chart as an object into Sheet 1. Place the chart on an empty place of the Sheet 1. Move the legend to the bottom of the chart. (Right-click on the legend area and choose Format Legend, then select Placement) Change the pattern of the Chart Area. Select any of the patterns you like from the Texture patterns. (Right-click on the Chart area, choose Format Chart Area, then click on Fill Effects and select Texture tab) OUTPUT: EXERCISE 7EXCEL FINANCIAL FUNCTIONS1. Calculate the compound interest for Rs 250 at interest rate 8% per annum for 4 years. Note : Use FV function in excelOpen a new excel sheet and save it as Finance.Rename sheet1 as compound interestEnter the data as followsApply function FV at c9.=FV(interest_rate,number_payments,payment,PV,Type)whereinterest rate= 8% per annumnumber_payments =4payment=0PV= -250. The PV is negative in the Excel function since we have deposited Rs 250 (cash out flow).type=1 . The payment is made at the beginning of the period.C9=FV(C2,C3,,C5,1)Interpretation: The future value of Rs 250 invested for 4 years at 8% per annum is Rs 340.122. Calculate the FV of investment where you deposit Rs 5000 into savings account that earns 7.5 % annually. You are going to deposit Rs 250 at the beginning of the month EXCEL FUNCTION:=FV(interest_rate, number_payments, Payment, PV,type) whereinterest rate= 7.5/12 % per month. Since each payment is made monthlynumber_payments =2*12 months. Since 12 payments per year over 2 yearspayment= -Rs 250. Payment of Rs 250 at the beginning of each month (cash out flow)PV= -Rs 5000. The PV is negative in the Excel function since we have deposited Rs5000 (cash out flow).type=1 . The payment is made at the beginning of the period.INTERPRETATION: The future value of an investment of Rs 5000 invested at 7.5% with Rs250 deposited at the beginning of the month for 2 years is Rs12298.46 3. Suppose we deposit Rs 20000 at the beginning of a year at 5% p.a compound. We withdraw Rs 2000 at the end of each year. What would be the sum available after 4 years? Excel can be used to solve this problem by making use of the FV function. EXCEL FUNCTION:=FV(interest_rate, number_payments, Payment, PV, type) whereinterest rate= 5% per annumnumber_payments = 4 yearspayment= 2000. The value is + since we remove Rs 2000 at the end of the year (cash inflow)PV= -Rs 20000. The PV is negative in the Excel function since we have deposited Rs20000 (cash out flow).type=0 . The payment is made at the beginning of the period.INTERPRETATION: The sum after 4 years will be Rs 15689.884. Suppose a machine is expected to last 8 years and its replacement price is estimated at Rs 5000. What annual provision must be made to ensure sufficient funds are available if money can be invested at 8% per annum? Excel can be used to solve this problem by making use of PMT functionEXCEL FUNCTION:==PMT(interest_rate, number_payments,pv,fv,type) whereinterest rate= 8% per annumnumber_payments =8 yearsPV= 0.The PV is zero in the Excel function since we start with zero initial investment.FV=-Rs5000 .The FV is negative in the Excel function since we will remove the Rs 5000 ( Cash outflow)type=0 . The payment is made at the beginning of the period. INTERPRETATION: Annual deposit Rs 470.07 will need to be made at the end of the year for 8 years 5. Create loan amortization table for a loan amount of Rs50000 at annual rate of 18% for 2 years.(Payment is made at each month)Open a new excel sheetRename sheet1 as loan amortizationEnter the values as follows Enter Payment no from 1 to 24 Calculate made at first month- Use PMT functionB8=PMT ($B$2,$B$3*$B$4,$B$5)Copy the formula for remaining months Calculate principle – Use PPMT functionC8= PPMT($B$2,A8,24,$B$5)Copy the formula for remaining months Calculate interest – Use IPMT functionD8 =IPMT($B$2,A8,24,$B$5)Copy the formula for remaining monthsCalculate principal balanceE8=$B$5+C8E9= E8+C9 – Copy the formula for remaining cells.OUTPUT:EXERCISE 8PIVOT TABLE CREATE A PIVOT TABLE FOR THE FOLLOWING DATA.a) A pivot table is a way to extract data from a long list of information and present it in a readable form.PROCEDURE:Click on a cell in the data table. Click on the?Insert?menu and click the?PivotTable?button:A dialog box will appear. The?Table/Range?value will automatically reflect the data in the table (we can click in the field to change the Table/Range value if Excel guessed wrong). Alternatively, you can choose an?external data source?such as a database.To?create the layout of your PivotTable, first select the fields that are required in the table, and then place them in the correct location in the field layout area.Drag and drop each field to the area you want it to be.NAME and DEPARTMENT – row labelsMONTH- column labelsDEPARTMENT BUDGET- valuesThe PivotTable report that is generated from different selectionsThe pivot chart is almost the same technique, but here select chart option rather than table optionOUTPUT:b) Create a pivot table for the sales data of January and February which contains 688 rows. Note: Download the Data sheet (Sales data of 688 rows) Generate a report for total number of items sold by each salesperson on 2nd January, 3rd January and 4th January OUTPUT:EXERCISE 9MEAN, MEDIAN, MODE, STANDARD DEVIATION, PERCENTILECalculate Mean, Median, Mode, Standard Deviation, Percentile for marks obtained by ten students in a Subject303212580645PROCEDURE:Open a new excel sheet and save it as MEANMEDIAN.Rename sheet1 as STATFUNCTIONSEnter the data as shown in adjacent tableCalculate Mean, Median, Mode, Standard Deviation, Percentile as follows by selecting appropriate Function from Formulas-Function Library- More Functions-Statistical Mean (Average)C13= AVERAGE (C3:C12)MedianC14=MEDIAN (C3:C12)Mode15=MODE (C3:C14)Range=max value-min valueC16= =MAX (C3:C12)-MIN (C3:C12) Standard deviationC17 =STDEV (C3:C12) 25th PercentileC18 =PERCENTILE (C3:C12, 0.25)50th PercentileC19 =PERCENTILE (C3:C12, 0.5)100th PercentileC20 =PERCENTILE (C3:C12, )Calculate Percentile of Each studentD3= =PERCENTRANK(C$3: C$12, C3, 1)*100 Copy the formula for the cells D4:D12OUTPUT:EXERCISE 10PROBABILITY DISTRIBUTION1. A manufacturing firm quality assures components manufactured and historically the length of a tube is found to be normally distributed with the population mean of 100 cm and a standard deviation of 5 cm. Calculate the probability that a random sample of one tube will have a length ( X ) of at least 110cms. Also calculate the probability that X lies between 85 and 105 cm.PROCEDURE:Open a new excel sheet and save it as NORMDIST.Rename sheet1 as NORMDISTRIBUTIONEnter the given data as followsP(X<=110) C10 = NORMDIST (C6, C4, C5, TRUE)P(X>=110)= 1- C10 P(85<=X<=105) = P(X<=105) - P(X<=85)P(X<=85) C14= NORMDIST (C7, C4, C5, TRUE)P(X<=105) C16= NORMDIST (C8, C4, C5, TRUE)P(85<=X<=105) =C16-C14OUTPUT:Interpretation: We observe that the probability that an individual tube length is at least 110 cm is 0.02275 or 2.3 % The probability that an individual tube length lies between 85 and 105 cm is 0.839995 or 84 %2. A local authority surveyed the travel preferences of people who travelled to work by train or bus. The initial analysis suggested that 1 in 5 people travelled by train to work. If 5 people are interviewed what is the probability that:a) Exactly 3 prefer travelling by trainb) 3 or more prefer travelling by trainc) Less than 3 prefer travelling by trainNote: This can be modeled by Binomial DistributionPROCEDURE:Open a new excel sheet and save it as BINOMDIST.Rename sheet1 as BINOMDISTRIBUTIONEnter the given data as followsCalculate probability of P(X=r) for r=0, 1, 2, 3, 4, 5D8 = BINOMDIST (C8, $C$3, $C$4, FALSE) and copy the formula for cells D9:D13Total =SUM(D8:D13)a) P(X=3)D16 =BINOMDIST (C16, $C$3, $C$4, FALSE)b) P(X>=3) =1-P(X<=2)D17 =1-BINOMDIST (C17, $C$3, $C$4, TRUE)c) P(X<3)=P(X<=2)D18 =BINOMDIST (C18, $C$3, $C$4, TRUE)OUTPUT: ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download