This Excel project will help you review your skills and develop a toolkit to use in your future busi

HIRE QUALIFIED ACADEMIC WRITERS 

This Excel project will helpyou review your skills and develop a toolkit to use in your future businesscourses.Create an Excel workbook named YourNameExcelToolkit.xls.Name the sheet tabs Documentation,Stocks, Savings, Loan and Majors.Sheet: StocksOn December 31, 2010 youpurchased 100 shares each of Apple, EMC Corporation, Deckersand Verizon.Go to.finance.yahoo.com/”>http://www.finance.yahoo.com, search for each stock and then go to theHistorical Prices. Determine how much you paid for each stockon December31, 2010. Use the adjusted closingprice.Use the Historical Prices under.finance.yahoo.com/”>http://www.finance.yahoo.com to determine how much each stock was worth on September1, 2011 using the adjusted closing price for that day.Document your findings on the Stocks sheet. This area should be labeled Conditions/DataEntry.See the sample below.Conditions/Data EntryCompanySymbolNumber of SharesPurchase Price 12/31/2010Price 9/1/2011AppleEMC CorporationDeckersVerizonSet up a separate area on the stocks sheet to evaluate the value ofthe portfolio on 9/1/11. This area shouldbe labeled Financial Analysis.Seethe sample below.It must be allformulas, no constants!Symbol, Shares,Purchase Price and 9/1 Priceare formulas that referencethe Data Entry Area.Total Purchase Price is a simple multiplication formula.% Allocation is a simple division formula: Total Purchase Price/Grand Totalfor Total Purchase Price (Use anabsolute reference to Grand Total PurchasePrice.).9/1 Value is a simple multiplication formula: 9/1 Price * Shares.9/1 % Allocation is a simple division formula: 9/1 Value /Grand Total for 9/1 Value (Use an absolutereference to 9/1 Value Grand Total.).Gain/Loss is a simple subtraction formula: 9/1 Value – Total Purchase Price.%Gain/Loss is a simple division formula: Gain/Loss / Total Purchase Price.Autofill all the way tobottom row.Financial AnalysisSymbolSharesPurchasePriceTotalPurchase Price% allocation9/1Price9/1Value9/1% AllocationGain /Loss%Gain / LossGRAND TOTALApply currency and percentformattingwith 2 decimal places in the appropriate cells.Create a pie chart showing% Allocation at purchase. Include aTitle, legend and data labels.Create a pie chart showing% Allocation on 9/1/11. Include a Title, legend and data labels.Sheet: SavingsAt Age 19 you open a savings account and thendeposit $1,000every year. The annual interest rate is 4.0%. Calculate how much you will haveat age 40.Set up theConditions/Data Entry area.See the sample below.Conditions/Data EntryStarting Age:19Annual Deposit:$1,000Interest Rate:4.0%Set up the FinancialAnalysis Area and fill in the first row using formulas.See thesample below.Age = absolute cell address of Starting AgeDeposit = absolute cell address of Annual DepositBeginning of Year Balance = cell address of the currentdeposit amount, i.e. the cell to the leftInterest Earned = Beginning of Year Balance *absolute cell address of InterestRateEnd of Year Balance = Beginningof Year Balance + InterestEarnedFinancial AnalysisYearAgeDepositBeginningof Year BalanceInterestEarnedEndof Year Balance119$1,000$1,000$40$1,040Complete the remainingrows of the Financial Analysis Area.Year: Fill in the second year and then autofilldown to 22years.Age = cell address of previous cell + 1.Autofill the columndown to age 40.Deposit:Autofillfrom the previous cell.Beginning of Year Balance = cell address of current depositamount + cell address of previous year’s end of year balance.Autofillthis even though it won’t be correct until you finish the entire row.Interest Earned:Autofill from the previous celleven though itwon’t be correct until you finish the entire row.End of Year Balance:Autofill from the previous cell.Locate the row thatcontains your balance at age 40.What is your balance at age 40? Enter it in cell G3.Change the conditions inthe Data Entry Area to start at age 22.Locate the row that contains your balance at age 40. What is your balance at age 40? Enter it in cell G4.Sheet: LoanTo get through 4 years of college you take $50,000 in loans.These carry a 6.625% fixed interest rate over fifteen years. Calculate themonthly paymentfor the loan. Create an amortization table the lists the period,beginning balance, principal payment, interest payment and ending balance foreach period of the loan.Set up the Conditions/DataEntry Area.See the sample below.Conditions/Data EntryLoan Amount:$50,000Interst Rate:6.625%Term (Years):15Periods per Year:12Use the PMT function tocalculate the monthly payment. Useall absolute cell addresses in the formula, no constants.Modify thefunction to make the result a positive number.Monthly Payment:Set up the first row of FinancialAnalysis Area/Amortization Schedule.Seethe sample below.Period: 1Beginning Balance: = celladdress of Loan AmountPrincipal Payment = PPMTfunction·Rate = absolute cell addresses of Interest Rate/ Periods per Year·Per = cell address of current period·Nper = absolute cell addresses of Term * Periodsper Year·Pv = negative, absolute cell address of LoanAmountInterest Payment = IPMTfunction, using same arguments as PPMT functionEnding Balance =Beginning Balance – Principal PaymentAmortization SchedulePeriodBeginning BalancePrincipal PaymentInterest PaymentEnding Balance1$50,000$162.96$276.04$49,837.04Complete the remainingrows of the Amortization Schedule to show all 15 years (180) of payments.Period: Fill in the 2ndperiod cell and then autofill the column.Beginning Balance = celladdress of ending balance of previous period. Autofill this even though it won’t becorrect until you finish the entire row.Principal Payment,Interest Payment, Ending Balance: Autofill from previous cells.Calculate a total for the amountof interest you will pay.Typethis amount in cell F3.Change the Interest Ratein cell B3 to 4.25%. Now, what isthe total amount of interest you will pay?Type this amountin cell F4.Sheet: MajorsCreate a sheet of demographic data of 250 students and theirmajors at Suffolk University. Create a pivot table from the data.Create 2 tables as showbelow.Student Years1FR2SO3JR4SRMajors1Accounting2Information Systems3Entrepreneurship4Finance5Global Business6Management7Marketing8Public AdministrationSet up the Conditions/DataEntry Area.·Create three columns: Student ID, Student Year, andMajor.·Autofill the Student ID column with consecutivenumbers 1-250.·Randomly assign a Student Year (1-4) to all 250students using the RANDBETWEEN function.Note: Your numbers will bedifferent than the sample below.·Randomly assign a Major (1-8) to all 250students using the RANDBETWEEN function.Note: Your numbers will bedifferent than the sample below.Student IDStudent YearMajor123217322Create 3 new columns,Student ID, Student Year, and Major.This area will display the text equivalent of the Student Year andMajor.Copy the student ids intothe new Student ID column.Use the VLOOKUP functionto fill the Student Year column with the correct text abbreviation.Lookup value = celladdress of the cell containing the number of the student year.Table array = absoluteaddress of cell range of Student Years table, not including the heading.Col index num = 2Use the VLOOKUP functionto fill the Major column with the text equivalent of the major.Lookup value = celladdress of the cell containing the number of the major.Table array = absoluteaddress of cell range of Majors table, not including the heading.Col index num = 2Create a pivot table fromthe new table. Place the Pivottable on the same sheet.Column Labels: Major, RowLabels: Student Year, Values: Countof Student IDAll SheetsGo back to each sheet and add your own personal formattinglike colors, borders and font effects.DocumentationFill in the Documentation sheet with appropriateinformation.