Scenario
JJ Cycles Ltd produce two high end bicycle products at two different factories. They need you to create a spreadsheet that can conduct a Cost Volume Profit Analysis for a selected product line. You can assume that sales price, fixed costs, and variable cost per unit are constant. 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 unit 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 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.
| 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: | 13 | |
| B1 | This sheet contains a list of staff with their income. Bonuses are calculated by applying the bonus rate to the annual salary. In F6 calculate the bonus amount ($) and then copy the formula down to F18. | 1 | ☐ |
| B2 | In G6 calculate the daily rate by dividing the annual rate by 365. Copy down to G18. | 1 | ☐ |
| B3 | In H6 calculate the employee’s annual super using the rate specified in I3. 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 H18. | 1 | ☐ |
| B4 | Some staff have taken days without pay, this will be deducted from their total package at the daily rate multiplied by the number of days taken. In I6 calculate the total package by adding the annual salary, bonus amount and super, and deducting the cost of days without pay. Copy down. | 2 | ☐ |
| B5 | Widen column I so it is a similar width to the other columns. | 1 | ☐ |
| B6 | Apply the Accounting number format to the totals in column I. | 1 | ☐ |
| B7 | In C20 calculate the total annual salary. | 1 | ☐ |
| B8 | In C21 calculate the highest annual salary. | 1 | ☐ |
| B9 | In C22 calculate the average annual salary rounded to the nearest hundred using a standard ROUND function. | 2 | ☐ |
| B10 | Name these ranges as follows:
B6:B18 Product_Line I6:I18 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 | 13 | |
| C1 | Convert the data in A3:H69 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 the two products. In column D use a formula to get the appropriate Category 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 Product Description for each Part Code from the Inventory table. | 2 | ☐ |
| C4 | In column F use a formula to get the appropriate Weight for each Part Code from the Inventory table and then multiply this by the quantity to get the total weight for that component. | 2 | ☐ |
| C5 | In column G use a formula to get the appropriate Unit Price for each Part Code from the Inventory table. | 2 | ☐ |
| C6 | In K4 calculate the total weight in grams for all the Groupset components for the product shown in K3. Use mixed references so the formula can be dragged down
and across. (Note marks will be deducted if referencing is incorrect.) Tip: Using named ranges correctly will simplify the formulas. |
3 | ☐
|
| Section D | The following are to be completed in the CVP Analysis Worksheet | 21 | |
| Click in cell B3, note that you can choose a product from the drop down list. This allows the company to select which product they wish to perform a CVP for. Ensure that calculations will return the correct answers regardless of which product is selected. |
| D1 | In B4 use a formula to retrieve the Unit Sales Price from the Fixed Costs sheet for the product shown in B3. | 2 | ☐ |
| D2 | In B5 use a formula to retrieve the Unit Sale from the Fixed Costs sheet for the product shown in B3. | 2 | ☐ |
| D3 | In B6 use a formula to retrieve the Total Fixed Costs from the Fixed Costs sheet for the product shown in B3. | 2 | ☐ |
| D4 | In E5 use the information in the Labour Costs sheet to calculate the Direct Labour Cost per unit for the product shown in B3. | 2 | ☐ |
| D5 | In E6 use the information in the BoM to calculate the Direct Material Cost per unit for the product specified in B3. | 2 | ☐ |
| D6 | In E14 calculate the Variable Selling & Admin Cost per unit (as a %). | 1 | ☐ |
| D7 | 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 | ☐ |
| D8 | In H5 calculate your break-even volume (in units). Apply an appropriate rounding function to get the answer to 0 decimal places (Consider whether to use ROUND, ROUNDUP or ROUNDDOWN). (This answer is very important so double check it!) | 2 | ☐ |
| D9 | In H6 calculate the Break-Even Sales Value ($ sales or break even volume in units). | 1 | ☐ |
| D10 | 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 and formula are correct. | 3 | ☐ |
| D11 | In H17 calculate the Break-Even Contribution Margin. | 1 | ☐ |
| D12 | In H18 calculate the Break-Even Profit Before Tax. (If we hadn’t rounded Break Even Units this should be 0) | 1 | ☐
|
| Section E | The following are to be completed in the Profit Volume Forecast Worksheet | 19 | |
| In this sheet we want to be able to forecast what our profits will look like with different sales volumes. | |||
| E1 | In C5 create a calculation to add the Increment (the amount our forecast is increasing by) in B3 to the volume in C3. (Note we want the ability to change the values in B3 and C3 so do NOT just add 25, use a relative reference or a range name). Drag the formula across to G4 | 1 | ☐
|
| E2 | In row 5 calculate the total sales revenue for each of the different Sales Volumes. | 1 | ☐ |
| E3 | Complete the variable costs table for the different sales volumes (B8:G13). | 3 | ☐ |
| E4 | In row 16 calculate the Contribution Margin for each sales volume. | 1 | ☐ |
| E5 | In row 17 calculate Contribution Margin less Fixed Costs for each sales volume. | 1 | ☐ |
| E6 | In row 20 calculate the state tax incurred for each sales volume, if the profit was zero or below, the calculation should return 0. (Do NOT just type 0 as the volumes can change.) | 2 | ☐ |
| E7 | In row 21 calculate the federal tax incurred for each sales volume, if the profit was zero or below the calculation should return 0. (Do NOT just type 0 as the volumes can change.) | 2 | ☐ |
| E8 | In row 24 calculate the profit after factoring in the income tax. | 1 | ☐ |
| E9 | 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 | ☐ |
| E10 | Complete the Break-Even Analysis Line Chart Data in B29 to G32 for each of the Unit volumes. (Note Total Costs includes Variable Costs, Fixed Costs and Income Tax) | 2 | ☐ |
| E11 | Using the data in B29 to G23, create a 2D 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 (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 | 6 | |
| F1 | If a stock item falls below the minimum stock level in M3 (subject to change) and is not on backorder (value in column H is “N”), then we need to reorder it. In I4 create a calculation that will return “Y” if the item needs reordering and leave the cell blank if it does not. | 3 | ☐ |
| F2 | The quantity to reorder is determined by the price of the item as shown in the table in L6:M10, e.g. anything that costs between $5 and $49.99 we reorder 25 (these values can change). In J4 calculate the reorder quantity, return 0 if reorder not required. | 3 | ☐ |
Please contact us at info@myassignmentguru.com or whatsapp at +1(450)800-2020 for solution.