ACCG200 Excel Assessment S2 2018 Instructions, homework help

ACCG200 Excel Assessment S2 2018 Instructions
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

College Paper Writing Service