Implement a spreadsheet to calculate payments, Financial Accounting

Assignment Help:

Tony is a salesperson at a local auto showroom. He asks you to assist him in developing a tool for calculating purchase and lease payments. He has already developed a draft of the problem but he needs help making it easier to use. Tony has some ideas about what the workbooks should contain. He has one sheet named "Sales Support" which consolidates the various data entry and results needed to price a car under various options. A second sheet, named "Loan Rate Sheet" contains the annual loan rates available from several local banks. A third worksheet, labeled "Do Not Delete" contains information about bank loan rates - it's confidential, so it is hidden and protected from you. Leave it in place as is.  A fourth sheet, "Calculations", is for your use to perform intermediate calculations as you see fit.

He has provided a draft of the workbook, which is available on Blackboard.  The green areas are where you make modifications to the interface. Users of the spreadsheet will not need to change other sections.

Your tasks:

Create a payment calculator on the Sales Support worksheet.   

First, open the draft worksheet and save it as an Excel 2010 workbook (.xlsx) named with your last name.  Enter your name and NetID on the Sales Support worksheet in cells A1 and A2. 

The total vehicle price (Cell B11) is determined from the base price of the vehicle (Cell B5), the total cost of six options selected by the customer (Cell B6), a repeat customer discount (Cell B9), plus applicable taxes (Cell B10).  

o The base price of the vehicle may run from a minimum of $1,000 to a maximum of $30,000. Create a form control scroll bar in cells D5 through F5 to simplify the entry of the base price of the vehicle price.  Use increments of $500.  The cell link of the scrollbar should be cell B5. 

Format cell B5 for currency.

o Create checkboxes for six options: CD changer, Movie Player, Bucket Seats, Air Conditioning, Full size spare tire, and Lifetime Oil changes. Place the checkboxes in range D6:D8 or F6:F8, with the cell links in E6:E8 or G6:G8, respectively. Each selected option adds $200 to the cost of the vehicle.  Put the total option cost in B6, formatted for currency.

o Return customers receive a $200 discount from the price before taxes are calculated. Add a combobox form control to indicate if the customer is a new customer or a returning one.  Store the data you need for the input ranges on the Calculations sheet.  Use Cell E9 for the cell link.


Related Discussions:- Implement a spreadsheet to calculate payments

Record performance of the services, Robin Corporation accepted credit cards...

Robin Corporation accepted credit cards for $34,200 of services performed in October 2011.The credit card company charged a 3% service fee and paid Robin as soon as it received the

Explain about spring and forward loading, Q. Explain about Spring and Forwa...

Q. Explain about Spring and Forward loading? Spring loading - Timing of option grants to occur before good news or after bad news is released Concerns about insider trading

Student, Open "Hoyle CPA: Earnings Per Share." Under "Free Questions" selec...

Open "Hoyle CPA: Earnings Per Share." Under "Free Questions" select "Financial Accounting & Reporting."

Cost recovery deduction, On May 15, 2010, Your Corporation acquired an airp...

On May 15, 2010, Your Corporation acquired an airplane (5 year recovery period, 6 year class life) for $1,450,000. Its qualified business use is 54%. Determine the maximum cost rec

What amount of loss, On January 1, 2011, Doty Co. redeemed its 15-year bond...

On January 1, 2011, Doty Co. redeemed its 15-year bonds of $2,500,000 par value for 102. They were originally issued on January 1, 1999 at 98 with a maturity date of January 1, 201

Calculate the profitability of company - investment decision, In no more th...

In no more than one typed page, provide a statement of your decision to invest or not invest in this company's stock based on your interpretation of the company's long-term prospec

Finacial management, There are two projects A and B. The initial capital ou...

There are two projects A and B. The initial capital outlay of A and B are Rs.1,35,000 and Rs.2,40,000 respectively. There will be no scrap value at the end of the life of both the

Compute the total amount of dividends that was paid to each, Vantage Compan...

Vantage Company issued bonds with a $500,000 face value and a 6% stated rate of interest on January 1, 2013. The bonds carried a 5-year term and sold for 95. Vantage uses the strai

Variation of securities-executorship laws and accounts, Variation of securi...

Variation of securities It would seem logical to carry out a strict apportionment between income and capital every time investments are bought or sold. If this were done, it wo

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd