complete the corresponding five (5) worksheets. Upon completion, submit your completed worksheets and also answer the following questions:
- Why is it important to create a sales budget prior to creating any other budgets?
- What item(s) might you find on the Income Statement, but NOT on the cash budget?
- Why is there a difference between “cash” and “income” each month?
Creating the pro-forma
financial statements
1) Establish
Sales Projection
Expected Sales: 1,000 White chocolate pretzels ($30/each)
2,000 Semi-sweet chocolate pretzels ($35/each)
White
Semi-sweet
Quantity
1000
2000
Sales price
30
35
30,000
70,000
Sales revenue
Total revenues 100,000
2) Create a Production (purchases) Budget
Semi-sweet
White
Projected sales
1,000
2,000
(10% of projected sales)
End inventory
100
200
Sales revenue
30,000
70,000
Units to produce
1,015
2,020
New costs
White
Semi-sweet
Material
$10
$12
Labor
$5
$6
Overhead
$3
$4
Total
$18
$22
Beg. Inventory
White
Semi-sweet
Quantity
85
180
Cost
$16
$20
Beg. Inventory
$1,360
$3,600
TO CALCULATE COGS (using FIFO inventory system):
# White sold at $16 = 85
# White sold at $18 = 915
Total cost = 1360
Total cost = 16,470
# Semi-sweet sold at $20 = 180
Total cost = 36,000
# Semi-sweet sold at $22 =
Total cost =
Total cost of goods sold =
Beg. Inventory
White
Semi-sweet
New costs
White
Semi-sweet
Quantity
85
180
Material
$10
$12
Cost
$16
$20
Labor
$5
$6
Cost
$3
$4
Total value
$1,360
$3,600
Cost
$18
$22
TO CALCULATE VALUE OF ENDING INVENTORY:
# WHITE IN INVENTORY @ $18 = __________ TOTAL VALUE = ______________
# WHITE IN INVENTORY @ $22 = __________ TOTAL VALUE = ______________
Other assumptions:
• General, selling & administrative expenses = $12,000
• Interest expense = $1,500
• Dividends = $1,500
CREATE THE PRO-FORMA FINANCIAL STATEMENT:
SALES REVENUE
COST OF GOODS SOLD
GROSS PROFIT
GEN. SELL & ADMIN EXP
OPERATING PROFIT
INTEREST EXPENSE
EPT (EARN BEFORE TAX)
TAXES (20%)
EAT (EARN AFTER TAX)
When NI was closed out at the end of the accounting cycle – where did it go?
COMMON STOCK DIV
INCREASE IN RET. EARN
Once you have created your income statement, you must break it down into smaller timeframes
in order to create a monthly ______________________________________________________
Assume your sales of $100,000 is expected to occur as follows:
MAR
FEB
JAN
MAY
JUN
APR
$15,000 $10,000 $15,000 $25,000 $15,000 $20,000
• 20% of sales is collected in month of sale
• 80% collected the following month
DEC
SALES
$12,000
JAN
FEB
$15,000
$10,000
MAR
$15,000
APR
$25,000
MAY
JUN
$15,000
$20,000
COLLECT
20%
COLLECT
80%
TOTAL
Now consider cash payments. You must consider the monthly costs associated with making the pretzels (material, labor
and overhead) and payments for general and administrative expenses, interest payments, taxes and dividends.
SEMI-SWEET CHOCOLATE PRETZELS
# UNITS COST/PER TOTAL
WHITE CHOCOLATE PRETZELS
# UNITS COST/PER TOTAL
MATERIALS
LABOR
OVERHEAD
$10
$5
$3
MATERIALS
LABOR
OVERHEAD
COMBINED
$12
$6
$4
• Costs are incurred on an equal monthly basis over the 6-month period.
• Payment for materials is made one month after purchase has been made.
• Labor and overhead are paid in the month incurred.
• General, selling, and administrative expenses are paid monthly and distributed evenly throughout the period.
• Tax payments are made in two equal installments, due in March and June.
• Dividends and interest expenses are paid in June.
• New equipment is purchased as follows:
• February: $8,000; paid in month purchased
• June: $10,000; paid in month purchased
MANUFACTURING COSTS
TOTAL COST
MATERIALS
LABOR
OVERHEAD
TIMEFRAME
6 MONTHS
6 MONTHS
6 MONTHS
AVG. MONTHLY
DEC
FEB
JAN
MAR
APR
MAY
JUN
Mat. Purchase
Payment for
materials
Monthly labor
Monthly overhead
G, S, & A Exp
Int exp
Taxes
PD dividends
New equipment
TOTAL PAYMENTS
Now, bring the projected cash receipts together with your projected cash distributions to determine your monthly
cash flows:
JAN
FEB
MAR
APR
MAY
JUN
TOTAL RECEIPTS
TOTAL PAYMENTS
NET CASH FLOW
Evaluate the grid above – assume that the Pretzel Palace has a corporate standard of maintaining a minimum
$5,000 cash balance at all times.
JAN
NET CASH FLOW
BEG. CASH BAL
TOTAL CASH BAL
LOAN (OR PMT)
CUMULATIVE
LOAN BALANCE
ENDING CASH BAL
$5,000
FEB
MAR
APR
MAY
JUN
ASSETS
CURRENT ASSETS:
CASH
MARKETABLE SEC
ACCT REC
INVENTORY
$5,000
$3,200
$9,600
$4,960
PLANT & EQUIP
TOTAL CURRENT ASSETS
$22,760
$27,740
$50,500
TOTAL ASSETS
ACCT PAYABLE
NOTES PAYABLE
LONG-TERM DEBT
COMMON STOCK
RETAINED EARNINGS
$15,000
$10,500
$20,500
TOTAL LIABILITIES & EQUITY
$50,500
$4,500
$0
BALANCE SHEET
20 JUN 2021
ASSETS:
CURRENT ASSETS:
CASH
MARKETABLE SEC
$3,200
ACCT REC
INVENTORY
TOTAL CURRENT ASSETS
PLANT & EQUIP
TOTAL ASSETS
LIABILITIES & EQUITY
ACCT PAYABLE
NOTES PAYABLE
LONG-TERM DEBT
$15,000
COMMON STOCK
$10,500
RETAINED EARNINGS
TOTAL LIABILITIES & EQUITY
Practicing ratio analysis:
Profitability ratios:
NI/SALES =
NI/TA =
NI/TE =
Turnover ratios:
Sales/AR(80% of sales are on credit)
COGS/Inventory
Sales/TA
Liquidity Ratios:
CA/CL =
(CA-INV)/CL =
Debt Utilization Ratios:
Total debt/total assets =
EBIT/INTEREST =
Week 9 Assignment: Pretzel Palace Budgeting
Assignment Due Date and Time
•
This assignment is due Sunday of Week 9 by 11:59pm ET.
Instructions
This assignment allows you to experience creating pro-forma financial statements
for your new business, Pretzel Palace. Because this task is a large undertaking, the
assignment is divided into five (5) worksheets to help you pace yourself through
the process. Use the “Pretzel Palace” video tutorial as a guide to help you complete
this assignment. After completing all the worksheets, you will answer three (3)
questions about the experience.
If you are ready to get started, proceed to Worksheet 1.
College of Online Education
Johnson & Wales University
ACCT3150
Worksheet 1
Establish ______________________________
Expected Sales:
•
•
1,000 WHITE CHOCOLATE PRETZELS ($30 EACH)
2,000 SEMI-SWEET CHOCOLATE PRETZELS ($35 EACH)
WHITE
SEMI-SWEET
QUANTITY
SALES PRICE
SALES REVENUE
TOTAL
Create a ________________________________
WHITE
SEMI-SWEET
WHITE
SEMI-SWEET
QUANTITY
85
180
COST
$16
$20
TOTAL VALUE
$1,360
$3,600
NEW COSTS
WHITE
SEMI-SWEET
MATERIAL
$10
$12
LABOR
$5
$6
OVERHEAD
$3
$4
TOTAL
$18
$22
PROJECTED SALES
END. INVENTORY
BEG. INVENTORY
UNITS TO PRODUCE
BEG. INVENTORY
College of Online Education
Johnson & Wales University
ACCT3150
TO CALCULATE COST OF GOOD SOLD (COGS):
# WHITE SOLD @ $16 = ______________
TOTAL COST = ________________
# WHITE SOLD @ $18 = ______________
TOTAL COST = ________________
# SEMI-SWEET SOLD @ $20 = _________
TOTAL COST = ________________
# SEMI-SWEET SOLD @ $22 = _________
TOTAL COST = ________________
TOTAL COST OF GOODS SOLD = ______________
College of Online Education
Johnson & Wales University
ACCT3150
Worksheet 2
BEG. INVENTORY
WHITE
SEMI-SWEET
QUANTITY
85
180
COST
$16
$20
TOTAL VALUE
$1,360
$3,600
NEW COSTS
WHITE
SEMI-SWEET
MATERIAL
$10
$12
LABOR
$5
$6
OVERHEAD
$3
$4
TOTAL
$18
$22
TO CALCULATE VALUE OF ENDING INVENTORY:
# WHITE IN INVETORY @ $18 = _____________
TOTAL VALUE = ___________
# SEMI-SWEET IN INVENTORY @ $22 = _______
TOTAL VALUE = ___________
OTHER ASSUMPTIONS:
•
•
•
GENERAL, SELLING, & ADMIN EXPENSES = $12,000
INTEREST EXPENSE = $1,500
DIVIDENDS = $1,500
CREATE THE PRO-FORMA INCOME STATEMENT:
ITEM
SALES REVENUE
COST OF GOODS SOLD
GROSS PROFIT
GEN. SELL & ADMIN EXP
OPERATING PROFIT
INTEREST EXPENSE
EBT (EARN BEFORE TAX)
TAXES (20%)
EAT (EARN AFTER TAX)
COMMON STOCK DIV
INCREASE IN RET. EARN
College of Online Education
Johnson & Wales University
AMOUNT
ACCT3150
Worksheet 3
Once you have created your Income Statement, you must break it down into
smaller time frames in order to create a monthly ___________________.
Assume your Sales of $100,000 is expected to occur as follows:
MONTH
JAN
FEB
MAR
APR
MAY
JUN
SALES
$15,000
$10,000
$15,000
$25,000
$15,000
$20,000
•
•
20% of sales is collected in month of sale
80% is collected the following month
MONTH
DEC
JAN
FEB
MAR
APR
MAY
JUN
SALES
$12,000
$15,000
$10,000
$15,000
$25,000
$15,000
$20,000
COLLECT
20%
COLLECT
80%
TOTAL
Now, consider Cash Payments. You must consider the monthly costs associated
with making the pretzels (material, labor, and overhead) and payments for general
and administrative expenses, interest payments, taxes, and dividends.
WHITE
UNITS
SEMI-SWEET
COST/PER
TOTAL
UNITS
COST/PER
MATERIALS
$10
MATERIALS
$12
LABOR
$5
LABOR
$6
OVERHEAD
$3
OVERHEAD
$4
•
•
•
•
•
•
COMBINED
TOTAL
Costs are incurred on an equal monthly basis over the 6-month period.
Payment for materials is made one month after purchase has been made.
Labor and Overhead are paid in the month incurred.
General, Selling, and Administrative expenses are paid monthly and
distributed evenly throughout the period.
Tax payments are made in 2 equal installments, due in March and June.
Dividends and interest expenses are paid in June.
College of Online Education
Johnson & Wales University
ACCT3150
•
New equipment is purchased as follows:
o February: $8,000, paid in month purchased
o June: $10,000, paid in month purchased
MANUFACTURING COSTS:
TOTAL COST
MATERIALS
LABOR
OVERHEAD
College of Online Education
Johnson & Wales University
TIMEFRAME
6 MONTHS
6 MONTHS
6 MONTHS
AVG. MONTHLY
ACCT3150
Worksheet 4
MONTH
MAT. PURCHASE
PMT FOR MATERIALS
DEC
$4,500
JAN
FEB
MAR
APR
MAY
JUN
MONTHLY LABOR
MONTHLY OVHD
G,S, & A EXP
INT EXP
TAXES
PD DIVIDENDS
NEW EQUIP
TOTAL PMTS
Now, bring the projected Cash Receipts together with your projected Cash
Disbursements to determine your monthly cash flows:
MONTH
TOTAL RECEIPTS
TOTAL PMTS
NET CASH FLOW
JAN
FEB
MAR
APR
MAY
JUN
Evaluate the grid above and assume that the Pretzel Palace has a corporate
standard of maintaining a minimum $5,000 cash balance at all times.
MONTH
JAN
FEB
MAR
APR
MAY
JUN
NET CASH FLOW
BEG. CASH FLOW
$5,000
TOTAL CASH FLOW
LOAN (OR PMT)
CUM. LOAN BALANCE
ENDING CASH BAL
College of Online Education
Johnson & Wales University
ACCT3150
Worksheet 5
BALANCE SHEET: 31-Dec-07
ASSETS
CURRENT ASSETS:
CASH
MARKETABLE SEC
ACCT REC
INVENTORY
TOTAL CURRENT ASSETS
PLANT & EQUIP
TOTAL ASSETS
LIABILITIES & EQUITY
ACCT PAYABLE
NOTES PAYABLE
LONG-TERM DEBT
COMMON STOCK
RETAINED EARNINGS
TOTAL LIABILITIES & EQUITY
AMOUNT
$5,000
$3,200
$9,600
$4,960
$22,760
$27,740
$50,500
AMOUNT
$4,500
$0
$15,000
$10,500
$20,500
$50,500
BALANCE SHEET: 20-Jun-08
ASSETS
CURRENT ASSETS:
CASH
MARKETABLE SEC
ACCT REC
INVENTORY
TOTAL CURRENT ASSETS
PLANT & EQUIP
TOTAL ASSETS
LIABILITIES & EQUITY
ACCT PAYABLE
NOTES PAYABLE
LONG-TERM DEBT
COMMON STOCK
RETAINED EARNINGS
TOTAL LIABILITIES & EQUITY
College of Online Education
Johnson & Wales University
AMOUNT
$3,200
AMOUNT
$15,000
$10,500
ACCT3150
Questions
1. Why is it important to create a sales budget prior to creating any other
budgets?
2. What item(s) might you find on the Income Statement, but NOT on the cash
budget?
3. Why is there a difference between “cash” and “income” each month?
Submission Instructions
Upload your completed worksheets to ulearn.
Grading Criteria
This assignment will be graded out of 100 points. Refer to the assignment rubric for
details.
College of Online Education
Johnson & Wales University
ACCT3150
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
