Exercise 14: Create a Merchandising Budget
The scenario is that Star Events, an event management company, will be merchandising a number of products in conjunction with a forthcoming Triathlon championship. It is your task to design spreadsheet to work out the profit that will be earned.
A partially completed Excel worksheet is provided to assist you. It contains column headers and row labels but you must enter appropropriate formulae to find the answer to the total profit generated by the scenario.
Download the activity sheet for Budgeting Exercise 14
Download the Excel worksheet template for Budgeting Exercise 14
- Your first task in this exercise is to utilise the Excel template provided to work out the total profit on merchandising, if all items are sold. Make a note of the total profit as you will need this figure for the 2nd task.
- An important aspect about this Excel template is that the viewer can make changes easily to factors such as cost price, mark-up percentage and sales quantity and see what happens to total profit. For your second task, calculate the percentage increase in total profit if the mark-up is increased from 25% to 30% for all product lines, and items are sold.
- Your last task is to use the spreadsheet to work out what mark-up is needed to achieve a total profit of $4,800 if all items are sold.
An important aspect about this exercise is that the learner will have the opportunity not only to develop a spreadsheet to work out an answer to this problem, but also an interactive tool. For example, changes can be easily made to cost price, quantity sold or mark-up percentage and the effect on total profit seen.