Question

In: Finance

Using Excel prepare a spreadsheet to show the profit and loss figure for the last financial...

Using Excel prepare a spreadsheet to show the profit and loss figure for the last financial year. The profit and loss should be shown as dollars and as a percentage.

Enter the raw data below, applying as many presentation Features (Font, Font Size, Font

Color, Number Formats and Color, Cell Shading, Text Rotation, etc) to it as you wish.

Apply appropriate number formats to your numbers.

Center your spreadsheet horizontally on the page

Give your spreadsheet an appropriate title and center it across your spreadsheet.

Select the best page orientation for your spreadsheet.

Adjust the column width and row height to suit the layout you have selected.

Create a formula to calculate the profit / loss as a currency for each month.

Create a formula to calculate the profit / loss as a percentage for each month.

Create a formula to calculate the Annual Totals for the Income, expenditure, and profit/loss.

Create a formula to calculate the profit / loss for the whole year.

Setup an appropriate header for this spreadsheet.

Setup a page number for this spreadsheet and place it in the footer.

Use the following raw data:

Month Income Expenses
March 1259.9 1410.45
April 1163.98 1499.10
May 1533 1370.25
June 1774 1440.8
July 1631 1530.25
August 1658 1490.55
September 1781 1369
October 1821.54 1420
November 2233.82 1611.81
December 2537.22 1577.63
January 1650 1423.98
February 1623 1598.12

Solutions

Expert Solution

The table with the answers are as shown below:

Month Income Expenses Profit/Loss as Currency Profit/Loss as pecentage
March 1259.9 1410.45 -150.55 -11.95%
April 1163.98 1499.1 -335.12 -28.79%
May 1533 1370.25 162.75 10.62%
June 1774 1440.8 333.2 18.78%
July 1631 1530.25 100.75 6.18%
August 1658 1490.55 167.45 10.10%
September 1781 1369 412 23.13%
October 1821.54 1420 401.54 22.04%
November 2233.82 1611.81 622.01 27.85%
December 2537.22 1577.63 959.59 37.82%
January 1650 1423.98 226.02 13.70%
February 1623 1598.12 24.88 1.53%
Annual 20666.46 17741.94
Annual Profit (Currency) 2924.52
Annual Profit (Percentage 14.15%

The Excel screen shot is as shown below:

jeff jeffy answered 2 years ago
Next > < Previous

Related Solutions

Using the financial statement provided (an Excel spreadsheet with the data is available on Blackboard), prepare...
Using the financial statement provided (an Excel spreadsheet with the data is available on Blackboard), prepare a complete Statement of Cash Flows that shows both the direct method and the indirect method for summarizing cash flow from operating activities. Note to complete the assignment you will need to calculate items such as dividends paid (Retained Earnings & AOCI2019 – Net income2019 – Retained Earnings & AOCI2018) and capital expenditures (Net PP&E2018 – Book Value of PP&E Sold2019 – Depreciation Expense2019...
Please show formulas using excel Build a spreadsheet: Construct an Excel spreadsheet to solve all of...
Please show formulas using excel Build a spreadsheet: Construct an Excel spreadsheet to solve all of the preceding requirements. Show how the solution will change if the following data change: the April 1 work-in-process costs were $66,000 for direct material and $18,000 for conversion. Work in process, April 1—10,000 units: Direct material: 100% complete, cost of .........................................................................................................$ 22,000 Conversion: 20% complete, cost of ................................................................................................................. 4,500 Balance in work in process, April 1 ..................................................................................................................$  26,500 Units started during April ......................................................................................................................................100,000 Units...
Prepare an Excel spreadsheet to present your financial decisions for two projects using NPV, IRR and...
Prepare an Excel spreadsheet to present your financial decisions for two projects using NPV, IRR and the Payback Period techniques. There are two mutually exclusive projects A and B.  Both projects require an investment of $10 million but the timing is different for the rest of the expected net cash flows. For project A                           For project B Period 0 =    - $10.0 m            - $10.0 million Period 1 =           6.5                       3.5 m Period 2              3.0                        3.5 Period 3 =           3.0                          3.5 Period 4 =           1.5                          3.5 Total Inflow = $14.0m                $14.0m Prepare 3...
Prepare a spreadsheet using Excel or a similar program in which you compute the items listed...
Prepare a spreadsheet using Excel or a similar program in which you compute the items listed in parts a, b, and d. Be sure to compute the Yield-to-Maturity (YTM) and Yield-to-Call (YTC) for each of years 5, 6, 7, 8, and 9. Kaufman Enterprises has bonds outstanding with a $1,000 face value and 10 years left until maturity. They have an 11% annual coupon payment, and their current price is $1,175. The bonds may be called in 5 years at...
Prepare a spreadsheet using Excel or a similar program in which you compute the items listed...
Prepare a spreadsheet using Excel or a similar program in which you compute the items listed in parts a, b, and d. Be sure to compute the Yield-to-Maturity (YTM) and Yield-to-Call (YTC) for each of years 5, 6, 7, 8, and 9. Kaufman Enterprises has bonds outstanding with a $1,000 face value and 10 years left until maturity. They have an 11% annual coupon payment, and their current price is $1,185. The bonds may be called in 5 years at...
PLEASE SHOW WORK USING EXCEL SPREADSHEET HCA HealthCare is considering an acquisition of Mission Health. Mission...
PLEASE SHOW WORK USING EXCEL SPREADSHEET HCA HealthCare is considering an acquisition of Mission Health. Mission Health is a publicly traded company, and its current beta is 1.30. Mission Health has been barley profitable and had paid an average of only 20% in taxes during the last several years. In addition, it uses little debt, having a debt ration of just 25%. If the acquisition were made, HCA would operate Mission Health as a separate, wholly owned subsidiary. Mission Health...
Prepare statement of profit and loss and statement of financial position DR CR Prepaid expense 1000...
Prepare statement of profit and loss and statement of financial position DR CR Prepaid expense 1000 account receivable 2200 office supplies 1800 office equipment 15000 cash 5400 accumulated depreciation-office equipment 4000 account payable 900 interest payable 100 salaries payable 1000 loan 2000 service revenue accrual 5000 share capital 12000 retained earning 4400 dividends paid 2000 service revenue 3000 office supplies expense 600 depreciation expense 2500 rent expense 1900
Construct a profit and loss statement using the following departmental figures and show the dollar amounts...
Construct a profit and loss statement using the following departmental figures and show the dollar amounts for net sales, total cost of goods sold, gross margin, expenses, and profit. Gross sales $82,000 Customer returns and allowances $4,000 Inward freight $2,000 Workroom costs $1,000 Opening inventory at cost $17,000 Closing inventory at cost $14,000 Purchases at cost $36,000 Cash discounts 8% Advertising $5,000 Rent $12,000 Salaries $17,000 Miscellaneous expenses $2,500
Blue Suits Consulting began business on January 1, 2017. Using an Excel spreadsheet, prepare journal entries...
Blue Suits Consulting began business on January 1, 2017. Using an Excel spreadsheet, prepare journal entries for the following transactions that occurred during 2017. January 1, 2017 The Owner invested $28,000 in cash and a computer equipment valued at $15,000 in the company. February 3, 2017 The Company performed services for a client and immediately received cash in the amount of $6,300. February 28, 2017 The Company purchased office equipment on credit for $8,000. March 3, 2017 The Company performed...
Process Cost Excel Project Create a new Excel spreadsheet and name it “Last name_PC”. You project...
Process Cost Excel Project Create a new Excel spreadsheet and name it “Last name_PC”. You project is to create a model for a production cost report using the weighted average method for the month of May.   Following good Excel design techniques, you should have an input area in which you put the department information for the month, and an output area that calculates the production cost report. As always, you should have only formulas or references in your output area....
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT