Excel sheets for each scenario. 25tips
2. identify a target firm B (Exxon Mobile in this case) that would enhance the value of firm A, if firm A would acquire B; and estimate what the value of firm A would be if it acquired B. The key to the second part of the project is to justify all assumptions the student makes to modify the forecast due to the acquisition.
Assignment: Looking Ahead: Application Assignment Net Revenue and Budgeting
You will begin this assignment in Week 4 and it will be due by Day 7 of Week 5.
No one can predict the future, but accountants and financial managers must try and do exactly that! By examining net revenue, costs, and cash flow, you can get a clearer picture of what to expect in your organization’s (or one with which you are familiar) fiscal future. Using these metrics to look forward will enable you to more effectively plan budgets that accomplish organizational goals.
When developing a budget, what variables do you have to take into account? In health care organizations, two of the largest groups of factors that you must consider are first, volume, and second, staffing and supply. The number of patients and tests performed each day, as well as employees and their pay rates are all crucial pieces of information when determining a budget.
In this Assignment, you address five scenarios: net revenue, fixed and variable costs, cash flow, volume budget, and staffing and supplies budget.
Note: For those Assignments in this course that require you to perform calculations you must:
A title and reference page are NOT needed in this assignment. Put your name and assignment at the top of the Excel spreadsheet.
For those not comfortable with the use of Microsoft Excel, this week’s Optional Resources suggest several tutorials.
To prepare:
Note: All the scenarios will be submitted as one document. Each scenario will be on a different tab in the spreadsheet.
Scenario 1: Net Revenue Scenario
Your clinic provides four kinds of services:
Question: The profile of your patients is such that the average collection rate is 75%. Assuming you have 100 visits of each type each month, what amount of new revenue will you generate in the next 12 months?
Scenario 2: Fixed/Variable Cost Scenario
You have performed a cost analysis of your health care organization and have determined the following: based on the latest three years of information, your annual cost of operations is $1,600,000 with annual volume of 10,000 procedures. You have determined that certain of your supply items are fixed in nature (those marked with an F) while others are variable (marked with a V).
Question: An insurance company that is considering directing its 1,000 units per year of procedure business to your organization has approached you. For the last three years, you have been charging a price of $165 per procedure (with a 100% collection rate). Your board has mandated that you make $5 of profit from each of the procedures. You obviously want the highest possible price, but as you enter the negotiations, what is the lowest possible price you would be willing to accept from this payer?
Hint: Calculate the variable cost.
Scenario 3: Cash Flow Scenario
Your new business venture will begin operation on July 1, 20X2. You will hire staff effective January 1, 20X2 with a cost of $40,000 per month. You know from experience that collections lag billing by 3 months (in other words, once you bill for a service, you must wait 90 days for the payment to be received.) Your business volume is projected to be as follows:
Question: If you have $380,000 of cash on hand on January 1, 20X2, how much cash will you have at the end of June 20X3? Assume a 100% collection rate.
Scenario 4: Volume Budget Scenario
You manage lab services in a large hospital. You have the following data on both the hospital’s budgeted patient days and visits for budget year 20XX along with the ratio of lab tests to patient days or visits.
Question: Based on this raw data provided , how many lab tests would you anticipate for the coming budget year? If each test is priced at $20.00, how much gross revenue would you budget? Assuming each full-time lab technician (FTE) can perform 200,000 tests each year, how many full-time lab technicians would you plan for?
Example on Template: 2 North Bldg calculated. You will need to complete 2 South Bldg, ICU and OPD
Scenario 5: Staffing and Supply Budget Scenario
Calculate the supplies budget necessary to operate your unit for the fiscal year beginning January 1, 20X8. It is your expectation that you will perform 24,820 procedures in the budget year. The following spending data is available for the period January 1 to March 31, 20X7 during which time procedure volume amounted to 3,240. Items marked (F) are considered fixed, those marked (V) are considered variable. Inflation is planned at 4%.
In reviewing performance to date, you note that in January, you purchased $150,000 of D5W fluid replacement charged to IV solutions, which represents an entire year’s supply. In addition, you returned $2,800 of office supplies for credit from the vendor in Febuary. These supplies were purchased in a previous fiscal year.
You also need to prepare the salary budget for the same fiscal year. You have determined that staff needs are for 6.5 FTEs.
A pay raise will be given to all staff on October 1st of each year at a rate of 8 percent. In making your calculations, always round to the nearest whole dollar for annual salary amounts, but keep pennies in the hourly pay rates. New staff begins the new fiscal year at $16.00 per hour.
This Assignment will be due by Day 7 of Week 5. Be sure and include all of your calculations.
Excel Project InstructionsAssume ABC Company has asked you to not only prepare their 2013 year-end Balance Sheet but to also provide pro-forma financial statements for 2014. In addition, they have asked you to evaluate their company based on the pro-forma statements with regard to ratios. They also want you to evaluate 3 projects they are considering. Their information is as follows:End of the year information:Account12/31/13Ending BalanceCash160,000Accounts Receivable126,000Inventory75,200Equipment745,000Accumulated Depreciation292,460Accounts Payable36,900Short-term Notes Payable18,300Long-term Notes Payable157,225Common Stock450,000Retained EarningssolveAdditional Information:Sales for December total 12,000 units. Each month’s sales are expected to exceed the prior month’s results by 5%. The product’s selling price is $15 per unit.Company policy calls for a given month’s ending inventory to equal 80% of the next month’s expected unit sales. The December 31 2012 inventory is 9,400 units, which complies with the policy. The purchase price is $8 per unit.Sales representatives’ commissions are 10.0% of sales and are paid in the month of the sales. The sales manager’s monthly salary will be $3,500 in January and $4,000 per month thereafter.Monthly general and administrative expenses include $8,000 administrative salaries, $5,000 depreciation, and 0.9% monthly interest on the long-term note payable.The company expects 30% of sales to be for cash and the remaining 70% on credit. Receivables are collected in full in the month following the sale (none is collected in the month of sale).All merchandise purchases are on credit, and no payables arise from any other transactions. One month’s purchases are fully paid in the next month.The minimum ending cash balance for all months is $140,000. If necessary, the company borrows enough cash using a short-term note to reach the minimum. Short-term notes require an interest payment of 1% at each month-end (before any repayment). If the ending cash balance exceeds the minimum, the excess will be applied to repaying the short-term notes payable balance.Dividends of $100,000 are to be declared and paid in February.No cash payments for income taxes are to be made during the first calendar quarter. Income taxes will be assessed at 35% in the quarter.Equipment purchases of $55,000 are scheduled for March.ABC Company’s management is also considering 3 new projects consisting of the purchase of new equipment. The company has limited resources, and may not be able to complete make all 3 purchases. The information is as follows for the purchases below.Project 1Project 2Project 3Purchase Price$50,000$75,000$32,500Required Rate of Return12%8%10%Time Period3 years5 years2 yearsCash Flows – Year 1$18,000$25,000$20,000Cash Flows – Year 2$22,000$20,000$18,000Cash Flows – Year 3$22,000$18,000N/ACash Flows – Year 4N/A$16,500N/ACash Flows – Year 5N/A$15,000N/ARequired Action:Part A:Prepare the year-end balance sheet for 2013. Be sure to use proper headings.Prepare budgets such that the pro-forma financial statements may be prepared.Sales budget, including budgeted sales for April.Purchases budget, the budgeted cost of goods sold for each month and quarter, and the cost of the March 31 budgeted inventory.Selling expense budget.General and administrative expense budget.Expected cash receipts from customers and the expected March 31 balance of accounts receivable.Expected cash payments for purchases and the expected March 31 balance of accounts payable.Cash budget.Budgeted income statement.Budgeted statement of retained earnings.Budgeted balance sheet.Part B:Calculate using Excel formulas, the NPV of each of the 3 projects.It is possible that ABC Company may not be able to complete all 3 projects. Therefore, advise ABC Company as to the order in which they should pursue the projects (i.e., which project should ABC Company attempt to do first, second, and last).Provide justification and analysis as to why you chose the order you did. The analysis must also be done in Excel, not in a separate document.This assignment must be submitted as 1 Excel document.
Good morning,
I do need help with this assignment. I am having especially troubles with creating the pivot table.
Thank you
Name: Here Class/Section: Here Project: Here Date Due: Here YearQuarter2016 Q12016 Q12016 Q12016 Q12016 Q22016 Q22016 Q22016 Q22016 Q32016 Q32016 Q32016 Q32016 Q42016 Q42016 Q42016…
EXCELSIOR COLLEGE:BNS301: National Security Ethics and Diversity
M6D1: Loyalty and Whistleblowing
Read:
View:
Discuss:
This activity addresses module outcomes 1 & 2. Upon completion of this activity, you will be able to:
The purpose of this activity is to examine the concept of loyalty and its possible extreme of whistleblowing. Can you be both loyal to your organization while also being a whistleblower? This can be a difficult ethical decision as on one hand you need to do what’s right but on the other, being a whistleblower can put you at odds with your co-workers, your boss, or even the United States government. Think through what loyalty means to you and what does it take to be a whistleblower.
Questions:
Based on the readings and activities for this module, answer the following questions:
Excellent Health Plan Member Name: Veronica Casto Member ID: MKJ538721P Prescription Group No: W32187 RX Group: 321543 Generic RX $15 Preferred Name brand RX $50 Nonpreferred Name brand RX $70Unknown State University Prime Care Excellence – HMO Date Issued 04/01/XXXX In-Network Member Responsibility Primary $25.00 Specialist $50.00 PT/OT/SP $35.00 BH, SU $100.00 Urgent Care $50.00 ED $150.00
Veronica had bronchitis for the past eight days. Feeling miserable, she went to the local hospital’s Emergency Department for care. She received a prescription for antibiotics. What co-payment should the hospital collect?
$50.00
$100.00
$150.00
Cannot be determined
Describe how you would approach customization of the project management toolbox, including project management tools, to suit your organizational project management environment in the case study, Excellence on a Global Scale.
Excellence in business communication – Writing and Completing Business Messages
1. Identify a website, advertisement, or piece of mail you received lately that uses obsolete language, buzzwords you don’t understand, or esoteric language. Rewrite the piece using plain language and other writing cues from Chapter 5. Include the original text in your submission.
2. Identify a company that has been in the media in the last 12 months due to a controversy. Find a communication from the company and identify a paragraph that has at least 3 sentences. Evaluate the effectiveness of the paragraph at three levels found in Chapter 5: overall paragraph structure, sentence effectiveness, and the effectiveness of six words of your choosing. Write your evaluation citing specific elements from the chapter.
3. Find a press release or professional communication from a credible organization that could benefit from a revision. Revise the letter or release to make it more effective, using the information from this module. Explain all of your changes and the explanation for each. Include the original text in your submission.
4. Find a Web page or personal blog that you feel has serious writing errors, such as the ones outlined on Page 160 of the textbook. Make all of the necessary changes to the page and explain what changes you made and why. Include the original text in your submission.
5. If a friend told you she was starting a small business to sell handcrafted cat figurines and was creating a website, what guidance would you give her on developing a webpage that employed professional writing and communication standards. Write up your advice, with a rationale for your main points.
(attached you will find the book required for this activity)
Excellence in business communication – Writing and Completing Business Messages
1. Identify a website, advertisement, or piece of mail you received lately that uses obsolete language, buzzwords you don’t understand, or esoteric language. Rewrite the piece using plain language and other writing cues from Chapter 5. Include the original text in your submission.
2. Identify a company that has been in the media in the last 12 months due to a controversy. Find a communication from the company and identify a paragraph that has at least 3 sentences. Evaluate the effectiveness of the paragraph at three levels found in Chapter 5: overall paragraph structure, sentence effectiveness, and the effectiveness of six words of your choosing. Write your evaluation citing specific elements from the chapter.
3. Find a press release or professional communication from a credible organization that could benefit from a revision. Revise the letter or release to make it more effective, using the information from this module. Explain all of your changes and the explanation for each. Include the original text in your submission.
4. Find a Web page or personal blog that you feel has serious writing errors, such as the ones outlined on Page 160 of the textbook. Make all of the necessary changes to the page and explain what changes you made and why. Include the original text in your submission.
5. If a friend told you she was starting a small business to sell handcrafted cat figurines and was creating a website, what guidance would you give her on developing a webpage that employed professional writing and communication standards. Write up your advice, with a rationale for your main points.
(attached you will find the book required for this activity)
Excel spreadsheet: a little girl pulls a sled with a chain, exerting 100N force. The sled is pulled horizontally across a frozen lake. It is difficult to keep the chain horizontal, so she pulls with an angle greater than zero. What happens to the horizontal force as the angle that the rope makes increases? Begin at theta=0degrees and continue to theta=100degrees in 10degree increments. calculate the forces in both the x and y directions for each of these angles. Now repeat the process for varying values of force, from 10N to 200N in 20N steps.
I need to know how to set this problem up to solve in excel. I am not sure what function to use to manipulate the variables.
Fx Fy Angle of chain Horizontal Verticalwith horizontal component ComponentForce =100 N100 0102030405060708090100 10098.4795551993.96445580986.59200104576.58637910964.251849921…
Hi there! Click one of our representatives below and we will get back to you as soon as possible.