1. The company has 12 machines that sell an average of 2,000 cans of Coke per month,...
Question:
1. The company has 12 machines that sell an average of 2,000 cans of Coke per month, for a total of 24,000 cans per month. In the Income Statement, input the data below and calculate the Total lines that are in Bold on the worksheet :
Revenue | |
Units Sold | 24,000 |
Price charged per Coke | $1.50 |
Variable Expenses | |
Cost per Coke | $0.75 |
Fixed Expenses | |
Maintenance | $400.00 |
Insurance | $1,000.00 |
Salary | $2,000.00 |
Taxes | $1,800.00 |
2. Use Goal Seek to calculate the Units Sold required to break even. In cells B25 to B27, enter the three cell references or numbers that you used in Goal Seek. In cell B28 enter the number of units that returns the breakeven value. Return the Units Sold to 24,000.
3. Create a one-variable data table in the shaded area E3:H13. Enter the appropriate labels in the top row to display the Units, Revenue, Total Expenses, and Net Income for Monthly Coke sales between 10,000 and 50,000 cans, in increments of 5,000. Complete the table with appropriate input cells, and format so that it is easy to read.
4. In the box below the data table (E16:K25), create a two-variable data table that displays Net Income for monthly Coke sales between 20,000 and 60,000 cans, in increments of 5,000 units in the column, and for Price Charged per Coke from $1.00 to $2.00 in increments of $0.20 in the top row. Complete the table with appropriate input cells, and format so that it is easy to read.
5. Using the custom number format, rename the cell at the top left of the table “Units”.
6. Assign names to the following cell ranges: B4:B5, B13:B16, B20:B22, using the text contained in column A to the left.
Expert Answer:
0 B Calculation of Units Sold required to Break even Sr no Particulars Amount A B D ... View the full answer
