Assignment Task

Scenario

Biketopia produces 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 breakeven analysis line chart. Download the full instructions and a mark breakdown from iLearn.

Start by downloading the assessment workbook from iLearn and copying or saving 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 that the first three worksheets are locked, you are not supposed to unlock them. The only cell you can change in the Information sheet is C3 where you are to type your name, you are not supposed to change anything in Costs & Pricing and only the TRUE/FALSE options can be changed (or even selected) in Clients.

Questions

1. This sheet contains a list of the company’s Melbourne based clients. There are 20 multiple-choice questions, some 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 G from FALSE to TRUE. Only change one option for each question or it will be marked wrong. Please note that this sheet is protected and other than the multichoice answers cannot be edited.

2. This sheet contains a list of staff with their weekly rate and typical work week and weekend hours worked per week. In cell F8 add the Weekday Hours to the Saturday Hours to get Total Weekly Hours. Copy the formula down to F20.

3. Widen column G so it is a similar width to the columns either side of it (all text in G7 should be visible)

4. In G8 calculate how much that employee gets paid for their weekly hours using the hourly rate in C8 and the Weekday Hours in D8. Copy the formula down to G20.

5. The company currently pay an additional 25% (shown in I4) on top of the standard rate for Saturday hours. In H8 calculate the Saturday pay for employee S12716 (do not type the 25% into the formula as the rate may change). Copy the formula down to H20.

6. Convert the data in A4:H70 to a table. Change the name of the table (not named range) to BOM (please be exact). Feel free to change the styling if desired but do NOT add a total row.

7. 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.

8. In column E use a formula to get the Product Description for each Part Code from the Inventory table.

9. In column H use a formula to get the Unit Price for each Part Code from the Inventory table and multiply it by Quantity to get the total price for each component

10. In K6 calculate the total weight for the Category shown in J6 and the product shown in K5. Use appropriate cell referencing so the formula can then be copied down and across to complete all the weight calculations for both product lines. (Full marks only awarded if correct cell referencing is used.)

11. Direct labour per unit is calculated by averaging the hourly pay (in the Labour Costs sheet) for all staff assigned to that product line and multiplying that total by the Man Hours to Make a Unit in the Costs & Pricing sheet. In B11 calculate the Direct Labour Cost per unit for the product shown in B4. (Must change if product name changes.)

12. In E5 Calculate the Contribution Margin per unit taking into account Variable Costs and Vary with Revenue Costs (sales commissions)

13. In E6 calculate the break-even volume (in units). Apply an appropriate rounding calculation (not formatting) to get the answer to 0 decimal places (Consider whether to use ROUND, ROUNDUP or ROUNDDOWN).

14. Tax is made up in part of a State Tax in G4 and a Federal Tax in G5, but tax is only charged where a profit was made. In B21, create a calculation that checks if the profit before tax was greater than 0, if yes, calculate the state tax incurred for the sales volume in B7 otherwise return 0 (do NOT just type 0 as the volumes can change). Adjust the cell referencing so the formula can be dragged down to B22 and then across to G22 and return the correct answers.

15. The inventory sheet contains a list of all items stocked to make the bikes. The In Stock column indicates how many of each part are currently in stock. Where the stock level has fallen below the minimum shown in N4 it may already have been re-ordered, where this is the case a “Y” will show in the On Backorder Column

16. In C7 create a calculation to add the values in B6 and B7. Adjust the cell referencing so that as you drag the formula across to G7 the results increase by the value shown in B6, e.g

17. In J5 enter a formula that returns a “Y” if the In Stock is below the value in N4 and it is not on backorder (value in column H is “N”), otherwise return an empty string.

18. The quantity to reorder is determined by the price of the item as shown in the table in M7:N10, e.g. anything that costs between $25 and $50 we reorder 25, anything $120 and over we reorder 10. Note: these values can change. In K5 calculate the reorder quantity, return 0 if reorder not required.

19. In N13:N18 calculate how many Part Codes are on Backorder for each supplier (in column M)

This ACCG2000 – Accounting and Finance has been solved by our PHD Experts at UnilearnO. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK and US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics and referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.