CREATE A CVP CHART USING THE RESULTS OF YOUR DATA TABLE.

25 May CREATE A CVP CHART USING THE RESULTS OF YOUR DATA TABLE.

You may use the textbook, previous cases and Excel Help during this exam. You may not use a tutor or a classmate to complete this exam. It is an individual assessment. The second section of this exam is designed to take two hours. The first part is a posttest to the pretest you took at the beginning of the semester. The second section only include topics covered in tutorials 9 and 10.

The outcomes for this class and this assessment are as follows:

1. Develop thinking and organizational skills in planning, designing and creating spreadsheets that fulfill a business related function or need.

2. Analyze data presented in a business case requiring information management for decision-making and effective use the tools of Excel to create spreadsheets that address the business problem.

3. Choose appropriate presentation styles for visual representation of data.

Item 1 Financial Analysis (This is the same problem as was the pretest. You may wish to start with what you created for the pretest or begin from scratch. Your pretest file can be downloaded from the Pretest folder in D2L.)

Your company is looking to provide a company car for its president and needs to know which option, buy or lease, would be the best financial decision.

Part I. Design and create a workbook in Excel to compare the purchase versus lease of an automobile. The car the company wishes to purchase is $18,400. Monthly net income (after taxes) is $3,024. You need to decide whether to lease the car over three years or buy the car with the aid of a bank loan for $18,400. Design and create your solution using Excel. Format your workbook attractively and appropriately based on professional design principles learned in this course. You must use proper functions and formulas for all calculations. Name the worksheet Vehicle Purchase Analysis. Be sure your calculations support your conclusions. Save your file as Exam III Item 1.

The following information is provided:

· If the company was to lease the vehicle with zero down, the lease payment would be $355 per month. At the end of the three years, there is an option to buy the vehicle for $12,000. What would be the total cost to own the vehicle at the end of three years?

· If the company was to purchase the vehicle with the aid of a bank loan with zero down for three years (interest rate is 7%), what would the monthly payment be? What would be the total cost to own the vehicle at the end of three years?

· How much would the company pay, over and above the price of the vehicle, for each option?

· What do you recommend as the best financial alternative: purchase or lease? Which option would you recommend the company choose and why? What other factors should be considered in the decision OTHER THAN cost?

Create an attractive chart to illustrate your financial findings. Be sure to use appropriate labels. Which chart type did you choose and why?

Perform all analysis using Excel. Answer the questions within your Excel workbook.

Part 2. Amortization Schedule. Create a new worksheet within the Exam III Item 1 file and name it Amortization Schedule. Assume the vehicle was purchased and the payback period was 3 years. Design and create an amortization schedule with a column indicating the # of the payment (there will be a total of 36 payments), the remaining principal, the amount of the payment applied to interest, the amount of the payment applied to principal, and the total amount of the monthly payment (will be consistent over the payback period). Use ppmt and ipmt function for the payment and interest portion of each payment.

Save your file as Exam III Item 1.

Item 2 Data Tables and Scenarios Rogers Party Planners

You are the sole proprietor of a small party planning company that specializes in small community and company events (average of 200 people per event). You have one employee who you pay $200 per party. You are also responsible for paying payroll taxes. You rent a small storage unit which you use for storing party materials and supplies. You have to pay for liability insurance. Because of the Seasonal Nature of Party planning, you may increase advertising during specific seasons. The advertising expense is based on an average throughout the year. The number of parties per month is also an average throughout the year.

You are concerned that you might have cash flow issues during slow months and want to analyze how the different seasons may affect your income. You also want to see how increasing you’re pricing during busy seasons may affect your income. You are thinking about hiring a second employee and perhaps taking on larger parties. You decide to explore all these different avenues by using the tools provided by Excel. You are also considering purchasing or building a facility that can be used for your events and parties.

Part 1. Use the data file Rogers.xls. Add a documentation sheet with author, date and purpose of workbook.

Create an Income Statement with the following data: Enter formulas to calculate amount in cells with asterisks.

Rogers Party Planning Monthly Income Statement

Sales Information

Revenue per head for party

$15.00

Average size of party

200

Revenue per party

$3,000.00
Number of Parties per Month

8

Average Revenue per Party

3,000.00
Total Monthly Revenue

$24,000.00
Variable Expenses

Average Material Cost per Head

7.17

Total Material Cost per Month

11,472.00
Payroll per Party

430.00

Total Payroll per Month

3,440.00
Total Variable Expenses

$14,912.00
Fixed Expenses

Insurance

90.00

Rent

800.00

Advertising

750.00

Total Fixed Expenses

$1,640.00
Summary

Total Monthly Revenue

$24,000.00
Total Monthly Expenses

$16,552.00
Gross Monthly Income

$ 7,448.00
Taxes (28%)

2,085.44
Net Monthly Income

$ 5,362.56
a) In the same worksheet as your income statement, create a single variable data table to analyze how the net income per month would change based on the number of parties per month. Use the range of parties from 1 per month to 16 per month. You can refer to the PDF and Word doc to check your table results. These are posted in D2L under the content area and named monthly income data table.

b) Create a CVP Chart using the results of your data table. Save this in the same worksheet at your data table and income statement. Format according to the rubric on the last page of this exam.