Question
In: Accounting
Spring 2020 Spreadsheet Project Name: Lexie's Wool Sweaters Projected Budgeting Data Sales & Collections October 2020...
| Spring 2020 Spreadsheet Project | ||||||
| Name: | ||||||
| Lexie's Wool Sweaters | ||||||
| Projected Budgeting Data | ||||||
| Sales & Collections | ||||||
|
October 2020 |
November 2020 |
December 2020 |
January 2021 |
February 2021 |
||
| Sales in Units (Sweaters) | 30,000 | 34,000 | 55,000 | 47,000 | 32,000 | |
| Selling Price per Sweater | $ 100.00 | |||||
| Cash Sales Collected in the Month of Sale | 30% | |||||
| Credit Sales Collected in the Month of Sale | 50% | |||||
| Credit Sales Collected in the Following Month | 20% | |||||
| Ending FG Inventory Requirement | 3% | of next months unit sweater sales | ||||
| Ending FG Inventory, September 30 , 2020 | 1,500 | sweaters | ||||
| Product Input Expenses | ||||||
| Direct Materials | ||||||
| Ending RM Inventory, September 30, 2020 | 8265.60 | yards | ||||
| Yards of Wool Required per Sweater | 4 | yards per sweater | ||||
| Raw Materials Cost per Yard of Wool | $ 3.50 | per yard | ||||
| Ending RM Inventory Requirement | 7% | of next months sweater production needs | ||||
| Wool Purchases Paid for in the Month of Purchase | 85% | |||||
| Wool Purchases Paid for in the Month following the Purchase | 15% | |||||
| Direct Labor | ||||||
| Number of Workers Required for the Making of Each Sweater | 5 | workers | ||||
| Labor Hours Required per Worker per Unit of FG (Sweater) | 0.5 | hours | ||||
| Labor Cost per Hour | $ 15.00 | per hour | ||||
| Manufacturing Overhead | ||||||
| Variable Manufacturing Overhead | $ 11.75 | per sweater | ||||
| Fixed Manufacturing Overhead | $ 30,200.00 | per month (Oct.) | $ 30,750.00 | per month (Nov. & beyond) | ||
| Noncash Fixed Manufacturing Overhead (included in above) | $ 10,250.00 | per month (Oct.) | $ 15,750.00 | per month (Nov. & beyond) | ||
| Selling & Administrative Expenses | ||||||
| Variable S&A | $ 7.37 | per unit sold | ||||
| Fixed S&A | $ 23,900.00 | per month | ||||
| Noncash Fixed S&A (included in above) | $ (10,750.00) | per month | ||||
| Factory Update & Cash Flow | ||||||
| Factory Update (PP&E) | $ 400,500.00 | paid on October 31, 2020 | ||||
| Principle Borrowed on October 1, 2020 | $ 300,000.00 | |||||
| Principle Repaid on November 30, 2020 | $ 300,000.00 | |||||
| Interest Payment on Borrowings in October & November | $ 9,000.00 |
per month (paid in following month) |
||||
Create a Schedule of Cash Collections in Excel using formulas only
Solutions
Expert Solution
As requested, only cash collections schedule is prepared using excel formulas:
Excel formulas:
ekkarill92 answered 2 years agoRelated Solutions
Spring 2020 Spreadsheet Project Name: Lexie's Wool Sweaters Projected Budgeting Data Sales & Collections October 2020...
Spring 2020 Spreadsheet Project
Name:
Lexie's Wool Sweaters
Projected Budgeting Data
Sales & Collections
October
2020
November
2020
December
2020
January
2021
February
2021
Sales in Units (Sweaters)
30,000
34,000
55,000
47,000
32,000
Selling Price per Sweater
$ 100.00
Cash Sales Collected in the Month of Sale
30%
Credit Sales Collected in the Month of Sale
50%
Credit Sales Collected in the Following Month
20%
Ending FG Inventory Requirement
3%
of next months unit sweater sales
Ending FG Inventory, September...
% - XYZ Company provides you with the following data projected for 2018: Projected sales are...
% - XYZ Company provides you with the following data projected
for 2018:
Projected sales are 30,000 units. Inventory at Dec 31,2017 was
4,000 manufactured units. The desired ending for Dec 31, 2018 is
8,000 manufactured units.
a) 5% - Provide a production budget for the number of units that
need to be produced.
b) 6% - Assume for each unit manufactured, four pounds of raw
materials are needed. Assume the desired ending inventory became
10,000 manufactured units. What effect...
Below is the data for the spreadsheet for a project selection model: Project NPV (return) cash...
Below is the data for the spreadsheet for a project selection
model:
Project
NPV (return) cash investment
personnel requirements
1
75,000
30,000
10
2
50,000
45,000
5
3
25,000
75,000
10
4
85,000
30,000
4
5
50,000
45,000
8
6
25,000
75,000
7
7
95,000
45,000
6
8
25,000
60,000
1
9
75,000
30,000
2
10
65,000
45,000
2
You have $250,000 to invest and 36 people. Maximize NPV by
approving/rejecting projects.
Multiple Choice #8: How many projects of the...
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....
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....
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....
Patricia’s housekeeping company has the following data projected for June. Beginning cash balance $24,500 Cash collections...
Patricia’s housekeeping company has the following data projected
for June. Beginning cash balance $24,500 Cash collections from
customers 36,200 Cash disbursements 54,300 Patricia wants to
maintain a cash balance of $10,000 or more. Any amount below this
can be borrowed from a local bank as needed in increments of
$1,000. All borrowings are made at month end. In Patricia's cash
budget for June, how much money will Patricia need to borrow at
month end? A. $6,000 B. $5,000 C. $7,000...
Graham Potato Company has projected sales of $13,200 in September, $16,000 in October, $23,200 in November,...
Graham Potato Company has projected sales of $13,200 in
September, $16,000 in October, $23,200 in November, and $19,200 in
December. Of the company's sales, 25 percent are paid for by cash
and 75 percent are sold on credit. Experience shows that 40 percent
of accounts receivable are paid in the month after the sale, while
the remaining 60 percent are paid two months after. Determine
collections for November and December.
Also assume Graham’s cash payments for November and December...
Graham Potato Company has projected sales of $20,400 in September, $22,000 in October, $30,400 in November,...
Graham Potato Company has projected sales of $20,400 in
September, $22,000 in October, $30,400 in November, and $26,400 in
December. Of the company's sales, 30 percent are paid for by cash
and 70 percent are sold on credit. Experience shows that 40 percent
of accounts receivable are paid in the month after the sale, while
the remaining 60 percent are paid two months after. Determine
collections for November and December.
Also assume Graham’s cash payments for November and December...
Graham Potato Company has projected sales of $14,400 in September, $17,000 in October, $24,400 in November,...
Graham Potato Company has projected sales of $14,400 in
September, $17,000 in October, $24,400 in November, and $20,400 in
December. Of the company's sales, 25 percent are paid for by cash
and 75 percent are sold on credit. Experience shows that 40 percent
of accounts receivable are paid in the month after the sale, while
the remaining 60 percent are paid two months after. Determine
collections for November and December.
Also assume Graham’s cash payments for November and December...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- More and more adults are considering commuting to work by bicycle rather than car, but few...
- Please answer these questions Data Processing and Analysis using python. Thank you In statistics, an outlier...
- Write a function file that accepts each of the four vectors as inputs. The function file...
- Kant says that to act morally is to act out of duty. How does this differ...
- Suppose you are trying to determine the capacity (in gallons) of the gas tank needed on...
- List the arithmetic operators used in BASIC and state their priorities of execution in a statement....
- Describe the espoused values of a company you have worked for (or been exposed to -...
ADVERTISEMENT