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

Explain methods of calculating depreciation, The financial year of Jack and...

The financial year of Jack and Jill Ltd will end on 31 May 2008. At 1 June 2007, the company had in use equipment with a total accumulated cost of Rs 135,620 which had been depreci

What do you mean by operating agreement, Q. What do you mean by Operating A...

Q. What do you mean by Operating Agreement? Operating Agreement - Agreement, generally a written document which sets out the rules by which a LIMITED LIABILITY COMPANY (LLC) is

compare the marginal rates of substitution, Consider two individuals with ...

Consider two individuals with endowments of 60 hours per week of leisure, nonlabour income of $Y per week, and a wage of $7.50.  At this wage assume that workers are constrained by

Assertion, Assertion -Implicit or explicit representations by an entity's m...

Assertion -Implicit or explicit representations by an entity's management which are embodied infinancial statement components and for which AUDITOR obtains and evaluates evidential

Calculate the depreciation of project, A small airline company called Mancu...

A small airline company called Mancunian Airways (MA) is considering purchasing a new jet, which will be used solely on the new route: Manchester, Paris, Madrid and back to Manch

Calculate asset turnover, What is asset turnover - Asset turnover is a rat...

What is asset turnover - Asset turnover is a ratio which is considered as measures the effectiveness with which a business uses its assets in relation to the level of sales or inc

Current yields for t-bonds, ACT presently is all-equity financed. This refl...

ACT presently is all-equity financed. This reflects the stance of the former CEO, a dominant personality who stated repeatedly: "I don't want us to be in thrall to the demands of t

Cumulative and substitutional legacies and devises, Cumulative and substitu...

Cumulative and substitutional legacies and devises Where a will makes two gifts of unequal amounts to the same person, they are assumed, in the absence of a contrary indication

Prepare mehring''s statement of cash flows, The following information is av...

The following information is available for Mehring Corporation for the year ended December 31, 2012: Collection of principal on long-term loan to a supplier

What is the difference, What is the difference between financial statements...

What is the difference between financial statements prepared from the expanded accounting equation and those prepared from a trial balance?

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