BUS100 Tutor-Marked Assignment

This assignment is worth 40% of the final mark for BUS100 Business Skills and Management.

The cut-off date for this assignment is 18 September 2026, 2355hrs.

Question 1

A company sells three products (A, B, C). Historical monthly sales data for the past 12 months has been provided. The company wants to forecast demand over the next 6 months, plan production, and analyse its finances. You may assume a linear relationship between sales and the number of months. The selling prices for A, B, and C are $20, $35, and $50, and their unit costs are $12, $20, and $30, respectively. There is a fixed overhead cost of $10,000 per month.

Historical sales (12 months) for each product are shown in the table below.

a. Using the past twelve months’ historical demand, determine the next six months’ demand for each product using the appropriate Excel function. Plot the appropriate graph to show the demand from month 1 to month 18.

(6 marks)

b. Construct a spreadsheet model to evaluate the company’s finances for the next six months. You should use the forecasted demand from part (a) and create a table showing monthly revenue, production cost, overhead cost, total cost (including production and overhead costs), and profit.  Use the model to determine total revenue, total cost, and profit accumulated over the next six months.

(12 marks)

c. Determine the optimal sale volume for product A, for the 6^th month from now, for the company to break even; you may assume that the other parameters all remain the same. For this part only, you may assume equal quantities across products. Explain how you get the answers.

(8 marks)

d. Use the model in part (a) for the forecasting demand, and the rest of the parameters to be the same as the model in part (b); if the company wants to achieve an accumulated profit of $40,000 at the end of the next six months, by changing the selling price, what should be the optimal selling price for each product? The selling price can’t exceed 2.5 times the unit cost; C is the most expensive, followed by B, and A is the cheapest. Explain the solver objective and constraints in words.

(9 marks)

Presentation, layout, and formatting

Your models for Q1 must be neatly laid out and presented. You must apply appropriate colours, alignment, labels, and formatting of numbers. Documentation and legends are expected.

(5 marks)
[Total Q1. – 40 marks ]

Question 2

Mr. Tan is planning to start a new tour company in Cambodia. He will take visitors on a wholeday trip to the main tourist attractions in the ancient city in the van he plans to lease. He would like to know how many visitors he needs each month to break even. It is assumed that there is no half-day trip. He will work 25 days per month. Each day, he can take a maximum of 150 customers.

His costs for the new local tourism business would be as follows:

He plans to charge $45 per visitor for a day trip, and petrol costs $4.50 per Liter.

a. Design a worksheet and tabulate the number of visitors from 1,000 to 2500 in intervals of 100. Each row represents the number of visitors, the total revenue from those visitors, the total fixed cost, the total variable cost, and the profit and profit margin based on the number of visitors. Your formula should fill all rows in the table.

(16 marks)

b. Based on the table, show the appropriate graph for the number of visitors against the total profit. Comment on the relationship between the number of visitors and the profit based on the graph drawn.

(6 marks)

c. Report the minimum number of visitors to the nearest ten to achieve a monthly profit of at least S$60,000. What would be the average daily visitors they should expect?

(5 marks)

d. Compute the new price to charge the customer if the average customer per month is 1200 and the rest of the parameters are the same as the model in part (a), and the company wants to make at least 30% profit margin. Explain how you get your answer.

(8 marks)   

Presentation, layout, and formatting

Your models for Q2 must be neatly laid out and presented. You must apply appropriate colours, alignment, labels, and formatting of numbers. Documentation and legends are expected.

 (5 marks)
[Total Q2. – 40 marks ]

Question 3

Using your experience running a virtual company at MonsoonSIM, analyse the given data in TMA Excel Template (Q3data).

a. Summarise the data (Q3data) from TMA Excel Template using a Pivot Table to get the average selling price and cost price for the three products, and analyse the sales-related performance indicators.(Limit your answer to 300 words.)            

(10 marks)

b. Describe three ways in which your business in MonsoonSIM can increase revenue. Give a detailed explanation of the suggestions.(Limit your answer to 300 words.)

(10 marks)
[Total Q3. – 20 marks ]

———-End of TMA Paper ———-