JPL, Inc. has provided its sales and expense data for the most recent period. The Controller has asked you prepare a spreadsheet that shows therelated CVP Analysis computations. Use the information included in the Excel Simulation and the Excel functions described below to complete thetask.• Cell Reference: Allows you to refer to data from another cell in the worksheet. From the Excel Simulation below, if in a blank cell, =B5" wasentered, the formula would output the result from cell B5. or 75 in this example.• Beslc Math functlons: Allows you to use the basic math symbols to perform mathematical functions. You can use the following keys: -(plus sign to add). - (minus sign to subtract). * (asterisk sign to multiply). and / (forward slash to divide). From the Excel Simulation below, if ina blank cell "=85+B6" was entered, the formula would add the values from those cells and output the result, or 120 in this example. If usingthe other math symbols the result would output an appropriate answer for its function.国日らCVP Analysis - ExcelFILEHOMEINSERTPAGE LAYOUTFORMULASDATAREVIEWVIEWSign InCalibri-11A A%PasteBIU-Alignment NumberConditional Format asCellCellsEditingFormatting Table StylesClipboardFontStylesA1fxGiven the following information complete a CVP analysisAD34 Unit sales11,200 units5 Selling price per unit6 Variable expenses per unit7 Fixed expenses$75 per unit$45 per unit$210,0009 Use the data to answer the fallowing.1011 1. Compute the CM ratio and variable expense ratio12 Selling price per unit13 Variable expenses per unit14 Contribution margin per unitper unitper unitper unit1516 CM ratio17 Variable expense ratio1819 2. Compute the break-even paint20 Break-even in unit sales21 Break-even in dollar salesunits2223 3. Compute the margin of safety24 Margin of safety in dollars25 Margin of safety percentage2627 4. Compute the degree of operating leverage28 Sales29 Variable expenses30 Contribution margin31 Fixed expenses32 Net operating income3334 Degree of aperating leverageSheet1READY10%

4 Unit sales 5 Selling price per unit 6 Variable expenses per unit 7 Fixed expenses 11,200 units $75 per unit $45 per unit $210,000 Use the data to answer the following 1 1. Compute the CM ratio and variable expense ratio 12 Selling price per unit 3 Variable expenses per unit 14 Contribution margin per unit per unit per unit per unit 15 16 CM ratio 17 Variable expense ratio 18 19 2. Compute the break-even point 20 Break-even in unit sales 21 Break-even in dollar sales units 22 23 3. Compute the margin of safety 24 Margin of safety in dollars 25 Margin of safety percentage 26 27 4. Compute the degree of operating leverage 28 Sales 29 Variable expenses 30 Contribution margin 31 Fixed expenses 22 Net operating income 33 34 Degree of operating leverage

4 Unit sales 5 Selling price per unit 6 Variable expenses per unit 7 Fixed expenses 11,200 units $75 per unit $45 per unit $210,000 Use the data to answer the following 1 1. Compute the CM ratio and variable expense ratio 12 Selling price per unit 3 Variable expenses per unit 14 Contribution margin per unit per unit per unit per unit 15 16 CM ratio 17 Variable expense ratio 18 19 2. Compute the break-even point 20 Break-even in unit sales 21 Break-even in dollar sales units 22 23 3. Compute the margin of safety 24 Margin of safety in dollars 25 Margin of safety percentage 26 27 4. Compute the degree of operating leverage 28 Sales 29 Variable expenses 30 Contribution margin 31 Fixed expenses 22 Net operating income 33 34 Degree of operating leverage

Excel Applications for Accounting Principles

4th Edition

ISBN:9781111581565

Author:Gaylord N. Smith

Publisher:Gaylord N. Smith

ChapterMB: Model-building Problems

Section: Chapter Questions

Problem 1M: The general ledger of Jay Consulting shows the following balances at July 31: Jay has asked you to...

See similar textbooks

Similar questions

Click the Chart sheet tab. This chart is based on the problem data and the two income statements. Answer the following questions about the chart: a. What is the title for the X-axis? b. What is the title for the Y-axis? c. What does data range A represent? d. What does data range B represent? e. Why do the two data ranges cross? f. What would be a good title for this chart? When the assignment is complete, close the file without saving it again. Worksheet. The VARCOST2 worksheet is capable of calculating variable and absorption income when unit sales are equal to or less than production. An equally common situation (that this worksheet cannot handle) is when beginning inventory is present and sales volume exceeds production volume. Revise the worksheet Data Section to include: Also, change actual production to 70,000. Revise the Answer Section to accommodate this new data. Assume that Anderjak uses the weighted-average costing method for inventory. Preview the printout to make sure that the worksheet will print neatly on one page, and then print the worksheet. Save the completed file as VARCOSTT. Check figure: Absorption income, 670,000. Chart. Using the VARCOST2 file, fix up the chart used in requirement 5 by adding appropriate titles and legends and formatting the X- and Y-axes. Enter your name somewhere on the chart. Save the file again as VARCOST2. Print the chart.
Draw graph in excel that show the comparison between Machine A vs. Machine B Net Present Value using the attached data. Please note there are two pages with data,
The general ledger of Jay Consulting shows the following balances at July 31: Jay has asked you to develop a worksheet that will serve as a trial balance (file name PTB). Use the data provided as input for your model. Review the Model-Building Problem Checklist on page 154 to ensure that your worksheet is complete. Print the worksheet when done. Check figure: Total debits, 17,731. To test your model, use the following balances at August 31: Print the worksheet when done. Check figure: Total debits, 18,810. CHART (optional) Using the test data worksheet, prepare a pie chart showing the percentage of each asset to total assets. Print the chart when done.
Further info is in the attached images For the Excel part of the question give the solutions in the form of the Excel equations. Please and thank you! :) Download the Applying Excel form and enter formulas in all cells that contain question marks. For example, in cell B34 enter the formula "= B9". After entering formulas in all of the cells that contained question marks, verify that the dollar amounts match the example in the text. Check your worksheet by changing the beginning work in process inventory to 100 units, the units started into production during the period to 2,500 units, and the units in ending work in process inventory to 200 units, keeping all of the other data the same as in the original example. If your worksheet is operating properly, the cost per equivalent unit for materials should now be $152.50 and the cost per equivalent unit for conversion should be $145.50. Thank you!
Using Excel, create a table that shows the relationship between the interestearned and the amount deposited, as shown. we will first create the dollar amount column and the interest row, as shown . Next we will type into cell B3 the formula = $A3*B$2. We can now use the Fill command to copy the formula in other cells, resulting in the table as shown. Note that the dollar sign before A3 means column A is to remain unchanged in the calculations when the formula is copied into other cells. Also note that the dollar sign before 2 means that row 2 is to remain unchanged in calculations when the Fill command is used.
The comparative financial statements of Global Technology are as follows: Open the file RATIOA from the website for this book at cengagebrain.com. Enter the formulas in the appropriate cells. Enter your name in cell A1. Save the completed model as RATIOA2. Print the worksheet when done. Also print your formulas. Check figure: Acid test (quick) ratio (cell C58), .82.
Subject/Course: Financial Accounting. Note: A sample of a Worksheet is attached with the template, follow the sample/template. Provide workings of each and every step. If possible, attach the excel file with the solution.
You wish to create a Data Table that calculates the NPV with various initial outlays in D1:E6. Which cell should be entered into the Data Table dialog box, and in which edit box?
Compute mean . Note: Use MS-EXCEL for the Calculation, Take Snapshot after analysis
Please show your computations through excel format. Using FIFO, compute for the cost of WIP, end.
Using the excel spreadsheet provided, prepare the calculations.
Show Full Process/Details with Formula on Excel. Calculate (1) Horizon Value (HV) or Terminal Value (TV) and (2) Value of Operations