i need help step by step to prepare gross profit method.! thank you please explain every step .!

PB GROSS PROFIT METHOD (GP) LEAFINING OBJECTIVES - Use the gross profit method to estimate ending inventory. - Identify reasons for ditterences between book and physical inventories. - Interpret trends in the gross profit ratio. - Alter the worksheet to include a new input category. - Creste a chart demonstrating the relationship between gross profit percentage and estimated ending imventory. PAOBLEM DATA On September 30, 2013, the general ledger of Leon's Golf Shop, which uses the calendar year as its accounting period, showed the following year-to-date account balances: The merchandise inventory account had a $48,000 balance on January 1, 2013. The historical gross profit percentage is 40%. REQUIREMENT 1. Leon prepares quarterly financial statements and takes physical inventory once a year-at the end of the accounting period. In order to prepare the financial statements for the third quarter, the store needs to have an estimate of ending inventory. You have been asked to use the gross profit method to estimate the ending inventory. Review the worksheet called GP. Study it carefully because it may have a solution format somewhat different from the one shown in your textbook. 2. Open the file GP from the website for this book at cengagebrain.com. Enter all the formulas where indicated on the worksheet. Enter your name in cell A1. Save your completed worksheet as GP2. Print the worksheet. Also print your formulas. Check figure: Estimated ending inventory (cell D23), $72,400. WHATHF ANALYSIS 3. On Dacnmber 31,2013 , the year-to-date account balances of selected accounts were an follow Estmated ending merchandise inventory at December 31, 2013, is $ 4. A physcal count of morchandise inventory on December 31, 2013, revealed inventory costing $22,000. In the space below, Ist at least two possible reasons for this balance to be different trom the estimate computed in requirement 3 . CHART ANALYSIS 5. Click the Chart sheet tab. The line chart that appears plots quarterty sales and actual gross. prolat for ali four quarters. Examine the pattem of behavior for these two items and comment. on any favorable or untavorable trends noted. What is happening to the gross profit ratio? When tho assignment is complete, close the flie without saving it again. TICKLEAS (optional) Worksheet. At the present time, Leon's Golf Shop includes payments for freight costs in the purchases account. On the advice of an accountant, Leon will establish a separate account for freight-in charges (aiso called transportation-in) in 2014. Modity the GP2 worksheet to accept this additional item of input. Then estimate ending merchandise irventory at March 31,2014 , using the following data: 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 GPT. Chart. Using the GP2 file, create an XY chart for the GP2 worksheet that demonstrates the relabionship between gross prolit percentage (X-axis) and estimated ending inventory (Y-axis). Complete the Chart Tickler Data Table beginning on row 51 of the Chart tab and use it as a basis for preparing the chart. Enter your name somewhere on the chart. Save the flle again as GP2. Print the chart