ACCG8121 Excel Assessment S1 2020 Instructions
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
eak 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
eak even analysis line chart. Download the full instructions and a mark
eakdown 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 co
ect answer regardless of the values in the spreadsheet
and must still work co
ectly 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 co
ect. Partial marks may be
awarded for co
ect or partially co
ect 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 co
ect Excel file, (Numbers files will not be accepted) and if it has
a ~ in front and is only 1KB it is not the co
ect 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.
Question Instruction Marks Done
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 co
ect answer, please indicate the co
ect answer
y changing the co
esponding 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
ate may change so do NOT just type it in. Make sure you use an absolute cell
eference 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 ☐
Question Instruction Marks Done
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
eak even volume in units per product type. 1 ☐
D10 In B23 and C23 calculate the
eak even sales value per product type. 1 ☐
D11 In F14 to F18 calculate the Break-Even Variable Costs (using the
eak 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
emove 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 incu
ed 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