EXCEL

pdf

School

University Of Georgia *

*We aren’t endorsed by this school

Course

2090

Subject

Finance

Date

Jan 9, 2024

Type

pdf

Pages

Uploaded by BarristerPrairieDog3932

Day 1 Keyboard shortcuts Select an attire column without using the mouse - command D Control 1 gets you into format cells Double click the corner of the box for it to drag down and do the same for the other columns Day 2 What is the total of all of the prices? =sum(I2:I1256) What is the average? =average(I2:I1256) What is the max? =max(I2:I1256) What is the min? The average price of a birkin bag in ONE particular shape. Conditional function called averageif Type =averageif Look at the bar below =averageif(c2:c1256,"birkin", I2:I256) The C is the shape range so you type “birkin” because that is the shape you are looking for And then you put I2:I256 because that is the price we are looking for The max price of birkin bags of 30 centimeters You can't do maxif only a maxifs =maxifs(J9I2:I1256,C2:C1256, "birkin", D2:D1256, 30) Average of a Kelly S in a 25 centimeter bag =AVERAGEIFS(I2:I1256,C2:C1256, "Kelly S", D2:D1256, 35) To get your answer to accounting form: Right click and hit format cells Count the number of bags that are hima bags (count if) =countif(G2:G1256,”hima).. 106 Average cost =averageif(G2:G1256),”hima”,I2:I1256)... 154,098.39 Average size =averageif(G2:G1256, “hima”, D2:d1256) …29.22 Average price of croc bad =averageif(E2:E1256, Using a wildcard symbol, type croc and * =averageif(E2:E1256,”croc*”, And then add price =averageif(E2:E1256,”croc*”, I2:I1256) …. 62,157.59 Average price of kelly bag which includes both R and S

=averageif(C2:C1256, "Kell*", I2:I1256) Average price of brown kelly bag either R or S as long as it is brown =AVERAGEIFS(I2:I1256, G2:G1256,"brown", C2:C1256, "Kelly*") … 28,455.04 TOY SHEET What will the customers pay? =B3*(1+B1) You have to make it an absolute cell reference Hit F4 and autofill EXCEL CHECK 1 What is the average hammer price of Birkin shaped bags in the data set? =averageif(c2:c1256,”birkin”, i2:i126) How many bags in the dataset was sold in christies auction =countif(b2:b1256,”christies”) What is the total cost of all croc bags in the dataset =sumif(e2:e1256,”*roco*”,i2:i1256) EXCEL CHECK 2 What is the average price of red Birkin bags with gold hardware? (do not include brushed gold) What is the total price of all kelly bags sold by sothebys? Create a column that categorizes bags <26 cm as “small” , 26-34 cm as “med” , and >34 cm as large. What is the average price of a medium bag? Find the accumulated value of 1,000 after 5.5 years at 6.2 per annum compound interest =fv(6.2%,5.25,0,-1000) 1,371.37 Suppose you save 10,000 per year for your retirement. Assuming a 7% annual return (compounded yearly), how much money will you have saved after 40 years? =fv(7%,40,-10000) 1,996,351.12 I currently have 42,000 saved in my retirement account. My employer-sponsored retirement plan pays me 2,250 every month, which is deposited into the same retirement account. If i am

currently 30 years old and i plan to retire at age 65, what will the future value of my retirement account be after 35 years? Assuming a 7.5% annual return, compounded monthly =FV(7.5%/12,35*12,-2250,-42000) 5,144,289.54 I would like to save 3,600 per year for the next 8 years. If i earn 6% on my savings, compounded annually, will i be able to afford a 35,500 down payment on my first house Answer We are looking for a future value Rate is 6%, 8 years, negative because we are taking it out of my pocket to put it in the bank =fv(6%,8,-3600) $35,630 The estimated cost of law school for a Georgia resident is 39,716 per year. My parents are writing me a check for 119,148 to cover the three year program OR deposit that amount in an account for me to access at my retirement, 45 years from today. If I take the cash, what is the future value of their gift, assuming a 6.5% APR interest rate (compounded monthly)? Answer Looking for a future value. The rate compounded monthly, so you divide by 12, 45 years multiply by 12, payment is 0 =fv(6.5%/12,45*12,0, -119148) I need to buy a new car. I am selecting between a 2022 Honda Civic, which starts at $22,350 and a 2022 BMW 330i, which starts at $41,450. If I decide to buy the Honda, I will put the $19,100 I save into an account for my retirement. If my retirement is in 30 years, what is the future value of that savings? Assume a 5% APR interest rate, compounded monthly =FV(5%/12,30*12,0,-19100) I have good news! I just won the lottery!!! The game I won pays out $5,000 per month for the next 20 years. I can take the payments as a lump sum of $750,000. Given a rate of 4.5% compounded monthly, what is the present value of my annuity? Based on value alone, should I take the lump sum? =PV(4.5%/12,20*12,-5000) I want to get rich, so I will stop buying a dang latte every day for the four years that I'm in college and, instead, put my money in the bank. My latte costs me $3.25 per day and I will be putting that money in an investment account that earns 7% APR, compounded daily. What is the future value of my investment? How much total interest have I earned? =FV(7%/365,4*365,-3.25) =*the number you got for the first part minus - (3.25*365*4)

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

I would like to buy a 2022 Mazda MX-5 Miata roadster at a cost of $27,650. I make a down payment of 20% of the car's value as a down payment. If I am taking out a five year loan at 2.79% for the remainder, what are my monthly payments? Answer: =PMT(2.79%/12,5*12, 80%*27650) I would like to buy a house. If I need to save $17,250 for my initial down payment (5% of the home's value), will I be able to do so if I save $500 per month for three years, assuming I earn 4.5% APR interest, compounded monthly? answer: =FV(4.5%/12,3*12,-500) I have a 30 year loan with a $1,350 monthly payment. The total amount of the loan is $327,750. What is my APR? Answer: =RATE(30*12,-1350, 327750)*12 =PMT (7.74%/12,30*12,327750) Personal finance experts suggest that homeowners should create a cash reserve of 3% of their home's value for emergency home repairs. Given that my home is worth $345,000, I have created a cash reserve of $10,350. Assume I put this money into an account earning 6.5% APR, compounded monthly. What is the value of my cash reserve after six years? Answer: =FV(6.5%/12,6*12,0,-10350) I need to expand my production line at the factory. I am going to do so by buying a machine that costs me $1,750,000 today. I can use it for five years before retiring it. It will return me $100,000 in year 1, $300,000 in year 2, $500,000 in year 3, $700,000 in year 4, and $900,000 in year five. What is the NPV of my project, given a 10% rate? Answer: =NPV(10%,100000,300000,500000,700000,900000) =NPV(10%,100000,300000,500000,700000,900000)-1750000 I borrow $300,000 to start a new business in Athens, GA from my friend Mr. K. Smart. He charges me 3% APR interest, compounded monthly and says that I can take as long as I need to pay it back. How many months will it take me to pay back his loan if I pay him $1,500/month? Answer: =NPER(3%/12,-1500,300000) I buy a 2020 Fiat 124 Spider for $26,840. I borrow the entire amount at 4.1% APR, compounded monthly. How many months long is the term of my loan if I am making payments of $495.52 per month? Answer:

=NPER(4.1%/12,-495.52,26840) I take out a mortgage loan of $327,750 at 2.59% APR, compounded monthly. How many years long is the term of my loan if I am making payments of $1,751.17 per month? Answer: =NPER(2.59%/12,-1751.17,327750)/12 LOOKUP FUNCTIONS Vlookup goes down and across columns =vlookup(B3,click the whole table and press F4, 3) =xlookup(B3,left column on table, right column on table) Data validations Part number: Hlookup goes across and down rows =hlookup(b16, table arway, 2) You can’t slide it over to fill them in because the 2 needs to become a 3 and a 4 and you need to make them absolute cell references Y