Please follow these directions exactly:

1. P&L and cash flow from scratch, following the directions below. Do not use templates.

When you create these, add tabs next to the Directions and Numbers

Remember there are 3 steps to this: reading the direction, PROCESSING WHAT THAT DIRECTION MEANS, then APPLYING that direction

1. "The Numbers" tab gives you the annual numbers for the company. Use only this information and do not add any categories.

Feel free to use the numbers as a worksheet, if you desire, but you are not required to fill it out for our purposes. IF IT HELPS YOU, FILL IT OUT.

The COGS are specified on the numbers sheet, so use those items for the COGS on the P&L.

Make sure to understand which items on the numbers are balance sheet items. Anything listed as "expense" in the title will go under expenses in the P&L. (HINT: My total expenses are $535,733, so you will know you are right if they match.)

For all numbers, make them whole. For all percentages, make them to 2 decimal places.

Within each section on the P&L and cash flow, please sort each section alphabetically Pay special attention to any direction with a >, as these are often-made mistakes.

1. Create a separate tab, name it P&L.

Create a P&L for the overall company and each division separately. So there will be five P&L's on the same page, with the first column being Company, then Commercial, Residential, Design/Build, and Snow Removal, and the percentage of revenue in that division.

In a separate row under divisions, list "% to Total Sales."

>To clarify, the format will be as follows; divisions across the top, line items down the left column.

The row of "% to Total Sales" will list ONLY the % of each division to the overall company.

You will replace the letters (W-Z) with the % of that division to Total sales.

The column of % in each division will reflect the % of each item as it relates to the Net Revenue of that division.

Copy the header below to the top of your P&L:

	Company	%	Commercial	%	Residential	%	Design/Build	%	Snow Removal	%
% to Total Sales		W%		X%		Y%		Z%

Apply all returns to Residential.

Equipment Rental and Contract Labor should be allocated Snow Removal.

>Material discounts should be allocated to all lines and should be allocated as percent of materials.

>For expenses on Company, list out each operating expense category individually, but on each product line, ONLY list the Total Operating Expense as a percentage of total sales (Net). To clarify, each divison will ONLY have a dollar amount (as a formula) and percentage listed on the Total Operating Expense line.

List every item as a percentage of the division's revenue, unless otherwise specified.

4.Create a cash flow tab and create a 6-week cash flow for this business. Start your cash flow on 1/1, which is a Monday.

There are no cash sales currently, all are sold on 30-day terms. All AP is on 30-day terms.

There should be a starting balance, cash in, cash out, and ending balance. You must improve their cash situation each week.

Advertising, Bank Charges, Business Licenses, Equipment Expense, Computer Software, and Rent are all paid on the 1st of the month.

Contract Labor, Equipment Rental, Freight In, and all Insurances are paid on the 8th of the month.

Labor, Manager Salary, Payroll, and Sales Commissions are paid bi-weekly starting on the first of the month.

Maintenance, Office Supplies, Payroll Taxes, and Postage are paid on the 15th of the month.

Shipping/Delivery, Shop Supplies, Small Hand Tools, Telephone, Utilities and Vehicle expense are all paid on the 25th of each month.

Use the breakdowns of the AR and AP (list each category)

1. Format both the P&L and Cash Flow in Excel to be easy to read and appropriate to hand to a client.

Format each to print on one page, using portrait and/or landscape, as applicable.

	Accounts Payable		$220,089
	(Current)		$33,013
	(30 days)		$44,018
	(60 days)		$66,027
	(Over 90 days)		$77,031
	Accounts Receivable		$208,360
	(Current)		$31,254
	(30 days)		$41,672
	(60 days)		$62,508
	(Over 90 days)		$72,926
	Accumulated Depreciation		$201,620
	Additional Capital Invested		$50,000
	Advertising		$2,200
	Bad Debt		$4,420
	Bank Charges		$3,450
	Business Licenses		$450
	Capital Stock Issued		$50,000
	Current Checking Account balance		$2,400
	Computer Software		$12,000
	Consulting		$1,000
	Contract Labor	COGS	$6,640
	Depreciation Expense		$28,747
	Equipment Expense		$26,200
	Equipment Rental	COGS	$862
	Freight In		$5,240
	Insurance - Health		$10,334
	Insurance - Liability		$14,373
	Insurance - Workers Comp		$11,600
	Labor-TOTAL	COGS	$198,944
	(Commercial)		$70,000
	(Residential)		$60,000
	(Design and Build)		$65,000
	(Snow Removal)		$3,944
	Line of Credit (Already maxed)		$150,000
	Maintenance		$12,593
	Manager Salary		$49,736
	Material Discounts	COGS	-$9,200
	Material Purchases-TOTAL	COGS	$424,254
	(Commercial)		$155,000
	(Residential)		$125,000
	(Design and Build)		$140,000
	(Snow Removal)		$4,254
	Office Supplies		$2,560
	Payroll		$150,000
	Payroll Taxes		$43,876
	Postage		$2,012
	Rent		$60,000
	Returns & Allowances		-$24,500
	Sales-TOTAL		$1,314,700
	(Commercial)		$437,109
	(Residential)		$372,550
	(Design and Build)		$398,541
	(Snow Removal)		$106,500
	Sales Commissions-TOTAL	COGS	$131,470
	(Commercial)		$40,000
	(Residential)		$50,000
	(Design and Build)		$41,470
	(Snow Removal)		$0
	Shipping & Delivery		$14,320
	Shop Supplies		$3,577
	Small Hand Tools		$2,875
	Telephone		$2,356
	Value of Trucks, Equipment, and Tools		$590,421
	Value of Office Equipment & Fixtures		$1,500
	Undistributed Earnings		-$150,809
	Utilities		$47,494
	Vehicle expense		$24,320

Answer rating: 100% (QA)

SOLUTION Here is the PL and cash flow for the given company based on the information provided PL Com... View the full answer