BCPT 123, Introduction to Business Computing

Excel Assignment No. 1: Using the Payment Function

READ THE INSTRUCTIONS CAREFULLY

Calculate the following information:

Search the internet to purchase the following items using the 2 scenarios listed below. Calculate and present all 2 scenarios using 1 Excel workbook.

SCENARIO NO.1 Assume you are a Broker. Provide the following information for you client.

Purchase a Company Executive Car: Your client is the CEO of a high tech business and she requires a new car. She has hired your brokerage company to find suitable transportation for business. She entertains and meets with many foreign business people so company image is important. She will use a company chauffer. Provide your client with 3 different car model options. You select the recommended car for the client.

1. You determine the down payment.

2. Determine what your bank loan will be.

3. Calculate the interest rate and make sure the Rate is described and displayed when you format your assignment. DO NOT USE an Internet purchase calculator. This MUST be calculated in Excel.

4. Calculate what you monthly payment would be if the loan was amortized over the following terms.

a. 3 years

b. 5 years

c. 8 years

5. You must display all (Monthly) Calculations in your workbook.

6. Provide 3 different car options to your client to select from.

7. You can use the same Rate for all 3 car calculations.

8. Use the banks prime rate of 3.69% as your base Prime Rate. Add 1.16% to the Banks Prime rate to determine the consumer loan Interest Rate.

9. Provide 3 various down payment options.

10. From the 3 possible cars you have provided for your client you are to select one of them as your recommendation. Insert a photograph of the recommended car purchase into your client Excel presentation sheets.

11. Provide a brief description of the car.

12. Invoice this client just for your services.

13. Use an Excel Invoice Template Simple Invoiceincluded in the Moodle File to bill your client.

14. Include a GST tax of 5% for your services and calculate the results.

15. The client will pay for their own products.

SCENARIO NO. 2 - Assume you are a Yacht Broker. Provide the following information for a different client.

Purchase a Sailboat: Your client is the owner of a manufacturing business and he would like to purchase another larger sailboat/yacht for personal use. He currently owns a C&C 27 ft. sailboat. He has hired your yacht brokerage company to find a suitable sailboat/yacht. He would like his next boat to sleep between 6 and 13 people. Use his current sailboat as a trade-in. The trade-in value will be 20% of the new purchase. You may also recommend to your client an increased down payment amount. Calculate this value and clearly display it in your quote.

1. Determine the trade-in value and calculate the down payment.

2. The trade-in value will be 20% of the new boat purchase.

3. Determine what will be financed.

4. Calculate the interest rate and make sure the Rate is described and displayed when you format your assignment. DO NOT USE an Internet purchase calculator. This MUST be calculated in Excel.

5. Calculate what your monthly payments would be if the loan was amortized over the following terms.

a. 10 years

b. 15 years

c. 25 years

6. You must display all Terms (Monthly) calculation in your workbook

7. Provide 3 different Yacht/Sailboat options for yourclients inspection.

8. You can use the Same bank Rate for all 3 Yacht calculations

9. Provide 3 various down payment options.

10. Use the banks prime rate of 1.55% as your base Prime Rate. Add 2.20% to the Banks Prime rate to determine the consumer loan Interest Rate.

11. Insert a photograph of your recommended yachtpurchase into your 0client Excel presentation sheets.

12. Provide a brief description of the Yacht/boat

13. Invoice this client for just your services.

14. Use an Excel Invoice Template Simple Invoice included in the Moodle File to bill your client.

15. Invoice this client for your services only.

16. The client will pay for their own products.

Presentation Instructions:

A. Create a name for your Brokerage Company and display it on your pages. Also title it Excel Assignment No. 1.

B. Insert your company name in the footer

C. Create or use a clipart image for your logo. It should also be incorporated into your pages.

D. Upload your Excel workbook with all your clients calculations.

E. Name each work Sheet Tab using this Naming list:

i. Car

ii. Boat

F. Apply formatting to your sheets

YOU MUST DEMONSTARTE AND SHOW ALL YOU CALCULATIONS ON YOUR WORKSHEETS.

G. Create an invoice for your services.

a. Your broker fees will be calculated as a flat 18.75% on the total price of your recommend Car/Boat.

b. Select and use only 1 of the 3 quotes for each of the scenarios. IE; 1 brokerage fee for the car purchase, 1 fee for the yacht/sailboat purchase. Use an Excel Invoice

c. Create your brokerage companys address.

d. Create a Billing Address for each client.

e. Add a GST tax of 5% for your services and calculate the results.

f. Clearly display all of the financial information for you clients inspection on the invoice.

H. Remember you are a business. Presentation to the client is paramount.

I. SAVE YOUR FILE AS Broker_PMT

J. Upload your finished Workbook and the two Invoicesto Moodle. 1 Workbook which includes 4 Worksheets.