blitz

For This or a Similar Paper Click Here To Order Now

Required: Develop an Excel spreadsheet that uses the following inputs to calculate the following outputs.
Inputs Outputs
Variable cost per unit Break-even point in units
Total fixed costs Break-even point in sales (dollars)
Sales price per unit Units sales required to achieve a target profit
Target profit Sales (dollars) required to achieve a target profit
Unit contribution margin
Assume that this report is going to be used by one of your clients. For this Excel assignment and all Excel assignments going forward unless otherwise specified, it is expected that your report:
1. Will use cell referencing to calculate outputs when appropriate.
2. Will clearly indicate which cells should be manipulated by the user (inputs).
3. Is organized in a way that the information is easy to navigate and useful for decision making.
4. Will document relevant assumptions.
5. Will be submitted as an Excel file. (.numbers files or links will be given a grade of zero)

 

To create the Excel spreadsheet for this assignment, follow the steps below to ensure all inputs and outputs are correctly included, and the layout is clear and easy to use for decision-making. Here’s a detailed guide:

---

Step 1: Set Up the Spreadsheet Layout

Start by organizing your spreadsheet with clear sections for Inputs and Outputs.

Columns:

• Column A for labels (e.g., “Variable cost per unit,” “Total fixed costs”).
• Column B for user input fields.
• Column C for calculations (for the outputs).

Step 2: Inputs Section

This is where the user will enter the required data. These inputs should be clearly marked, and the cells for data entry should be easily identifiable (for example, in a light color like yellow).

1. Variable cost per unit (cell B2)
2. Total fixed costs (cell B3)
3. Sales price per unit (cell B4)
4. Target profit (cell B5)

Step 3: Output Section

These are the outputs that will be calculated based on the inputs provided.

1. Break-even point in units (cell C2)
2. Break-even point in sales (dollars) (cell C3)
3. Units sales required to achieve a target profit (cell C4)
4. Sales (dollars) required to achieve a target profit (cell C5)
5. Unit contribution margin (cell C6)

---

Step 4: Formulas for Calculations

Now, let’s set up the formulas for the outputs:

1. Unit Contribution Margin:
   • Formula: =B4-B2
   • This formula calculates the contribution margin per unit (Sales price per unit – Variable cost per unit).
2. Break-even point in units:
   • Formula: =B3/C6
   • This formula calculates the number of units that need to be sold to break even (Total fixed costs / Unit contribution margin).
3. Break-even point in sales (dollars):
   • Formula: =C2*B4
   • This formula calculates the total sales in dollars required to break even (Break-even point in units * Sales price per unit).
4. Units sales required to achieve a target profit:
   • Formula: =(B3+B5)/C6
   • This formula calculates the number of units to be sold to achieve the target profit (Total fixed costs + Target profit) / Unit contribution margin.
5. Sales (dollars) required to achieve a target profit:
   • Formula: =C4*B4
   • This formula calculates the total sales in dollars required to achieve the target profit (Units sales required * Sales price per unit).

---

Step 5: Document Assumptions

In a separate section or in a comment, include any relevant assumptions. For example:

• Assume that sales price and variable costs remain constant.
• Assume that fixed costs and target profits are accurate for the time period.

---

Step 6: Formatting for Usability

• Highlight Input Cells: Color the input cells (B2 to B5) in a light color like yellow to indicate that these are the cells the user will manipulate.
• Use Borders: Add borders around the input and output sections to make it easier to navigate.
• Label Outputs Clearly: Ensure that the outputs (C2 to C6) are clearly labeled and easy to find.

---

Step 7: Save the File

After entering the formulas, save the file in Excel format as instructed (e.g., .xlsx). Double-check that everything is functioning properly by entering different values in the input cells and ensuring the outputs change accordingly.

---

Example Layout in Excel:

A	B	C
Inputs
Variable cost per unit	10
Total fixed costs	5000
Sales price per unit	20
Target profit	2000
Outputs
Unit contribution margin		=B4-B2
Break-even point in units		=B3/C6
Break-even point in sales (dollars)		=C2*B4
Units sales required for target profit		=(B3+B5)/C6
Sales (dollars) required for target profit		=C4*B4

---

This structure allows easy navigation and ensures that the user can input necessary data and instantly see the calculated outputs for decision-making.

For This or a Similar Paper Click Here To Order Now

WRITE MY ESSAY