BUS100 Assignment Questions

Question 1 – 40 marks 

Last year (year 0), Amy’s family spent $15,000 on food, $6,000 on transportation, $5,000 on holidays, and $8,000 on other expenses. The cost increases per year, according to government estimates, for food is 5.2%, transportation is 4.5%, holidays are 5.6%, and others are 8%. The family’s total income in the previous year was $45,500. This income is expected to grow by only 2.8% per year. Their bank balance at the start of last year was $25,000. At the end of each year, the family will spend the remaining income minus all expenses into the bank account as savings. You may assume that the money in the bank earns an interest of 1.5% annually.

(a). Practice business modelling skills to develop a spreadsheet model to evaluate the family’s finances for the next ten years (e.g., from year 1 to the end of 10 years). You should create a table to show the yearly income, expenses incurred, such as food, transportation, holidays, and others. The table should also include any savings and the final bank balance. Use the model to display the final balance at the end of the 10 years, the average income, and the average total expenses. (15 marks)

(b). Determine the average proportion of income that will be spent on the various expenses for the 10 years. (7 marks)

(c). Use the model to compute the minimum income growth rate to ensure the family’s incomes keep up with expenses? Explain how you achieve the answer. What is the total bank balance at the end of the ten years using this minimum income growth rate? (6 marks)

(d). Using the model in part (a), determine how much they should reduce the holiday expense over the years to ensure that there is a positive saving each year and achieve $150,000 bank balance at the end of 10th years. Show the details of how you achieve the answer. What is the percentage of holiday expense reduction as compared to part (a)?

Question 2 – 40 marks 

The sales team has ten salespersons. Their basic pays vary from $15,000 to $35,000 per annum, according to their number of years with the company, and they are given corresponding sales targets. For staff whose years with the company are less than 2 years old, the basic salary is $15,000; those whose years with the company are between 2 and 6 years will get $25,000, and those whose are with the company above 6 years will get $35,000. In addition, they receive a 1.2% commission on all sales they bring in and an additional 0.5% point commission for every dollar they sell above their targets. Any salesperson who sells $1 million or more gets an extra bonus of $5,000. The salespersons are ranked according to the percentage they achieve above their targets. Only if the person exceeds the target, the first-ranked person receives an incentive trip to the annual sales conference and a five-day, all-expenses-paid vacation for two. This year, the event is taking place in the USA, specifically in Florida, and the incentive package is valued at $15,000. Table 1 below shows the sales data information for the team.

Table 1: Sales Data

(a). Demonstrate your business modelling skills to develop a spreadsheet model to help the manager compute the individual salesperson’s commission, bonus, incentive, and total compensation. You should show the answer for each salesperson in detail. (15 marks)

(b). Use the model to compute the total compensation the company has to give out. What is the proportion of the total compensation to the total sales? (5 marks)

c) If the sales is expected to grow by 10% for the first five ranked members, 5% for ranks from 6th to 8th, and only 2% growth for the last two ranks. Compute the new compensation for each salesperson and find the total compensation. What are the change in total compensation and the percentage of change as compared to part (b)? (5 marks)

(d). Outline how the company should change the basic salary payments based on the number of years in the company so that all the sales personnel can achieve a minimum pay of $30,000. You may refer to the increase in sales as discussed in part (c) for each salesperson. Justify using a model and discuss how the change in pay structure can help the sales team to achieve the goal based on your recommendations. (10 marks)