Scenario
Bikes 2000 Ltd produce two high end bicycle products. They need you to create a spreadsheet that can conduct a multi-product Cost Volume Profit Analysis. You can assume that all products made are sold and that sales price, fixed costs, and the sales mix stay constant. You will need to calculate the labour costs, material costs, fixed costs and variable costs and then work out the break even volume using a weighted contribution margin. You have also been asked to show a forecast for different unit volumes for a selected product line and produce a break even analysis line chart. Download the full instructions and a mark breakdown from iLearn.
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 and 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. Please note the first 3 worksheets are locked and you will only be able to change the cells specified.
General Guidelines
- All the light grey cells require you to insert a calculation or a cell reference, 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 requested.
- The use of named ranges is encouraged, and in certain cases required but unless stipulated calculations do not need to use named ranges.
- Please do NOT create tables other than where directed.
- Because you are developing a model that we may wish to use for future product lines with different values it is very important that all calculations 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 other results.
- To get full marks for a question both the formula AND the answer must be correct. Partial marks may be awarded for correct or partially correct formulas, but this will be at the marker’s discretion.
- Use the check boxes in the Done column to keep track of tasks completed.
- Remember to save often.
- When you are ready to submit, save, close and 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 the correct Excel file, (Numbers files will not be accepted) and if it has a ~ in front and is only 1KB it is not the correct file. Also please ensure that there are no “non-English” characters in the file name as these files cannot be opened in an English version of Excel.
Detailed instructions are provided on the next pages. Please follow each of the instructions precisely.
| Section A | The following are to be completed in the Labour HR Data sheet: | 8 | |
| A1-A8 | This sheet contains a list of employees at the company followed by a series of multiple-choice questions. Some of the questions are general, some refer to the data. Each question has only one correct answer, please indicate the correct answer by changing the corresponding value in column F from FALSE to TRUE. Only change one option for each question or it will be marked wrong.
|
8 | ☐
|
| Section B | The following are to be completed in the Labour Costs sheet: | 14 | |
| B1 | This sheet contains a list of staff with their income details. Column C has been hidden, unhide column C. | 1 | ☐ |
| B2 | Staff salaries are shown as a daily rate (column E) and the average number of days worked per month is shown in column F. In H6 calculate the employee’s annual salary using the values in E6 and F6. Copy the formula down for the rest of the staff. | 1 | ☐ |
| B3 | In cell I6 calculate the employee’s annual super using the rate specified in J3. This rate may change so do NOT just type it in. Make sure you use an absolute cell reference and copy the formula down to I18. | 1 | ☐ |
| B4 | Each employee also receives an annual bonus calculated by applying the bonus rate to their annual pay. In J6 calculate the employee’s total package by adding their annual pay, annual super and annual bonus. Copy down to J18. | 2 | ☐ |
| B5 | Widen column J so it is a similar width to the other columns. | 1 | ☐ |
| B6 | Apply the Percentage number format to the Super rate in J3. If necessary, adjust it to show one decimal place. | 1 | ☐ |
| B7 | In J20 add up all the Total Packages to get a total labour cost for the year. | 1 | ☐ |
| B8 | In J21 calculate the lowest total package rounded to the nearest hundred. | 2 | ☐ |
| B9 | In J22 calculate the highest total package rounded to the nearest hundred. | 2 | ☐ |
| B10 | Name these ranges as follows:
D6:D18 Product_Line J6:J18 Total_Package (Note you are also encouraged to name any other ranges that prove helpful.)
|
2 | ☐
|
| Section C | The following are to be completed in the BOM Worksheet | 8 | |
| C1 | Convert the data in A3:F67 to a table. Change the name of the table to BOM. | 2 | ☐ |
| C2 | The Bill of Materials itemises the quantities of each component required to make one of each type of bike. In column D use a formula to get the appropriate Product Description for each Part Code from the Inventory table (in the Inventory sheet).
The formula must still work if either BOM or Inventory are sorted differently. |
2 | ☐ |
| C3 | In column E use a formula to get the appropriate Unit Price for each Part Code from the Inventory table. | 2 | ☐ |
| C4 | In I4 and I5 calculate the number of different components that are required for each product type (do not factor in quantity of each component). | 2 | ☐
|
| Section D | The following are to be completed in the CVP Analysis Worksheet | 25 | |
| This sheet contains the CVP analysis. This is a multiproduct analysis, information and calculations relating to the X-800 should go in column B, and details for the ZF300 should go in column C. | |||
| D1 | In C3 calculate the total Fixed Costs from the values in the Costs sheet. | 1 | ☐ |
| D2 | In F6 calculate the total vary with revenue costs (as a percentage). | 1 | |
| D3 | In B12 and C12 calculate the direct material costs per unit for each product. | 2 | ☐ |
| D4 | In B13 and C13 calculate the direct labour costs per unit for each product. | 2 | ☐ |
| D5 | In B19 and C19 calculate the Sales Mix for each product (i.e. the units of that product sold as a percentage of the total units sold.) | 1 | ☐ |
| D6 | In B20 and C20 calculate the contribution margin for each product (this should take into account the vary-with-revenue-costs). | 2 | ☐ |
| D7 | In F9 calculate the weighted average contribution margin. | 2 | ☐ |
| D8 | In F10, use the weighted average contribution margin to calculate Break Even Units. We cannot sell part of a bicycle, so apply an appropriate rounding function to get the minimum number of whole units we need to sell to not make a loss. | 2 | ☐ |
| D9 | In B22 and C22 calculate the break even volume in units per product type. | 1 | ☐ |
| D10 | In B23 and C23 calculate the break even sales value per product type. | 1 | ☐ |
| D11 | In F14 to F18 calculate the Break-Even Variable Costs (using the break even units and sales values for the two products). | 5 | ☐ |
| D12 | In F21 calculate the Break-Even Contribution Margin. | 1 | ☐ |
| D13 | In F22 calculate the Break-Even Profit Before Tax.
Tip: If we hadn’t rounded Weighted Break Even Units this should be 0, temporarily remove the rounding function to test. |
1 | ☐
|
| Section E | The following are to be completed in the Profit Volume Forecast Worksheet | 20 | |
| In this sheet we want to be able to model what our profits will look like with different sales volumes for different products. If you click in B3 you will see you can adjust the increment (how much our volume in units goes up by). In B4 you can choose the starting point and in G3 you can choose which product you wish to look at. The formulas in this sheet must take into account the selections made in these cells. (You are allowed to adjust these to test your model.) | |||
| E1 | In C4 create a calculation to add the Increment in B3 to the volume in B4. Use appropriate cell referencing so that when you drag the formula across to D4 it will add B3 to C4. Drag the formula across to G4. | 1 | ☐
|
| E2 | In row 5 calculate the total sales revenue for each of the different Sales Volumes for the product selected in G3. | 2 | ☐ |
| E3 | Complete the variable costs table (B8:G12) for the different sales volumes in row 4, using the appropriate product cost information in the CVP sheet. | 4 | ☐ |
| E4 | In row 15 calculate the Contribution Margin for each sales volume. | 1 | ☐ |
| E5 | In row 16 calculate Contribution Margin less Fixed Costs for each sales volume. | 1 | ☐ |
| E6 | In B19 enter a formula to calculate the state tax incurred if the profit was greater than zero but put 0 if it was zero or below. Apply referencing that will allow the formula to be dragged down to B20 and then across to G20. | 3 | ☐ |
| E7 | In row 23 calculate the profit after factoring in the income tax. | 1 | ☐ |
| E8 | In row 25 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 | ☐ |
| E9 | Complete the Break-Even Analysis Line Chart Data in B28 to G30 for each of the Unit volumes. (Note Total Costs includes Variable Costs, Fixed Costs and Income Tax), Total Sales Value should be exactly the same value as row 5. | 2 | ☐ |
| E10 | Using the data in A28 to G30, create a 2D Line Chart that plots the Total Fixed Costs,
Total Costs & Total Sales. Add the Sales Volume in Units (in row 4) as Horizontal Axis Labels. Add the Chart Title: Profit Volume Forecast (mark given only if this exact title is used). Use chart tools (not drag and drop) to position the legend at the top of the chart.
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).
|
4 | ☐ |
| Section F | The following are to be completed in the Inventory Worksheet | 10 | |
| F1 | Inventory items are categorised according to price. The category lookup table is provided in N6:O10. So for example a product costing from $30 up to (but not including) $50 is category B. In I4 create a calculation to determine the correct category for that product. (Formulas should automatically copy down.) | 2 | ☐ |
| F2 | In J4 enter a calculation to check if the number in stock (shown in G4) is below the minimum stock level in O3. If it is put a “Y” in the column, otherwise leave it blank.
Note the value in O3 can be changed and your formula should still work. |
2 | |
| F3 | If a stock item falls below the minimum stock level and is not on backorder (value in column H is not “Y”), then we need to reorder it. In K4 create a calculation that will return “Y” if the item needs reordering and leave the cell blank if it does not. | 3 | |
| F4 | If an item needs to be reordered, the usual reorder quantity is 10, but if the supplier is Nashbar and its a category A or B then we reorder 25. Create a calculation in L4 to calculate the correct reorder quantity. Note: if the reorder quantity is zero , the formula must put 0 (without quotes). | 3 | ☐ |
For solution, contact at info@myassignmentguru.com or WhatsApp/Text at +1(450)800-2020.