Fin 370 final exam | Business & Finance homework help

1) The goal of the firm should be
2) An example of a primary market transaction is
3) According to the agency problem, _________ represent the principals of a corporation.
4) Which of the following is a principle of basic financial management?
5) Another name for the acid test ratio is the
6) The accounting rate of return on stockholders’ investments is measured by
7) If you are an investor, which of the following would you prefer?
8) The primary purpose of a cash budget is to
9) Which of the following is a non-cash expense?
10) The break-even model enables the manager of a firm to
11) A zero-coupon bond
12) If you have $20,000 in an account earning 8% annually, what constant amount could you withdraw each year and have nothing remaining at the end of 5 years?
13) At what rate must $400 be compounded annually for it to grow to $716.40 in 10 years?
14) The present value of a single future sum
15) Which of the following is considered to be a spontaneous source of financing?
16) Compute the payback period for a project with the following cash flows, if the company’s discount rate is 12%. Initial outlay = $450 Cash flows:Year 1 = $325 Year 2 = $65Year 3 = $100
17) For the NPV criteria, a project is acceptable if the NPV is __________, while for the profitability index, a project is acceptable if the profitability index is __________.
18) Which of the following is considered to be a deficiency of the IRR?
19) The firm should accept independent projects if
20) The most expensive source of capital is
21) The cost associated with each additional dollar of financing for investment projects is
22) The XYZ Company is planning a $50 million expansion. The expansion is to be financed by selling $20 million in new debt and $30 million in new common stock. The before-tax required rate of return on debt is 9%, and the required rate of return on equity is 14%. If the company is in the 40% tax bracket, what is the marginal cost of capital?
23) Shawhan Supply plans to maintain its optimal capital structure of 30% debt, 20% preferred stock, and 50% common stock far into the future. The required return on each component is: debt–10%; preferred stock–11%; and common stock–18%. Assuming a 40% marginal tax rate, what after-tax rate of return must Shawhan Supply earn on its investments if the value of the firm is to remain unchanged?
24) Lever Brothers has a debt ratio (debt to assets) of 40%. Management is wondering if its current capital structure is too conservative. Lever Brothers’ present EBIT is $3 million, and profits available to common shareholders are $1,560,000, with 342,857 shares of common stock outstanding. If the firm were to instead have a debt ratio of 60%, additional interest expense would cause profits available to stockholders to decline to $1,440,000, but only 228,571 common shares would be outstanding. What is the difference in EPS at a debt ratio of 60% versus 40%?
25) Zybeck Corp. projects operating income of $4 million next year. The firm’s income tax rate is 40%. Zybeck presently has 750,000 shares of common stock which have a market value of $10 per share, no preferred stock, and no debt. The firm is considering two alternatives to finance a new product: (a) the issuance of $6 million of 10% bonds, or (b) the issuance of 60,000 new shares of common stock. If Zybeck issues common stock this year, what will be the projected EPS next year?A. $4.94B. $2.96C. $5.33D. $3.20
26) _________ risk is generally considered only a paper gain or loss.A. TransactionB. TranslationC. EconomicD. Financial
27) Capital markets in foreign countriesA. offer lower returns than those obtainable in the domestic capital marketsB. provide international diversificationC. in general are becoming less integrated due to the widespread availability of interest rate and currency swapsD. have been getting smaller in the past decade
28) Buying and selling in more than one market to make a riskless profit is calledA. profit maximizationB. arbitrageC. international tradingD. an efficient market
29) What keeps foreign exchange quotes in two different countries in line with each other?A. Cross ratesB. Forward ratesC. ArbitrageD. Spot rates
30) One reason for international investment is to reduceA. portfolio riskB. price-earnings (P/E) ratiosC. advantages in a foreign countryD. exchange rate risk
 

Children and adolescents’ health and transition to practice (due 48

 
APA format
1) Minimum 5 pages  (No word count per page)- Follow the 3 x 3 rule: minimum three paragraphs per page
You must strictly comply with the number of paragraphs requested per page.
           Part 1: minimum  1 page
           Part 2: minimum  3 pages
           Part 3: minimum  1 page
         
   Submit 1 document per part
2)¨******APA norms
         All paragraphs must be narrative and cited in the text- each paragraph
         Bulleted responses are not accepted
         Don’t write in the first person 
         Don’t copy and paste the questions.
         Answer the question objectively, do not make introductions to your answers, answer it when you start the paragraph
Submit 1 document per part
3)****************************** It will be verified by Turnitin (Identify the percentage of exact match of writing with any other resource on the internet and academic sources, including universities and data banks) 
********************************It will be verified by SafeAssign (Identify the percentage of similarity of writing with any other resource on the internet and academic sources, including universities and data banks)
4) Minimum 3 references (APA format) per part not older than 5 years  (Journals, books) (No websites)
Part 2: Minimum  5 references (APA format) per part not older than 5 years  (Journals, books) (No websites)
All references must be consistent with the topic-purpose-focus of the parts. Different references are not allowed.
5) Identify your answer with the numbers, according to the question. Start your answer on the same line, not the next
Example:
Q 1. Nursing is XXXXX
Q 2. Health is XXXX
6) You must name the files according to the part you are answering: 
Example:
Part 1.doc 
Part 2.doc
__________________________________________________________________________________
Part 1: Children and adolescents’ Health
 
Topic: Collaboration in Pediatric Primary Healthcare
1. Describe an experience that you have encountered so far in your pediatric clinical rotation involving collaboration. 
2. Describe if in your experience the collaboration resulted in improved patient outcomes. 
3. In your experience, what barriers to collaboration have you observed? 
4. Why, in your opinion, effective collaboration is essential in providing quality healthcare
Part 2: Children and adolescents’ Health
 
Topic: Collaboration in Pediatric Primary Healthcare
This assignm3nt is specific to a particular pediatric primary care practice and is mostly based upon student observation and experience. 
 From your observations and experiences in your pediatric clinical rotation:
1. Provide an example of multidisciplinary collaboration (1 paragraph).
2. Provide an example of how multidisciplinary and family collaboration affects patient outcomes (2 paragraphs).
4. Discuss a particular case where collaboration among healthcare professionals and family members affected patient/family satisfaction (2 paragraphs) .
a. Patients
b. Family/caregivers.
4. Discuss a particular case where collaboration among healthcare professionals and family members affected patient/family satisfaction  (2 paragraphs).
5. In your clinical practicum. What are some barriers you have observed to collaboration among health care professionals, patients, and families? (2 paragraphs)
Part 3: Transition to practice
  
Topic: Utilizing Evidence Practice Guidelines
One of the ways that the NP can manage risk of being successfully sued for malpractice or negligence is by utilizing evidence practice guidelines. 
1. Explain how these guidelines affect the “standard of care”
2. Discuss how utilizing this guideline can be a major strategy to significantly reduce risk to the provider.

Exercise 3-creating a portfolio | Business & Finance homework help

Creating a portfolio (port.xls): In this exercise, you are given actual stock price data from an actual portfolio. [I used to update these data every semester, but I’d usually make a little error or two in the update, so you get an old data set that I’m fairly confident is clean.] You are to use the raw pps data (tier1) to generate market values (tier2) and daily returns (tier3). The three tiers together make a portfolio spread sheet that we’ll call port.xls. The daily returns data will be used in subsequent exercises to illus trate portfolio management concepts. Note: Prices have been adjusted for stock splits. Di vidends are ignored. Market data (last column) is the S&P500 IndexThere are a couple of old screen shots (videos called Tier2 and Tier3) posted to the Moodle web site. You might find these helpful in visualizing the operations that you are being asked to per form, but be aware that the data sets in the screen shots are different than your data set.A sample structure of port.xls can be seen in sample3. Your port.xls will be much larger than the sample. The old symbol for market data shown in sample3 is ^SPX whereas the current symbol for the S&P500 is ^GSPC.
1. Open the Excel spreadsheet file called “portmaster.xls”.2. Save a copy to your local drive. This first block of data (approximately 250 trading days times 30 firms) is called “tier1”. The data comes from finance.yahoo.com, Historical Prices. Hint: As you build your formulas, you might try testing them in “sample3” to see if they work (i.e. see if you get the same results as in the sample)3. Build a second block of data (tier2, market values) directly and immediately below tier1. Start by copying all the dates from tier1 into tier2 [I now notice that this has already been done for you]. Next, calculate the market value (pps x number of shares) for every day for every firm. Only do this for the firms (not for the total portfolio or for the market index). Your formula should look roughly like “=C$3*C4” where C3 is the “no of shares” and “C4 is the “pps”. The dollar sign ($) in C$3 will hold the row constant at row 3 as you copy the formula down and across for every day and every firm.4. Next, calculate the market value of the portfolio (col. B) = the sum of all the stocks’ market values. Note: The S&P500 data [^GSPC] should be copied from tier1 into tier2. This market data is being shown for comparison and is not part of the portfolio.5. Build a third block of data (tier3, daily returns) directly and immediately below tier2. Again, make sure that you have the dates in Column A. Calculate the daily returns for every stock for every day (except for the first day). Use the basic model of (new-old)/old. [What happens if you use new-old/old?]. You cannot calculate a return for Day1 because you don’t have the data for Day0. The first daily return is on should be in Day2 and the formula should be =(day2 daily re turn minus day1 daily return) divided by (day1 daily return). But in your formula, use cell desig nations. Write the formula once, then copy it to all days and all firms. Also, calculate these dailyreturns for the portfolio and for the S&P500 index (this needn’t be an extra step – you can calcu late daily returns for the portfolio, all the firms, and the market all in one copy- formula opera tion.
5) Calculate Standard Deviation using the Excel function =STDEVP on the daily returns for each stock, for the portfolio, and for the S&P 500. Place these data in a row under tier3 (see the sample3 for an example).6) Calculate total annual returns by using “(lastday marketvalue – firstday marketvalue)/firstday marketvalue” to calculate total annual returns for each stock, for the portfolio, and for the S&P 500. Place these data immediately under the standard deviation data. In the sample3 the total re turns are only for six days. In your port.xls, the total returns are for a year.7) Send your port.xls through Moodle2 “port.xls” assignment link, the same way you’ve been sending previous assignments. Remember- accompanying comments are appreciated.

Mgt 421 – 2 | Management homework help

  
Part 1: Case Analysis 
Read the discussion forum below, which shows several employees’ posts about how to develop a better performance review system. Then, answer the questions that follow. 
Jackson: Hi team, this week we’ll use this forum to discuss our performance review system. Specifically, we want to explore the benefits and drawbacks about the timing of our reviews. Currently, our reviews are conducted annually. The HR team wants to know what we think about quarterly reviews. The HR team wants us to share our views with them by September 10.
Cynthia: I’ve never experienced quarterly reviews before, so it’s hard for me to say if that’s better. My hunch is quarterly reviews would be more fair and helpful. But, I also assume they take a lot of extra time for our managers. @Erin I think your last company held quarterly reviews. What are your experiences with more frequent reviews? Has anyone else worked at a company with more frequent reviews? Tell us what it was like.
Erin: @Cynthia your assumptions match my experience. I enjoyed getting more frequent feedback. But, when I was a manager, it was a crushing responsibility because the company expected me to conduct the reviews without lightening my load elsewhere. @Nigel thanks for sharing your experiences with monthly reviews. You mentioned the reviews are more like check-ins that don’t require so much time by the managers. Totally makes sense to me. @Vilma also think your comments about adding peer reviews makes a lot of sense and relieves managers.
Nigel: Hi all, there’s one discussion point I hope we can meet in person (or by online conference) to discuss. I appreciate the HR team wants our input about the frequency of reviews, but I’m worried that we’re not addressing one of the problems: perceived fairness of the reviews. I believe in 15 minutes, we could work out a few common concerns to share with the HR group from our whole team.
Jackson: Thanks for your comments, team. Overall, here are what I think are the key take-aways: (a) we want more feedback, so quarterly reviews may be helpful; (b) we don’t want to create an unreasonable burden on managers; and (c) we want more conversation around objectivity and fairness of the reviews. At this point, I propose I draft our comments into a short report for your review. I also suggest we set up a one-hour
  
  
Part 2: Writing Exercise 
Anne Jenkins is a finance manager for J.L.N. Used Cars (JLN), a used-car business with about 30 dealerships. She is in charge of running the company’s financing program. She sets terms for loans provided through the dealerships. She also manages a team that works directly with dealerships to ensure that loans are promoted and granted appropriately. On Anne to-do list for the day, she has several writing tasks. 
Anne needs to be efficient with her writing thus she hires you to help her writing in one of the following options:  

Forum respones | Social Science homework help

Hello everyone! Last week and forum of the course!
     Child and Family Specialists will face a number of issues as families and our world continue to change. I think of of the most pressing issues will be the multiple meanings of family. Powell (2007) presents that different government entities, employers, religions, and culture have different meanings of what family means. Families today are very diverse between family size, forms, ages, and roles (Powell 2007). Many families are beginning childless families and many families are no longer nuclear. Changing family structures (such as cohabitation, single parent families or blended families) could all influence child and family specialist services and support they offer to families. Now that same-sex marriage has been legalized child and family specialist may experience families of same-sex couples. Another factor may be that individuals consider their family those individuals that they choose or family of choice (Powell 2007). Family of choice may include individuals that are not biological or through a commitment. The Pew Research Center (2015) presents that the family is changing in a variety of ways including more mothers in the workforce, older parents, more educated parents, grandparents raising grandchildren, and changes in fertility. The diversity of families will continue to change and family specialists should be prepared for diversity in families and changes that may occur.
    Differing meaning of families can have an impact on how child and family professionals handle services and resources for families and children. The different definition of families may impact service eligibility or certain resources that are available to families.The differing family meanings could have impacts on family roles, family management, parenting styles, and communication. Child and family specialists need to be prepared for differing meaning of family so that they can address family needs appropriately. As the world changes so will families and specialists should be able to adjust and be flexible in the differing views and definitions of family. Child and family specialists should be ready to offer diverse support and services to a number of different families.
Staci
References
Pew Research Center. (Dec. 2015). Parenting in America. Retrieved from http://www.pewsocialtrends.org/2015/12/17/1-the-american-family-today/
Powell, L.H. & Cassidy, D. (2007). Family Life Education: Working with Families Across the Life Span (2nd Ed.). Long Grove, IL: Waveland Press.
 
second response:
Forum 8 Future Issues
 
 

While I am sure there will be many issues arising over the next ten years, probably some we haven’t even thought of yet, one that I think will be pressing is the increasing reliance on technology. Technological advances will certainly be helpful as the field moves forward, but I think there will also be hindrances as a result of the proliferating use of technology, especially the internet. As stated in Powell & Cassidy (2007), “technology has enabled every-one to access information (both good and questionable) with ease” (p. 20). On one hand the ease of access allows people to find out more about child and family development on their own. On the other hand, it also allows people to find a lot of misinformation which can lead to complications in the work of child and family development specialists. I imagine that many professionals in this field spend a lot of time helping people relearn information that they thought they knew because they had the wrong information, and technology allows such misinformation to spread farther and wider, adding to the work that these professionals have to do. In addition, as children and teens use technology more and more, the need for regulating this use is ever-increasing (Berk, 2009). Of course this is something that parents are primarily responsible for, but child and family development specialists will likely have some role in it, especially when it comes to safety concerns. I am certainly not saying that technology is a bad thing. We must move forward and adapt to the changes around us, and technology certainly allows us to do many things that we couldn’t do without it; none of us would be in this class without the technological advances that have occurred over the last few decades. However, I do think that as professionals we will need to be increasingly cautious when working with children and families as technology and access to information continues to advance.
References
Berk, L. E. (2009). Child Development (9 ed.). Needham Heights, MA: Allyn and Bacon/Longman Publishers.
Powell, L. H., & Cassidy, D. (2007). Family Life Education (2nd ed.). Long Grove, IL: Waveband Press, Inc.
 

8 paper on homelessness | Social Science homework help

Consider from the authors of the course text the following quote:  “Progressive social change is about defending the weak from the strong, gaining resources to reduce human hardship, and striving to promote an equitable society.  It is about battling for economic and social justice, working to protect and enhance civil liberties, and respecting the environment” (Rubin & Rubin, 2008, p. 433).
Begin your paper with a discussion of community or progressive organizing, as you understand it at this point in the course.  Review your areas of strength as an organizer.
Considering the above quote, select a social problem or cause that you could address as a social change agent.  Explain what motivates you to support this cause.  Some suggestions/examples are:
 
   Homelessness
 
The list could go on and on. Consider movies such as Erin Brockovich, Legally Blonde 2, Patch Adams, or others where someone felt strongly about a social cause, or saw a need, mobilized support, and worked to effect change.  What cause might you take on?
Describe the process you would go through to mobilize others to collectively enact changes.  Some of the questions to be answered might be:

What are your goals?  What would you like to achieve?
What role do you see yourself taking in this cause?
How will you mobilize others to help you?
Where will you obtain funding for your cause?
What coalitions, local and state, would you contact who might support you in your cause?
What governmental policies would you need to have changed?
What specific skills or tools would you need to develop in order to accomplish your goals? What would be your best approach to this particular problem?
What government agencies or representatives would you need to approach? Research and name specific members of government who might support you in your legislative district.
What roadblocks or challenges might you anticipate that could hinder your success or progress?

Conclude your paper with a discussion about how you would evaluate and reflect upon your progress, review your original goals and evaluate how you are meeting your goals and if they have remained current and relevant to your area of interest or concern.  Understanding that change takes time, how will you know that you are on track and making progress?  Summarize what you hope to accomplish and any insights you might have gained in this project. 
 
Writing the Final Paper

Must be eight to ten double-spaced pages in length and formatted according to APA style as outlined in the approved APA style guide.
Must include a title page with the following:

Title of paper
Student’s name
Course name and number
Instructor’s name
Date submitted

Must include an introductory paragraph with a succinct thesis statement.
Must address the topic of the paper with critical thought.
Must conclude with a restatement of the thesis and a conclusion paragraph.
Must use at least seven scholarly sources, including a minimum of three from the Ashford University Library.
Must use APA style as outlined in the Ashford Writing Center.  
Must include, on the final page, a reference page that is formatted according to APA style as outlined in the approved APA style guide.

Emerging areas of human health

  
Case Report: Cumulative
You will be creating a case report in stages over four course topics. This assignment will add to your previous work in Topic 5. Use an example from your own personal practice, experience, or own personal/family; however, simulated cases are not acceptable for practice hours and therefore not acceptable for this assignment. Examples might include a patient with Duchesne’s muscular dystrophy. Huntington’s disease, Down’s syndrome, sickle cell anemia, BRCA 1 or BRCA 2 mutations, or other genetic disorder that you and/or the organization you practice in may specialize in treating.
General Requirements:
Use the following information to ensure successful completion of the assignment:
· This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.
· Doctoral learners are required to use APA style for their writing assignments. 
· This assignment requires that at least three additional scholarly research sources related to this topic, and at least one in-text citation from each source be included.
· You are required to submit this assignment to LopesWrite. 
Include the following:
· Guidelines and reasons behind the FDA regulations for introducing new pharmaceutical agents (policy).
· The role that money and grants play in scientific advances/the economics of health care (capitalism).
· The role and involvement family plays into the health care decision.
Directions:
For this assignment (Conclusion of the Case Report), include previous Parts 1, 2, and 3 of the Case Report in one document, combined with additional genetics information learned from the assigned readings from all course topics. This final Case Report document should include the following:
1. Describe the disease, its prevalence, its incidence and general knowledge of the disease.
2. Discuss the laboratory testing that can be done.
3. Describe if chromosomal analysis is/was indicated, and detail the chromosomal change that caused the disease if it is a chromosomal disorder.
4. Describe the disorder in terms of its origin as either a single gene inheritance, or as a complex inheritance and considerations for practice and patient education.
5. Describe the gene mutation of the disease, as well as whether it is acquired or inherited, and how the mutation occurs.
6. Examine how genetics can influence policy issues.
7. Discuss any nutritional influences for this disease.
8. Process of nutritional assessment and counseling as it relates to health, prevention, screening, diagnostics, prognostics, selection of treatment, and monitoring of treatment effectiveness.
Continue your paper with 1,000-1,250 words which include the following:
1. Discuss any ethical considerations for this disease.
2. Compare how genetics can improve care and health outcomes while reducing cost to usual practices.
3. Discuss the changes in approaches to care when new evidence warrants evaluation of other options for improving outcomes or decreasing adverse events.
4. Create a plan for how you might educate colleagues and/or patients on this genetic disorder.
You are required to complete your assignment using real-world application. Real-world application requires the use of evidence-based data, contemporary theories, and concepts presented in the course. The culmination of your assignment must present a viable application in a current practice setting. 

Current issues in the behavioral healthcare system

In the course project assignments of the past four weeks, you have written on various topics dealing with trends and issues in the behavioral healthcare system. Those weekly project assignments can be used as references for writing your final research paper.
In addition, read and incorporate the following articles from the EBSCO host database into your paper:

Deakin, A. (2004, November). Finding your organization’s hidden        treasure. Behavioral Health Management, 24(6), 27-29.
Droppa, D., & Luczak, R. (2004, January). Collaboration, technology,         and outcomes—A recipe to improve service delivery.                Behavioral Health Management, 24(1), 41-44.

To complete the research paper, you will need to include an introduction and conclusion section as well as a title page and reference section. The title of the research paper will be the Current Issues in the Behavioral Healthcare System.
Your final paper is due for submission. The paper should adhere to the following guidelines:

The length of the paper should be eight to ten double-spaced pages (not including the title and reference pages).
The main sections should have a:

Title page
Introduction
Body of the paper (with subheadings)
Conclusion
Reference page(s)

The paper must use the APA format for citing sources and references.

Your final paper introduction (one page) should include the following points:

An overview of the research paper
The purpose or objective of the research paper

The body of the paper (five to six pages) should address each of the following topics using information learned in the course, in combination with outside references:

Based on your previous assignments and review of the literature, what are some of the major issues faced by today’s behavioral healthcare system? How have the current and future trends that are evolving in the industry addressed some of those issues?
Do you think there is a difference between the changing trends taking place in the private sector and that of public behavioral healthcare inpatient facilities? Based on your understanding about behavioral health services and the populations being served by them, do you agree that both private and public organizations are able to provide the necessary clinical services? Provide a rationale in support of your response.
In behavioral healthcare, outcomes are the established norm for measuring the success or lack of services. What are some of the major challenges in collecting the data needed to support and report behavioral health outcomes? Provide a rationale for your response.
Quality of care and services is an important part of an outcome-based strategy. The objective behind maintaining and improving quality is to provide competent and efficient services to consumers. In your opinion, do the current regulatory and accreditation standards for the behavioral health industry help to meet that objective? How?

Your conclusion (one to two pages) should include the following points:

What conclusions can you draw from your research that would demonstrate the role played by behavioral health in the healthcare industry?
What changes would you like to bring to today’s behavioral healthcare system in order to resolve the current issues identified?
Based on your literature review, if you were asked to conduct a formal research project on any of the topics discussed in the research paper, what would your choice be and why?

Exp22_excel_ch02_ml1 – katherine’s coffee shop weekly payroll

Exp22_Excel_Ch02_ML1 – Katherine’s Coffee Shop Weekly Payroll
Exp22 Excel Ch02 ML1 – Katherine’s Coffee Shop Weekly Payroll
Excel Chapter 2 Mid-Level 1 – Payroll 
  
Project Description:
You have been hired as the new manager of Katherine’s Coffee shop. As part of your responsibilities, you will be calculating the weekly payroll. The previous manager developed a partial worksheet, but you will enter the formulas to calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay. In addition, you want to include total pay columns, calculate some basic statistics, and create an employee lookup section. As you construct formulas, make sure you use absolute, relative, and mixed cell references correctly in formulas.
     
Start Excel. Download and open   the file named Exp22_Excel_Ch02_ML1_Payroll.xlsx.   Grader has automatically added your last name to the beginning of the   filename. 
 
Use IF functions to calculate   the regular pay and overtime pay in columns E and F based on a regular   40-hour workweek. Be sure to use the appropriate absolute or mixed cell   references. Pay overtime only for overtime hours. Note employees receive 1.5   their hourly wage for overtime hours worked. Calculate the gross pay in cell   G5 based on the regular and overtime pay. Spencer’s regular pay is $440. With   five overtime hours, Spencer’s overtime pay is $82.50.
 
Create a formula in cell H5 to   calculate the taxable pay. Multiply the number of dependents by the deduction   per dependent and subtract that from the gross pay. With two dependents,   Spencer’s taxable pay is $422.50.
 
Insert a VLOOKUP function in   cell I5 to identify and calculate the federal withholding tax. With a taxable   pay of $422.50, Spencer’s tax rate is 25% and the withholding tax is $105.63.   The VLOOKUP function returns the applicable tax rate, which you must then   multiply by the taxable pay.
 
Calculate FICA in cell J5 based   on gross pay and the FICA rate, and calculate the net pay in cell K5. Copy   all formulas down their respective columns. Be sure to preserve the existing   formatting in the document. Based on the hours Spencer worked he paid $39.97   to FICA and had a weekly net pay of $376.90
 
Use Quick Analysis tools to   calculate the total regular pay, overtime pay, gross pay, taxable pay,   withholding tax, FICA, and net pay on row 17. (On a Mac, this step must be   completed using the AutoSum feature on the ribbon.)
 
Apply Accounting Number Format   to the range C5:C16. Apply Accounting Number Format to the first row of   monetary data and to the total row. Apply the Comma style to the monetary   values for the other employees. 
 
Insert appropriate functions to   calculate the average, highest, and lowest values in the Summary Statistics   area (the range I24:K26) of the worksheet. Format the # of hours calculations   as Number format with one decimal and the remaining calculations with   Accounting Number Format.
 
Use the XLOOKUP function to look   up the employee name in cell A20 (Wagner) in the payroll data and return the   specified information in row 20. Ensure the return array includes overtime   pay, gross pay, taxable pay, federal tax, FICA and net pay. 
 
Save and close the workbook.   Submit the file as directed. 

Exp22_excel_ch02_ml2 – vacation property 1.1

Exp22_Excel_Ch02_ML2 – Vacation Property 1.1
Exp22 Excel Ch02 ML2 – Vacation Property 1.1
Excel Chapter 2 Mid-Level 2 – Vacation Property 
  
Project Description:
You have been hired as a finance manager at Granger vacation homes, a regional construction company that finances and builds vacation properties. As part of your responsibilities you manage mortgage applications. Your predecessor left a partially created document you will use to calculate various details about each property being financed. You will use the VLOOKUP function to help determine mortgage interest rates, IF statements to calculate down payment, various statistical calculations, and XLOOKUP to create a quick search within the document.
     
Start Excel. Download and open   the file named Exp22_Excel_Ch02_ML2_Vacation   Property.xlsx. Grader has automatically added your last name to the   beginning of the filename. 
 
Write an IF function in cell D9   that calculates the down payment for the mortgage. The down payment is 15% of   the purchase price if the customer is considered high risk or 5% of the   purchase price if considered low risk. The criteria to assess the risk of the   loan is based on the credit score value in cell B6. Be sure to use the appropriate   absolute or mixed cell references when referencing the input values in the   range B4:B6. Then use the fill handle to copy the formula down, stopping in   cell D28.
 
Enter a formula to calculate the   percent financed in cell E9. The percent financed is the amount   financed/purchase price. Next use the fill handle to copy the formula down,   stopping cell E28.
 
Enter a formula in cell F9 to   calculate the amount financed. The amount financed is the purchase price-down   payment. After entering the formula, use the fill handle to copy the formula   down, stopping in cell F28.
 
Use the VLOOKUP function in cell   G9 to lookup the mortgage rate based on the customer credit score and the   data in the range D4:E6. Be sure to use the appropriate absolute or mixed   cell references. Use the fill handle to copy the formula down, stopping in   cell G28.
 
Calculate the rate per period in   cell H9. All mortgages being financed will be paid monthly. After completing   the formula, use the fill handle to copy the formula down, stopping in cell   H28.
 
Calculate the total number of   payment periods in cell J9 based on the years financed in cell I9. After   completing the formula, use the fill handle to copy the formula down,   stopping in cell J28.
 
Use the PMT function to   calculate the total monthly payment in cell K10 based on the periodic   interest rate, number of periods, and amount financed. Ensure the results are   a positive value then use the fill handle to copy the function, down stopping   in cell K28.
 
Use Quick Analysis to calculate   the total of all payments in cell K29. (Quick Analysis is not available for   Mac. If using a Mac, use AutoSum to calculate the SUM of the range K9:K28).
 
Use the appropriate statistical   functions in the range B32:B36 to calculate descriptive statistics based on   the row headings in the range A32:B36. In cell B32, calculate the total count   of loans based on column A, lowest payment in cell B33 based on the data in   column K, highest payment in B34, average payment in B35, and median payment   in B36. 
 
Use the XLOOKUP function in cell   E32 to lookup the employee number in cell D32 and return the corresponding   down payment, % finances, and amount financed. 
 
Use the appropriate function to   insert the current date and time in cell B2.
 
Save and close the workbook.   Submit the file as directed.