W24_1190 Minor Excel Assignment: Commerce & Business Administration

Commerce & Business Administration

CSIS 1190 - Excel in Business MS EXCEL Minor Assignment

Introduction

This is an individual project using MS Excel.  The purpose of this assignment is to give you some experience with several of the features of the Microsoft Excel spreadsheet. The breakdown marks of tasks are as follows:

BACKGROUD INFORMATION

The purchase of a car usually entails extensive bargaining between the dealer and the consumer.  The dealer has an asking price but typically settles for less.  The commission paid to a salesperson depends on how close the selling price is to the asking price.  Victory Motors has the following policy for its sales staff:

• A 3.3% commission on the actual selling price for cars sold at 95% or more of the asking price
• A 2.2% commission on the actual selling price for cars sold at 88% or more of the asking price
• A 1.2% commission on the actual selling price for cars sold at 85% or more of the asking price
• No commission will be paid for cars sold at less than 85% of the asking price

REQUIREMENTS

One the 1st spreadsheet “Revenue”, you are required to:

• Calculate the dealer’s asking price = dealer’s cost + (X% of mark-up * dealer’s cost)
• Calculate the Sold Price over Asking Price for % of Asking Price achieved
• Calculate the Commission earned by salesperson based on the commission structure above.
• Calculate the final revenue for Victory (minus selling costs) on every sale for Victory Motors

    Revenue = [the selling price - (the cost of the car + salesperson’s commission)]

You are also to remark “You can do better” if the percentage of actual selling price over asking price is less than 85% and “Good Job if it is 85% or better.  The salesperson will not receive any commission for percentage less than 85% ☹

You may also want to calculate some statistical calculations such as Highest, Lowest and Average figures, etc.)  Then, also plot an embedded chart to display any information that you like.   Do all the above in Spreadsheet #1 and name this spreadsheet REVENUE.

You are required to create a spreadsheet-based decision support model that allows the CEO of Victory Holdings Group of Companies to understand how GM of Victory Motors uses MS EXCEL to compare different scenarios (e.g. Varying mark-up percentages 22%, 24%, 26%).  Rather than having a separate model for each scenario, you are expected to design a single model and employ “scenarios” to change only those aspects of the model that varies with the scenarios being considered. 

DATA

You MUST use the following data for your assignments. 

On a second spreadsheet (name it FORECAST), prepare a 5-year forecast (Y2024 to Y2028). 

The figures above only give you the 1st half of the revenue (minus selling costs) for 2023.  Add up the revenue for Victory for the 1st half of 2023.  For the second half of 2023, assume that it will generate 2.5 times of the 1st half of the 2023 revenue to get the TOTAL revenue for 2023.

The Y2023 Other Cost of Operations for Victory Motors is $410,000 and will increase annually.  The expected annual increase for Revenue and Cost is 2.5% and 7.5% respectively.

You may wish to plot a bar chart (Separate chart) to display the gross profit over the 5 years.  Also insert a graphic in the spreadsheet and create a hyperlink for user to click on the graphic and it will bring the user to an appropriate web-site that might be useful for the manager.

The company decided to borrow a one-time loan of $75,000 in Jan 2024 to launch a marketing campaign to expand the business.  The $75,000 is taken from the bank and use in the marketing campaign directly and as such no additional $ is plough back into the cash flow.   Given the bank charges 6% interest rate and the company wishes to borrow for 5-year term, add another row for Annual Payment to the bank (Victory Motors chooses to pay monthly payment, calculate the total 12 monthly payments using the appropriate built-in functions and formula.)  Also calculate the Total Bank term payment for the loan assuming the monthly payment scheme, Total Interest for the loan taken.

Then compute the Accumulated Cash Flow from 2024 to 2028 (taking in consideration the 12 monthly bank payments).  You DO NOT need to worry about the Present Value/Future Value concepts (just to keep the assignment simple).

Also prepare a memo as a General Manager to the CEO using MS Word (together with data and charts from MS Excel) to “advice” and comments on the sales figures projected based on the scenario.

DESIGN ISSUES

• Never use a number in a formula. 
• Try to plan your spreadsheet so that it can be re-used in the future.

SUBMISSION

You are required to e-mail the following to me with the e-mail subject

 (example FirstName_LastName_ _Meeting Day =  JohnSmith_CSIS1190Monday):

• Victory.xlsx which will contain 2 spreadsheets (revenue & forecast as well as a separate chart)
• Memo.docx – memo written to your CEO with advices.

You should also make a backup of the system; late submissions will not be graded.