CSMFO



InstructionsAll calculations must be done using formulas or functions.Do not use manually typed in values within formulas or functions.Example: =SUM(A1:A3)+42.83These instructions have been provided to you in hard copy and can also be found in the Excel Test.xls workbook on the Instructions worksheet.Read each problem carefully and perform the tasks in each section. Good Luck!!Open Excel Test.xls.PayrollGo to the Payroll worksheet.Enter your full name in cell A1.Insert a new employee in row 13. Enter the following data points for this new employee:Employee is Jones, PeggyAptitude Test Scoreis 85Pay Rate is 23.5Hours are 35Each employee is given a weekly aptitude test, a score of 80 or above on this test entitles them to a bonus calculated as follows: bonus percentage times gross pay. A score below 80 yields a bonus of 0. Calculate the bonuses for each employee in the Bonus column. Compute the Taxable Income (Gross Pay plus Bonus) for each employee.Calculate the Average of each column.Format values as needed.Savings PlanGo to the Savings Plan worksheet.Cell reference your name in cell A1.ArtSoft employees may participate in a company savings plan which requires the employee to contribute $200 monthly for 5 years. The annual interest rate earned is 5%. Enter the monthly interest rate for the plan.Enter the total monthly contribution the employee must make.Determine the future value of this investment. Format values as needed.SalesGo to the Sales worksheet.Cell reference your name in cell plete columns Total and Average.Create a line graph comparing the sales for each city in the first quarterconstruct a line graph of sales by month, with one line for each city, with the following features:Place the graph below the Net Sales tableMake a legend to designate which line color refers to which city.Enter “ArtSoft First Quarter Sales” as the Chart Title.Enter “Month” as the x-axis label and “Sales (Millions)” as the y-axis label.Change the scale of the y axis to make 30 the minimum valueFormat values as needed. FilterGo to the Filter worksheet.Cell reference your name in cell A1.Freeze panes so column headings remain visible.Filter the data to find all employees whohave recently been promoted, anddo not belong to the union, and earn between $45,000 and $75,000 (inclusive). Sort the filtered data in descending order by salary. LookupGo to the Lookup worksheet.Cell reference your name in cell A1.Make the Widget World Raise %s table a named range.Using a formula or function, bring in the following values from the Filer worksheet: Salary and Years with WidgetUsing the named range you created above and the Salary values, calculate the Raise % and Raise $ for each employee Format values as needed.WineGo to the Wine worksheet.Cell reference your name in cell A1.Calculate the Total Revenue using Bottles Sold, Price per Bottle, less Discount.Calculate the Subtotal, Tax, and Total.Create a pie chart to the right of the table with a pie wedge for each Winery and the size of the pie wedge based on the Number of Bottles Sold.Format values as needed.DataGo to the Data worksheet.Cell reference your name in cell A1.Place a Filter on the column headings.Sort the data by the column Credits, large to small values.Format the Credit and Debit columns as comma and two decimal places.Filter the BAI Type Code for numbers greater than 500.Adjust all columns’ width as neededInsert a worksheet and name it Data Results.Cell reference your name in cell A1.Create numbers one through three beginning in cell A3 and ending 2 rows below.Input a formula on the Answers sheet next to number one which counts how many transactions have a 0 Day Flt Amt greater than 0. Input a formula on the Answers sheet next to number two which calculates the sum of 1 Day Flt Amt and 2 Day Flt AmtInput a formula on the Answers sheet next to number three which calculates the sum of one and two day float for all BAI Types less than 500Format values as needed.MortgageGo to the Mortgage worksheet.Cell reference your name in cell A1.Harold bought a house and his first mortgage payment is due August 1, 2007. Harold's mortgage payment is due on the 1st of every month. The monthly principal amount is $100.00. The annual interest rate is 5.0% and the monthly interest is calculated using a 30/360 year. The principal balance of the mortgage loan on July 31st, 2007 is $350,000.00.Input the loan variables from the above sentence into the column Loan Variable according to the description in the Loan Item column.Calculate the following:August 1, 2007 Monthly Principal DueAugust 1, 2007 Monthly Interest DueAugust 1, 2007 Mortgage Payment DueSeptember 1, 2007 Principal Balance OutstandingNumber of years to pay off Principal Balance assuming Harold pays exactly $100 per month towards PrincipalThe monthly mortgage payment if Harold wanted to pay off his loan in 30 yearsWhich is the higher monthly mortgage payment?$100 per month in principal plus interest, ORPaying off the loan in 30 yearsWhich paydown scenario is less costly over the life of the loan?$100 per month in principal plus interest, ORPaying off the loan in 30 yearsFormat values as needed.Save As and Exit The FileSave As the file to the desktop with the following nomenclature:Your full name, no spaces, all lower case, ExcelTestExample: snowwhiteExcelTest.xls ................
................

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

Google Online Preview   Download