BikePro Ltd want you to perform a Cost Volume Profit Analysis for their High-End Racing Bike product, the X-800. You will need to calculate the labour costs, material costs, fixed costs and variable costs and then work out the break-even volume. You have also been asked to show a forecast for different sales volumes and produce a break-even analysis line chart.
Start by downloading the assessment workbook from iLearn and copying/moving it to an appropriate folder. When you open the workbook it is very important that you Enable Macros/Content. You will then be asked to enter your Student Number (you will not be able to edit it afterwards, so type it in carefully) and then enter your Student Name.
Detailed instructions are provided on the next pages. Please follow each of the instructions precisely and in sequence.
All the grey cells require you to insert some form of calculation, i.e. start with an “=”, just typing in the answer will result in 0 marks. Do not change the structure of the workbook in anyway or put any workings anywhere other than directed. Only use rounding functions where specifically required. The use of named ranges and tables is encouraged, and in certain cases required.
Because we are developing a model that we may wish to use with other products or different values it is very important that all calculations will return a correct answer regardless of the values in the spreadsheet and must still work correctly when the lists of data (Labour Costs, BOM & Inventory) are sorted in a different order.
Double check your answers as you go as one wrong result may affect lots of other results. Use the check boxes in the Done column to keep track of tasks completed.
Remember to save often. When you are ready to submit, upload the completed Excel File to iLearn (Excel Submission). You do not need to rename it as your student number will be automatically attached by iLearn, but please ensure that it is an Excel file you submit (Numbers files will not be accepted) and that there are no “non-English” characters in the file name as these files cannot be opened in an English version of Excel.
Question Instruction Marks Done
| Section A | The following are to be completed on the Labour Costs Worksheet | 10 | |
| A1 | All staff members receive an annual bonus of $200 plus an additional percentage of their Annual Income. Each staff member has been allocated their own percentage bonus rate (column C). In D6:D16 calculate each staff members Bonus. | 2 | ☐ |
| A2 | F3 contains the % Superannuation given to all staff which is calculated on their annual salary only (bonus not included). In E6 calculate the annual Super amount paid to each staff member using the value in F3. Copy the formula down to E16 (to get full marks, a named range or an absolute cell reference must be used). | 2 | ☐ |
| A3 | In F6:F16 calculate the total package for each staff member (Annual Salary, Bonus and Super). Adjust the spreadsheet so that the “#####” problem is addressed. | 2 | ☐ |
| A4 | In F18 calculate the Total Labour Costs. | 1 | ☐ |
| A5 | Insert a row below Row 18. In E19 type “Average” and in F19 calculate the average Total Package. | 2 | ☐ |
| A6 | In F20 use a formula to calculate the highest Total Package paid to an individual staff member (i.e. the biggest value in F6:F16)
|
1 | ☐
|
| Section B | The following are to be completed on the BoM and Inventory Worksheets | 13 | |
| B1 | Go to the Inventory Sheet. This sheet contains a list of all the items the company keep in stock to build the bikes. Each part is identified by a unique part code.
Convert the Range A3:J44 to a Table. Name the table Inventory. |
1 | ☐ |
| B2 | Go to the Bill of Materials (BoM) Worksheet. This sheet contains a list of which parts (and how many) are needed to make two different types of bicycles, the ZF300 (no longer in production) and the X-800, the model currently produced. The table is incomplete as it doesn’t have the rest of the information about each part. Use appropriate formulas to lookup the corresponding Category, Product Description, Supplier and Unit Price for each of the Part Codes from the Inventory table. (Note: Both the BoM and the Inventory are frequently re-sorted, so the formulas must work regardless of sort order.) | 8 | ☐
|
| B3 | Calculate the Total Price for each Part Code in column H. | 0.5 | ☐ |
| B4 | Still in the BoM sheet, click into K3. Note the dropdown that allows you to select a product. In K4 create a formula to calculate the total price of parts of category Groupset for that product. Copy the formula down to K8 to get a breakdown of Product Costs by Category. | 3 | ☐ |
| B5 | In K9 calculate the total cost to make one unit of the selected product. | 0.5 | ☐
|
| Section C | The following are to be completed in the CVP Analysis Worksheet | 17 | |
| C1 | In B14 calculate the Total Fixed Costs. | 1 | ☐
|
| C2 | In E5, using the information in the BoM, calculate the Direct Material Cost per unit specified in B3. (Note Do NOT use the value in BoM!K9 as this can be for another product, and ensure the calculation works regardless of sort order in the BoM.) | 2 | ☐ |
| C3 | In E6 use the information in the Labour Costs sheet to calculate the Direct Labour Cost per unit. Note if the values in the changes this figure should update. | 1 | ☐ |
| C4 | In E9 calculate the Variable Manufacturing Cost per unit. | 1 | ☐ |
| C5 | In E14 calculate the Variable Selling & Admin Cost per unit (as a %). | 1 | ☐ |
| C6 | In H4 calculate the Contribution Margin per Unit (this is the Unit Sales Price less the Variable Manufacturing Cost per unit and the Variable Selling & Admin Cost per unit). | 2 | ☐ |
Question Instruction Marks Done
| C7 | In H5 calculate your break-even volume (in units) rounded up to 0 decimal places. (This answer is very important so double check it, multiply it by the variable cost figures and add the fixed costs back in and unrounded it should give you 0). | 2 | ☐ |
| C8 | In H6 multiply the Break-Even Volume in Units by the Unit Sales Price to calculate the Break-Even Sales Value. | 1 | ☐ |
| C9 | In H9 to H14 calculate the Break-Even Variable Costs using the calculated figures in H5 or H6 (where appropriate). Note, each answer is worth ½ mark which will only be awarded if the answer is correct. | 3 | ☐ |
| C10 | In H15 calculate the Total Break-Even Variable Costs. (Answer must be correct to get mark.) | 1 | ☐ |
| C11 | In H17 calculate the Break-Even Contribution Margin. | 1 | ☐ |
| C12 | In H18 calculate the Profit Before Tax. (If we hadn’t rounded this should be 0). | 1 | ☐
|
| Section D | The following are to be completed in the Forecast Worksheet | 20 | |
| D1 | In C4 create a calculation to add the Increment (the amount our forecast is increasing by) in B3 to the volume in B4. (Note we want the ability to change the increment so do NOT just add 100, use a relative reference or a range name). Drag the formula across to G4 | 1 | ☐ |
| D2 | In row 5 calculate the total sales revenue for each of the different Sales Volumes. | 1 | ☐ |
| D3 | Complete the variable costs table for the different sales volumes (B8:G14). | 4 | ☐ |
| D4 | In row 16 calculate the Contribution Margin for each sales volume. | 1 | ☐ |
| D5 | In row 17 calculate Contribution Margin less Fixed Costs for each sales volume. | 1 | ☐ |
| D6 | In B20:G22 complete the income tax for each sales volume. Formulas must take into consideration that in some cases there may have been a loss in which case the income tax should be 0. (Do NOT just type the 0 as the model should work with different inputs.) | 5 | ☐ |
| D7 | In row 24 calculate the profit after factoring in the income tax. | 1 | ☐ |
| D8 | In row 26 work out the profit for each sales volume as a percentage of sales. (Format as a percentage with 2 decimal places, do not apply rounding formulas.) | 1 | ☐ |
| D9 | Complete the Break-Even Analysis Line Chart Data in B29 to G32 for each of the Unit volumes. (Note Total Costs = Variable Costs + Fixed Costs+ Income Tax) | 2 | ☐ |
| D10 | Using the data generated in question D9, create a Line Chart that plots the Total
Fixed Costs, Total Costs & Total Sales. Add the Volume Units as Horizontal Axis Labels and add the Chart Title “Break-Even Analysis“. (N.B. Only include one chart ensure it is on the Forecast Sheet and that it is a 2D Line Chart with no line markers ( not a Scatter chart with smooth lines). |
3 | ☐
|
| Section E | The following are to be completed in the Inventory Worksheet | 5 | |
| E1 | In H4 create a formula to convert the weight in grams to a weight in pounds. The calculation must round the result to 2 decimal places. (Ensure the formula is copied down for the whole table.) | 2 | ☐ |
| E2
|
In J4 create a calculation to work out the reorder quantity. If the stock level is below the minimum level shown in M3, then reorder the appropriate amount as shown in the Re-Order Quantities table (L5:M9), otherwise return 0. (Ensure the formula is copied down for the whole table. Note you should have one consistent calculation for the column and the calculations should work regardless of values or sort order.) | 3 | ☐
|
| TOTAL MARKS | 65 |
Contact us for solution at info@myassignmentguru.com
Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).
After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.
If your question is slightly different from the above question, please contact us at info@myassignmentguru.com with your version of question.