Business Skills and Management – Business Modelling

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.

(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)

Experts Answer on Above Business Management Questions

Family financial modelling

The data given shows that the income is 45500, food is 15000, transportation is 6000, holidays 5000, other expenses 8000 and total expenses is 34000. The bank balance at the beginning is 25000 and the bank interest rate is 1.5%. In addition to this, the data provided also suggest that the expense growth rates for food is 5.2%, transportation is 4.5%, holidays is 5.6%, others 8% and income growth is 2.8%.
10 year financial modelling: The formula to calculate the 10 year financial modelling is: Future Value=Base×(1+g)
Results: Final bank balance in 10th year is 41000, and average income is 52000 for 10 years. Average total expenses is 51500. The findings indicate that the expenses grow faster than income, and there is a significant decline in the savings gradually. The expenses are more than the income in the 7th year and the bank balance starts showing a decline thereafter.

Average proportion of income spent

The average percentage of income spent across different categories are food 34%, transportation at 13%, holidays at 12%, and other expenses at 19%. The total expenses amount to 80% on average.
Minimum income growth rate: The weighted average expense growth is 6.2%, so if the income grows at 6.2%, savings will remain positive throughout the year.

Holiday reduction to achieve $150000 balance

While applying the solver, the holiday expense growth rate is reduced by 50%. The downward adjustment of the new holiday growth rate leads to positive savings and allows for meeting the target bank balance.
Compensation model structure: With respect to each sales person, the commission is 0.012sales and the extra commission is 0.005(sales-target).

Proportion of compensation to total sales

It is assumed that the total sales is $8M and compensation is $650000, which amounts to 8.1% compensation in relation to sales.

Sales growth scenario

Sales growth for top 5 is 10%, and for 6-8th rank, it is 5% and last 2 is 2%. As a result, the new compensation would be $720000, and increases by $70000 which shows a 10% increase.
Adjust basic salary structure: The minimum payment goal is $30000, and this requires increasing the entry level basic from $15k to $22k, mid level from $25k to $30k and for seniors level, it’s $35k.

This model answer is reviewed by Pamela Lim, having good understanding of business management practices.
Disclaimer: This answer is a model for study and reference purposes only. Please do not submit it as your own work.

Want a Full Worked Out Answer with References?

Assignments based on business modelling involving finance and sales compensation analysis requires not only financial expertise but also needs calculation and analytical abilities. Financial accounting experts are well poised to provide quality assistance involving modelling analysis. If you are facing challenges in completing your business modelling assignment above, consult our professional assignment experts to get appropriate guidance and suggestions to enhance your learning.